Index of 1.

An index is a data structure (ordered) that helps MySQL obtain data efficiently. In addition to the data, the database system also maintains data structures that satisfy specific lookup algorithms and that refer to (point to) the data in a way that makes it possible to implement advanced lookup algorithms on these data structures, called indexes.

== SQL optimization is the most important index ==

There is no index in Figure 1. If we want to query data 3, we will search from top to bottom and perform full table scan (full table scan) to query data.

In Figure 2, the first index is 34, and the second index 77 is compared with the root node 34. If it is larger than 34, it is placed in the right subtree. Less than 34 goes to the left subtree. These numbers act as references to addresses that point to data.

Creating an index, which in database terms is a data structure, is essentially a table (called a B+ tree data structure in mysql5.5 and above) that records a reference.

1.2 Index Advantages and Disadvantages

Advantages 1) Similar to the book catalog index, improve the efficiency of data retrieval, reduce the IO cost of database. 2) Sort data through index columns, reduce the cost of data sorting and reduce CPU consumption.

disadvantage

1) The index is actually a table, which holds the primary key and index fields and points to the records of the entity class, so the index column also takes up space. 2) Although indexes greatly improve query efficiency, they also reduce the speed of updating tables, such as INSERT, UPDATE, DELETE tables. MySQL > alter table update; MySQL > alter table update; MySQL > alter table update;

1.3 Index Structure

Indexing is implemented in MySQL’s storage engine layer, not in the server layer. So each storage engine may not have exactly the same index,

Not all storage engines support all index types. MySQL currently provides the following four types of indexes:

  • BTREE index: the most common index type. Most indexes support B-tree indexes. (InnoDB engine mainly supports BTREE index)
  • HASH index: Supported only by the Memory engine.
  • R-tree index (spatial index) : Spatial index is a special index type of MyISAM engine. It is mainly used for geospatial data types and is usually used less frequently
  • Full-text: Full-text indexing is also a special index type of MyISAM. It is mainly used for full-text indexing. InnoDB supports full-text indexing from Mysql5.6.

MyISAM, InnoDB, Memory three storage engines for various index types support

The index InnoDB engine MyISAM engine The Memory engine
BTREE index support support support
A HASH index Does not support Does not support support
R – tree indexes Does not support support Does not support
Full-text Supported after version 5.6 support Does not support

When we talk about indexes, if not specified, we mean indexes organized in a B+ tree (multi-way search tree, not necessarily binary) structure. Clustered indexes, compound indexes, prefix indexes, and unique indexes all use B+tree indexes by default.

InnoDB is the right choice. Support transaction security, row locking (suitable for high concurrency), row level locking is more efficient, row locking may not ensure data integrity.

MyISAM: If the application is mainly read and insert operations, with few updates and deletes, and the transaction integrity and concurrency requirements are not very high, then this storage engine is very suitable. Does not support transaction security, table locking, can ensure data integrity 100 percent

1.4 BTREE structure

BTree is also called multi-path balanced search tree. The BTree features of an M-fork are as follows:

  • Each node in the tree contains a maximum of m children. (If this is a binary tree, each node contains at most 2 branches, that is, two children)
  • Each node except root and leaf nodes has at least [ceil(m/2)] children. (m/2 rounded up)
  • If the root node is not a leaf node, there are at least two children.
  • All the leaf nodes are in the same layer.
  • Each non-leaf node is composed of N keys and N +1 Pointers, where [ceil(m/2)-1] <= n <= m-1

Take a 5-fork BTree as an example. 5-fork means that each node contains a maximum of five children.

Number of keys: formula derivation [Ceil (m/2)-1] <= n <= m-1.

Ceil (5/2)-1<= N <=5-1 that is 2 <=n<= 4. When n>4 exceeds this range, the middle node splits to the parent node, and the two nodes split.

C N G A H E K Q M F W L T Z D P R X Y S

The evolution process is as follows:

1). Insert the first four letters C N G A

N keys and n+1 Pointers.

2). Insert H, n>4(i.e. there are more than 4 ACGHNs), and split the middle element G upward to the new node (i.e. split to the parent node, other nodes split to the sides).

3). Insert E, K, Q without splitting

4). Insert M, middle element M split up to parent node G

5). Insert F, W, L, T without splitting

6). Insert Z and the middle element T splits up into the parent node

7). Insert D, the middle element D splits upward into the parent node. And then insert P, R, X, Y without splitting

8). Finally, S is inserted, NPQR node N >5, and the intermediate node Q splits upward. But after splitting, the parent node DGMT’s N >5, and the intermediate node M splits upward

Compared with binary trees, BTREE is more efficient in data query because BTREE has a smaller hierarchical structure than binary tree for the same amount of data, so the search speed is faster.

1.5 B + TREE structure

B+Tree is a variant of BTree. The differences between B+Tree and BTree are as follows:

1). N fork B+Tree contains a maximum of N keys, and BTree contains a maximum of N-1 keys.

2). The leaf nodes of a. B+Tree (the bottom row with no child nodes is called leaf nodes) store all key information (these key information contains the key information of the root node and child nodes, these keys are only used as indexes) and are arranged according to the key size order.

3). All non-leaf nodes (not stored) can be considered as index parts of key.

Summary: only leaf nodes of B+Tree store data

  1. The leaf node stores data, and other non-leaf nodes can be regarded as the index part of key without storing data. == For example, if the search data goes from root node to non-leaf node to leaf node, some leaf nodes can find associated data information. Query what data need to go to the leaf node, query stability is higher

  2. There are keys between leaf nodes and non-leaf nodes to reference, which makes the query more efficient and stable

  3. There will be A connection between leaf nodes, which can be queried across nodes, which is very suitable for scope query.

Since only leaf nodes of B+Tree hold data, querying any key goes from root to leaf. Therefore, the B+Tree query efficiency is more stable.

The MySql index data structure is optimized over the classic B+Tree. On the basis of the original B+Tree, a linked list finger pointing to adjacent leaf nodes is added to form a B+Tree with sequential Pointers, which improves the performance of interval access (facilitating interval search).

1.6 the index

1.6.1 Index Classification

1) Single-value index: that is, an index contains only a single column. A table can have multiple single-column indexes

For example, the User table has name,password. The index we created contains only name column, is a single-value index.

2) Unique index: The value of the indexed column must be unique, but empty values are allowed.

For example, if the primary key id is unique, creating an index on this column is a unique index.

3) Composite index: that is, an index contains multiple columns

For example, there are name and password in the User table. The index we created contains multiple columns such as name and password, which is a compound index.

1.6.2 Creating indexes
Index type: This parameter is not required in general casesUNIQUE: 唯一的
FULLTEXT: 全文
SPATIAL: 空间
语法类型: [UNIQUE|FULLTEXT|SPATIAL] may not be specifiedCREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type]  //Index type: if not specified, use B+Trees, BTREEON tbl_name(index_col_name,...)
index_col_name : column_name[(length)][ASC | DESC]
Copy the code

Creating a normal index

Idx_city_name Specifies the name of the index created for city_name in the city tablecreate index idx_city_name on city(city_name)
Copy the code

Create composite index:

The NAME,email, and STATUS fields are used as indexes. The indexes follow the left-most principleCREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
Copy the code
1.6.3 Viewing Indexes
SHOW INDEX FROM city; // By default, the primary key is used to create an indexCopy the code

1.6.4 Deleting indexes

DROP INDEX INDEX name ON Table name;

DROP INDEX index_name ON city; 
Copy the code
1.6.5 Modifying an Index
1). alter table tb_name add primary key(column_list); Alter table tb_name add unique index_NAME (column_list); Alter table tb_name add index index_NAME (column_list); Add a normal index. The index value can appear more than once. 4). alter table tb_name add fulltext index_name(column_list); This statement specifies the index as FULLTEXT, which is used for full-text indexingCopy the code

1.7 Index design principles

  1. Create indexes for tables that are frequently queried and have a large amount of data

  2. For index field selection, the best candidate column should be extracted from the condition of the WHERE clause, or if there are many combinations in the WHERE clause, it should be selected

    Select the combination of the most commonly used and best filtered columns. (Frequently used conditional query)

  3. With unique indexes, the higher the differentiation, the more efficient the index usage.

  4. With short indexes, indexes are also stored on hard disks after they are created. Therefore, improving I/O efficiency of index access can also improve overall access efficiency

    Rate. The shorter the total field length, the better.

  5. A combined index of N columns with the left-most prefix is equivalent to creating N indexes if used in the WHERE clause

    The first few fields that make up the index, then the query SQL can use the combined index to improve query efficiency. (Composite indexes follow the leftmost matching rule)

Leftmost matching rule: The order of SQL statement WHERE conditions must be the same as the order of index creation. If the query does not match the first leftmost index, the index is invalidated and the query statement does not follow the index.

Create composite index:CREATE INDEX idx_name_email_status ONtb_seller(NAME,email,STATUS); This is equivalent to creating an index for name; Index name, email; Index name, email, status;Copy the code

2. View

A view is a visual table based on the result set of an SQL statement.

A view contains rows and columns, just like a real table. Fields in a view are fields in real tables from one or more databases. We can add SQL functions, WHERE, and JOIN statements to the view, and we can submit data as if it came from a single table.

** Note: The design and structure of the ** database is not affected by functions, WHERE, or JOIN statements in the view.

The main advantages of views over regular tables include the following. Simple: The user who uses the view does not need to care about the structure, association conditions, and filtering conditions of the corresponding table, which is already the result set of filtered compound conditions to the user. Security: View users can only access the result set they are allowed to query. Table permission management is not limited to a row or a column, but can be easily implemented through views. Data independence: Once the structure of the view is determined, the impact of table structure changes on users can be shielded. Adding columns to the source table has no impact on the view. If the source table changes the column name, it can be resolved by modifying the view without affecting visitors.

2.1 Creating a View

Create a view for the query result with view name view_nameCREATE VIEW view_name AS
    SELECT column_name(s) FROM table_name WHERE condition;
//Query data from the viewSELECT * FROM view_name;
Copy the code

2.2 Updating the View

One way; If the modified view exists, the view is modified using the modify statement; if not, a view is createdCREATE OR REPLACE VIEW view_name AS
	SELECT column_name(s) FROM table_name WHERE condition; A way;ALTER VIEW <View name> AS <SELECTstatements>
Copy the code

2.3 Canceling views

DROP VIEWView nameDROP VIEW view_name
Copy the code

3. Stored procedures

Stored procedures and functions are a set of SQL statements that are compiled and stored in the database. Invoking stored procedures and functions can simplify the work of application developers, reduce data transmission between the database and the application server, and improve the efficiency of data processing.

The difference between a stored procedure and a function is that a function must have a return value, whereas a stored procedure does not. Function: is a procedure that returns a value; Procedure: is a function that returns no value; (Stored procedures do not return a value, but out can also return a result, so stored procedures can do anything stored functions can do.)

3.1 Stored Procedures

3.1.1 Creating a Stored Procedure
grammarCREATE PROCEDURE procedure_name()
begin
	- SQL statement
end; The sample; Delimiter Specifies that the delimiter is $. Use this delimiter to indicate that the command ends and you can execute delimiter $create procedure pro_test1()
begin
	select 'Hello Mysql' ;
end$
delimiter ;
Copy the code
**==delimiter==** Declares the delimiter of the SQL statement

This keyword is used to declare the delimiter of the SQL statement and to tell the MySQL interpreter whether the command has finished and whether MySQL can execute it. By default, delimiter is a semicolon; . On the command line client, if a command line ends with a semicolon, mysql will execute the command when you press enter.

Understanding; The following statement, which I execute directly (containscreate procedureThe delimiter statement is syntax error, so the stored procedure uses the delimiter statement to indicate that the command is executed.)create procedure pro_test1()
begin
	select 'Hello Mysql' ;
Copy the code
3.1.2 Calling a Stored Procedure
call procedure_name(); // Procedure_name is the name of the created stored procedureCopy the code
3.1.3 Viewing Stored Procedures

A call to a stored procedure executes the SQL statement stored in the stored procedure

1 - way; Query all stored procedures in db_name
select name from mysql.proc where db='db_name';
- Method 2: Query the status of the stored procedure
show procedure status;
-- Method 3: Query the definition of a stored procedure (including the procedure creation statement). Pro_test1 is the name of the stored procedure
show create procedure test.pro_test1;
Copy the code
3.1.4 Deleting a Stored Procedure
DROP PROCEDURE [IF EXISTSSp_name;//Sp_name Name of the stored procedureCopy the code

3.2 grammar

Stored procedures are programmable, meaning they can use variables, expressions, and control structures to perform complex functions.

3.2.1 DECLARE Define variables

DECLARE allows you to define a local variable whose scope can only be defined in BEGIN… In the END block.

The var_name variable name can be declared as more than one,DEFAULT valueThe assignmentDECLAREvar_name[,...]  type [DEFAULT value]
Copy the code

The sample

delimiter $
create procedure pro_test2()
begin
    declare num int default 5;      -- Declare variable num of type int, default value 5
    select num+ 10;
end$
delimiter ;
Copy the code
3.2.2 Direct assignment of SET

** SET can be used to assign constants or expressions. The syntax is as follows:

Multiple constants or expressions can be assigned directlySET var_name = expr [, var_name = expr] ...
Copy the code

The sample

DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
    DECLARE NAME VARCHAR(20);
    SET NAME = 'MYSQL';
    SELECT NAME ;
END$
DELIMITER ;
Copy the code

You can also select… Into:

DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city;  -- into specifies the assigned variable, that is, the total of the query city table is assigned to the variable countnum
select countnum;
END$
DELIMITER ;
Copy the code
3.2.3 If condition judgment
if search_condition then statement_list    If search_condition is true,then statement_list
    [elseif search_condition then statement_list] ... -- Execute this statement if satisfied
    [else statement_list]							  -- Otherwise execute this statement
end if;										-- The closing character echoes if
Copy the code

The sample

delimiter $
create procedure pro_test6()
begin
declare height int default 175;  -- Define variable height, type int, default 175
declare description varchar(50); -- Define variable description, type varchar(50)
    if height > = 180 then        -- If height>=80, assign the variable description description = 'tall ';
    	set description = '身材高挑';
    elseif height > = 170 and height < 180 then
    	set description = 'Standard size';
    else
    	set description = 'Average body';
    end if;
select description ;
end$
delimiter ;
Copy the code
3.2.4 Passing Parameters

Parameters are passed when the stored procedure is created

Syntax format:

create procedure procedure_name([in/out/inout] Parameter name Parameter type)IN: This parameter can be used as input, which requires the caller to pass in a value. By default, it is an input parameter.OUT: This parameter is output, which means that this parameter can be returnedINOUT: can be used as an input parameter or an output parameterCopy the code
3.2.4.1 IN: This parameter is used as input

Example: Determine the type of body that the current height belongs to based on the defined height variable

Create a stored procedure
delimiter $
create procedure pro_test5(in height int) Create a stored procedure; In: Input parameter,height parameter name,int parameter type
begin
	declare description varchar(50) default ' '; -- Declare variable description to be of type VARCHAR, default to ''
	if height > = 180 then
		set description='身材高挑';
	elseif height > = 170 and height < 180 then
		set description='Standard size';
	else
		set description='Average body';
	end if;
	select concat('height', height , 'Corresponding body type is :',description);
end$
delimiter ;

Call stored procedure: pass the value
call pro_test_in(200);
Copy the code
3.2.4.2 OUT: This parameter is used as the output

Output parameter: equivalent to the return value of the modified stored procedure

Gets the body type of the current height based on the height variable passed in (the return value)

Create a stored procedure
delimiter $
create procedure pro_test_out(in height int , out description varchar(100)) -- In input parameters,out output parameters
begin
	if height > = 180 then
		set description='身材高挑';
	elseif height > = 170 and height < 180 then
		set description='Standard size';
	else
		set description='Average body';
	end if;
end$

delimiter ;

Call the stored procedure: pass in the value 180 and use the variable description to receive the return value
call pro_test_out(180.@description);
SELECT @description;  Look at the change return value variable
Copy the code

==@description== : This type of variable is preceded by the * “@” symbol *. It is called a user session variable and represents the entire session process. This is similar to a global variable. @@global.sort_buffer_SIZE: This type of variable preceded by the “@@” symbol is called a system variable.

3.2.5 case structure

This is similar to the SQL case when function

A:CASE case_value
    WHEN when_value THEN statement_list  -- 
    [WHEN when_value THEN statement_list] 
    [ELSE statement_list]
END CASE; Method 2:CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] 
    [ELSE statement_list]
END CASE;
Copy the code

Example: Given a month, figure out the quarter

delimiter $ 
create procedure procedure_case_test1(month int) -- The default value is in: the parameter is changed to input
begin
	declare result varchar(20); -- Define variable result as vARCHar of type length 20
	case 
			when month > = 1 and month < =3 then
			set result = 'First Quarter';
			when month > = 4 and month < =6 then
			set result = 'Second Quarter';
			when month > = 7 and month < =9 then
			set result = 'Third quarter';
			when month > = 10 and month < =12 then
			set result = 'Fourth Quarter';
			end case;
	select result;
	end $
delimiter;
	
call	procedure_case_test1(2); Call function input value 2
Copy the code
3.2.6 while loop

It keeps going

grammar

while search_condition do   If the condition is true, keep executing. If the condition is not true, break the loop
	statement_list
end while;
Copy the code

The sample; Compute the increment from 1 to n

delimiter $
create procedure pro_test8(n int) Enter n
begin
    declare total int default 0; -- Define a variable total of type int, default value 0
    declare num int default 1;   -- Define variable num,int, default 1
    while num< =n do				When - when num < = n
        set total = total + num; -- Assignment operation
        set num = num + 1;
    end while;
    select total;
end$
delimiter ;
Copy the code
3.2.7 repeat loop

== a conditional loop control statement that exits the loop when the condition is met. While is executed when the condition is met, while repeat is exited when the condition is met. Syntax: Loop conditions do not need semicolons

REPEAT
    statement_list  ;       -- Execute the SQL statement if the conditions are met
    UNTIL search_condition -- Until this condition is met and the loop exits, otherwise the loop continues
END REPEAT;
Copy the code

The sample

delimiter $
create procedure pro_test10(n int)
begin
    declare total int default 0;
    repeat
        set total = total + n;
        set n = n - 1;
    until n=0       -- Until n=0, exit the loop
    end repeat;
    select total ;
end$
delimiter ;
Copy the code
3.2.8 loop cycle

==LOOP implements a simple LOOP. The exit condition of the LOOP needs to be defined in another statement, usually by the LEAVE statement ==(the exit condition of the LOOP is defined by the LEAVE statement).

grammar

[begin_label:] LOOP         -- Alias of the current loop loop
	statement_list			Declare the body of the loop
END LOOP [end_label]		-- End loop
Copy the code

If you don’t add a LOOP exit statement to the statement_list, the LOOP statement can be used to implement a simple infinite LOOP.

3.2.9 Leave statements (usually used with LOOP loops)

Used to exit from annotated process construction, usually with BEGIN… END or loop together. Here is a simple example using LOOP and LEAVE to exit the LOOP:

delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
    declare total int default 0;
    ins: LOOP    -- Declare the ins alias of the loop loop
        IF n < = 0 then -- Define the leave exit condition
        leave ins;  Exit loop ins
        END IF;
    set total = total + n;
    set n = n - 1;
    END LOOP ins; -- End loop
    select total;
END$
delimiter ;
Copy the code
3.2.10 Cursor/Cursor

The == cursor is the data type used to store the query result set ==, which can be looping over with the cursor in stored procedures and functions. Cursor usage includes cursor declaration, OPEN, FETCH, and CLOSE, with the following syntax, respectively.

Declaration cursor:

DECLARECursor_name Cursor variableCURSOR FORSelect_statement query statement;Copy the code

OPEN the cursor:

OPEN cursor_name ;
Copy the code

FETCH cursor: Retrieves a row of data in a cursor, and each FETCH moves the cursor backward

 FETCH cursor_name INTO var_name [, var_name] ...
Copy the code

CLOSE the cursor:

CLOSE cursor_name ;
Copy the code

The sample

delimiter $
create procedure pro_test11()
begin
	declare e_id int(11);
	declare e_name varchar(50);
	declare e_age int(11);
	declare e_salary int(11);
	declare emp_result cursor for select * from emp; Create cursor; Stores the result set of the query
	open emp_result;   -- Open the cursor
	fetch emp_result into e_id,e_name,e_age,e_salary; Fetch cursor data: assign to 4 variables
	select e_id,e_name,e_age,e_salary;     -- Calling stored procedures; Query the values of four variables
	fetch emp_result into e_id,e_name,e_age,e_salary; 
	select e_id,e_name,e_age,e_salary;   
close emp_result;
end$

call pro_test11(); Call the stored procedure
Copy the code

The above example optimization, how many data we have to write how many crawl, you can use cycle optimization

DELIMITER $
create procedure pro_test12()
begin
    DECLARE id int(11);
    DECLARE name varchar(50);
    DECLARE age int(11);
    DECLARE salary int(11);
    DECLARE has_data int default 1;
    DECLARE emp_result CURSOR FOR select * from emp;
    DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;  Has_data =0 when no data is fetched
    open emp_result; -- Open the cursor
    repeat  -- Loop to retrieve cursor data
        fetch emp_result into id , name , age , salary; -- When the data cannot be captured
        select id , name , age , salary;
    until has_data = 0  -- Exit conditions
    end repeat;
    close emp_result; -- Close the cursor
end$
DELIMITER ;
Copy the code

==DECLARE EXIT HANDLER FOR NOT FOUND; Handle == this is a handle mechanism provided by mysql.

You need to create cursor statements right next to each other

4. Store functions

Function: is a procedure that returns a value;

grammar

CREATE FUNCTIONfunction_name([param type ...] )Parameter type list param type: parameter name Parameter type
RETURNS type -- Return type
BEGIN.END;
Copy the code

The sample

Define a storage function that requests the total number of records that meet the criteria;

delimiter $
create function count_city(countryId int)
returns int -- Return value type
begin
	declare cnum int ; Declare a variable to hold the return value
    select count(*) into cnum from city where  id = countryId; The total number of city table queries is assigned to the variable cnum
	return cnum;	Returns the value of cnum
end$
delimiter ;
Copy the code

Call a function

select count_city(1);  Call the function, passing in the argument 1
select count_city(2);
Copy the code

5, the trigger

A trigger is a table-related database object that fires and executes a set of SQL statements defined in the trigger (similar to an event) before or after insert/ UPDATE/DELETE. This feature of triggers helps ensure data integrity, logging, data validation, and other operations on the database side. The aliases OLD and NEW (also called row record variables) are used to refer to the changed record contents in the trigger, similar to other databases. Mysql triggers currently only support row-level firing, not statement-level firing.

Trigger type Use of NEW and OLD
INSERT flip-flop NEW indicates data to be added or already added
UPDATE flip-flop OLD indicates the data before modification, and NEW indicates the data to be modified or has been modified
DELETE flip-flop OLD indicates data to be deleted or already deleted

5.1 Creating a Trigger

Grammatical structure:

create trigger trigger_name   Create trigger
before/after insert/update/delete  -- Before/After triggers before or after,insert or update triggers this trigger
on tbl_name   -- The name of the table that triggers and is associated with, and is followed by, on
[ for each row ] -- row-level trigger
begin
trigger_stmt ;   -- Trigger specific content
end;
Copy the code

Example: Log data changes of emP table through trigger, including increase, modify, delete;

Start by creating a log table:

create table emp_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment 'Operation type, insert/update/delete',
operate_time datetime not null comment 'Operation time',
operate_id int(11) not null comment 'Operation table ID',
operate_params varchar(500) comment 'Operation parameters'.primary key(`id`)
)engine=innodb default charset=utf8;
Copy the code

Create an INSERT trigger and log when the data is inserted:

INSERT trigger, row variable new(new refers to the inserted row), new.id fetch emP table ID,

DELIMITER $
create trigger emp_logs_insert_trigger
after insert   Trigger this trigger after the insert operation
on emp 
for each row
begin         -- Trigger content
    insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
    values(null.'insert',now(),new.id,concat('After insertion (id:'),new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,') '));
end $
DELIMITER ;
Copy the code

Create an update trigger that logs data when it is updated:

DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
    insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
    values(null.'update',now(),new.id,concat('Before modification (id:'),old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,'), after modification (id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,') '));
end $
DELIMITER ;
Copy the code

Create a trigger for the DELETE row to log when the data is deleted:

DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null.'delete',now(),old.id,concat('Before deleting (id:'),old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,') '));
end $
DELIMITER ;
Copy the code

Testing:

insert into emp(id,name,age,salary) values(null.'Bright Left'.30.3500);
insert into emp(id,name,age,salary) values(null.'Bright Right Hand'.33.3200);
update emp set age = 39 where id = 3;
delete from emp where id = 5;
Copy the code

5.2 Deleting Triggers

drop trigger[schema_name.] trigger_NAME If schema_name is not specified, the default is the current database.Copy the code

Before the change (id: ‘old. Id’, name: ‘that old. The name,’, the age: ‘, old. Age, ‘, the salary is: ‘, old. Salary, ‘), modified (id ‘, new id, ‘name:’, the new name, ‘ age:’,new.age,’, salary:’,new.salary,’)’)); end $ DELIMITER ;

Create a trigger for the DELETE row to log when the data is deleted: ```sql DELIMITER $ create trigger emp_logs_delete_trigger after delete on emp for each row begin insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat(' delete before (id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')')); end $ DELIMITER ;Copy the code

Testing:

insert into emp(id,name,age,salary) values(null.'Bright Left'.30.3500);
insert into emp(id,name,age,salary) values(null.'Bright Right Hand'.33.3200);
update emp set age = 39 where id = 3;
delete from emp where id = 5;
Copy the code

5.2 Deleting Triggers

drop trigger[schema_name.] trigger_NAME If schema_name is not specified, the default is the current database.Copy the code