Super detailed illustration! Stored procedure, view, index, function, trigger

@TOC

1.1 Downloading the Linux Installation Package

Download address: dev.mysql.com/downloads/m…

Install MySQL 1.2

APACHE

1). Uninstall pre-installed in the centos mysql RPM - qa | grep -i mysql RPM -e mysql - libs5.171.1.el6.x86_64 --nodeps

2). Upload the mysql installation package Alt + p -------> put E:/test/ mysql5.622.1.el6.i686.rpm-bundle.tar

3). Decompress the mysql installation package mkdir mysql tar -xvf mysql5.622.1.el6.i686.rpm-bundle.tar -C /root/mysql

4Yum -y install libaio.so1. libgcc_s.so1. libstdc++.so6. libncurses.so. 5 --setopt=protected_multilib=false

	yum  update libstdc++4.47.- 4.el6.x86_64

5Install mysql-client RPM -ivh mysql-client5.622.1.el6.i686.rpm

6Install mysql-server RPM -ivh mysql-server5.622.1.el6.i686.rpm
Copy the code

1.3 Starting the MySQL Service

SQL

service mysql start

service mysql stop

service mysql status

service mysql restart
Copy the code

Log in MySQL 1.4

Mysql installation is completed, will automatically generate a random password, and stored in a password file: / root/mysql_secret mysql -u root -p after login, modify the password:set password = password('itcast'); Grant all privileges on *.* to'root' @The '%' identified by 'itcast';
flush privileges;
Copy the code

Index of 2.

2.1 Index Overview

MySQL defines an index as: An index is a data structure (ordered) that helps MySQL obtain data efficiently. In addition to the data, a database system maintains data structures that satisfy a particular lookup algorithm and that reference (point to) the data in some way so that advanced lookup algorithms can be implemented on those data structures, which are called indexes. As shown in the following schematic diagram:

On the left is the data table. There are seven records in two columns. On the far left is the physical address of the data record (note that logically adjacent records are not necessarily physically adjacent on disk). In order to speed up the search of Col2, a binary search tree shown on the right can be maintained. Each node contains the index key value and a pointer to the physical address of the corresponding data record, so that the corresponding data can be quickly obtained by using binary search.

Indexes themselves are generally too large to be stored entirely in memory, so they are often stored on disk as index files. Indexes are the most common tool used to improve performance in databases.

2.2 Index Advantages and Disadvantages

advantage

1) Similar to the catalog index of books, improve the efficiency of data retrieval and reduce the IO cost of the database.

2) Data is sorted by index columns to reduce the cost of data sorting and CPU consumption.

disadvantage

1) In fact, an index is also 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 index greatly improves query efficiency, it also reduces the speed of updating tables, such as INSERT, UPDATE, DELETE. When you update the table, MySQL not only saves the data, but also saves the index file. Every time the index column is added, MySQL will adjust the index information after the key value changes due to the update.

2.3 Index Structure

[White Whoring materials]

Indexes are implemented in the storage engine layer of MySQL, not in the server layer. Therefore, indexes of each storage engine may not be identical, and not all storage engines support all index types. MySQL currently provides the following four indexes:

• BTREE index: the most common index type. Most indexes support THE BTREE index.
• HASH index: Only supported by the Memory engine. Easy to use.
• R-tree Index (Spatial Index) : A spatial index is a special index type of the MyISAM engine. It is mainly used for geospatial data types, and is generally used less frequently.
• Full-text: full-text indexes are also a special index type of MyISAM, mainly used for full-text indexes. InnoDB supports full-text indexes from the Mysql5.6 release.

MyISAM, InnoDB and Memory storage engine support for various index types

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 refer to indexes, unless otherwise specified, we refer to indexes organized in B+ trees (multi-path search trees, not necessarily binary). By default, clustered indexes, compound indexes, prefix indexes, and unique indexes are all B+tree indexes.

2.3.1 BTREE structure

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

• Each node in the tree contains a maximum of m children.
• Every node, except root and leaf nodes, has at least [ceil(m/2)] children.
• If the root node is not a leaf node, there are at least two children.
• All the leaf nodes are on the same layer.
• Each non-leaf node consists of n keys and n+1 Pointers, where [ceil(m/2)-1] <= n <= m-1

For example, the number of keys for a 5-fork BTree is as follows: [ceil(m/2)-1] <= n <= m-1. So 2 <= n <=4. When n is greater than 4, the intermediate node is split to the parent node, and the nodes on both sides are split.

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

The evolution is as follows:

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

2). Insert H, n>4, the middle element G letter split up to the new node

3). Insert E, K, Q without splitting [white prostitute data]

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

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

6). Insert Z, and the middle element T is split up into the parent node

7). Insert D, the middle element D is split up into the parent node. And then you insert P, R, X, Y without splitting

8). Finally, insert S, NPQR node n>5, intermediate node Q splits upward, but after splitting, the parent node DGMT n>5, intermediate node M splits upward

At this point, the BTREE has been constructed. Compared with binary trees, BTREE is more efficient in querying data, because for the same amount of data, the hierarchy of BTREE is smaller than that of binary trees, so the search speed is faster.

2.3.3 B + TREE structure

B+Tree B+Tree B+Tree

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

2). B+Tree leaves save all key information in order of key size.

3). All non-leaf nodes can be regarded as the index part of the key.

Since B+Tree only stores key information on leaf nodes, query any key from root to leaf. Therefore, the query efficiency of B+Tree is more stable.

2.3.3 B+Tree in MySQL

The MySql index data structure is an optimization of the classic B+Tree. On the basis of the original B+Tree, a linked list pointer pointing to adjacent leaf nodes is added to form a B+Tree with sequential pointer, which improves the performance of interval access.

B+Tree index structure in MySQL

2.4 Index Classification

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

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

3) Composite index: a single index contains multiple columns

2.5 Index Syntax

Indexes Can be created at the same time when a table is created, or new indexes can be added at any time.

Prepare the environment:

create database demo_01 default charset=utf8mb4;

use demo_01;

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `city` (`city_id`, `city_name`, `country_id`) values(1.'xi 'an'.1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2.'NewYork'.2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3.'Beijing'.1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4.'Shanghai'.1);

insert into `country` (`country_id`, `country_name`) values(1.'China');
insert into `country` (`country_id`, `country_name`) values(2.'America');
insert into `country` (`country_id`, `country_name`) values(3.'Japan');
insert into `country` (`country_id`, `country_name`) values(4.'UK');
Copy the code

2.5.1 Creating indexes

Grammar:

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]
Copy the code

Example: create index for city_name in table city;

​

​

2.5.2 Viewing indexes

Grammar:

`show index from table_name; `Copy the code

Example: View index information in the city table.

2.5.3 Deleting an Index

Grammar:

SQL

DROP  INDEX  index_name  ON  tbl_name;
Copy the code

Select * from ‘city’ where ‘idx_city_name’ = ‘city’;

2.5.4 ALTER command

SQL

1). alter  table  tb_name  add  primary  key(column_list); This statement adds a primary key, which means that the index value must be unique and cannot beNULL

2). alter  table  tb_name  add  unique index_name(column_list); The value that this statement creates the index must be unique (exceptNULLOutside,NULLMay occur multiple times)3). alter  table  tb_name  add  index index_name(column_list); Add normal index, 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

2.6 Index Design Principles

Indexes can be designed according to the existing principles. When creating indexes, consider these principles to improve the efficiency of using indexes.

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

• For the selection of index fields, the best candidate columns should be extracted from the CONDITIONS of the WHERE clause, and if there are more combinations in the WHERE clause, the combination of the most commonly used and best-filtered columns should be selected.

• When using a unique index, the higher the distinction, the higher the efficiency of using the index.

• Indexes can effectively improve the efficiency of querying data, but more indexes is not better. The more indexes, the higher the cost of maintaining the index. For tables where DML operations are frequent, such as inserting, updating, and deleting tables, too many indexes introduce high maintenance costs, reducing the efficiency of DML operations, and increasing the time consumption of corresponding operations. In addition, MySQL can also suffer from selection problems if there are too many indexes, and although a usable index will still be found eventually, it will increase the selection cost.

• After a short index is created, the index is stored on a hard disk. Therefore, the I/O efficiency of index access and the overall access efficiency can be improved. If the total length of the index field is shorter, more index values can be stored in the given size of the storage block, which can effectively improve the I/O efficiency of MySQL to access the index.

• If N columns are combined with the leftmost prefix, then N indexes are created. If the first few columns of the index are used in the WHERE clause, then the query SQL can use the combined index to improve query efficiency.

Create composite index:CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS); Create an index on name; Create index for name, email; Create index for name, email, status;Copy the code

3. View

3.1 View Overview

A View is a table that exists virtually. The view does not actually exist in the database; the row and column data comes from the table used in the query that defines the view and is dynamically generated when the view is used. Colloquial, a view is the result set returned by the execution of a SELECT statement. So when we create the view, the main job is to create this SQL query statement.

The advantages of a view over a normal table include the following.

• Simple: The user who uses the view does not need to care about the corresponding table structure, association conditions, and filter conditions. The result set is already a filtered compound condition for the user.
• Security: View users can only access the result set they are allowed to query. Table permissions are not limited to specific rows and columns, but can be easily managed through views.
• Data independence: Once the structure of the view is determined, you can mask the impact of table structure changes on users. Adding columns to the source table has no impact on the view. If the source table changes the column name, you can change the view to solve the problem without affecting the visitors.

3.2 Creating or Modifying a View

The syntax for creating a view is:

SQL CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement  [WITH [CASCADED | LOCAL] CHECK OPTION]Copy the code

The syntax for modifying a view is:

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED |  LOCAL] CHECK OPTION]Copy the code

SQL

Options: WITH [CASCADED | LOCAL] CHECK OPTION ` determines whether to allow updating data records no longer meets the conditions of the view.

LOCAL: Updates can be made as long as the conditions of this view are met. CASCADED: The CASCADED view can be updated only when all the conditions for the view are met. The default value.Copy the code

For example, create a city_country_VIEW view and run the following SQL:

`create or replace view city_country_view 
as 
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;
Copy the code

Query view:

3.3 Viewing Views

Since MySQL 5.1, the SHOW TABLES command displays not only the names of TABLES but also the names of VIEWS. There is no SHOW VIEWS command that displays VIEWS separately.

Similarly, you can run the SHOW TABLE STATUS command to display not only TABLE information but also view information.

To query the definition of a VIEW, run the SHOW CREATE VIEW command.

3.4 Deleting a View

Grammar:

SQL `DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]Copy the code

Example, delete view city_country_view:

SQL

`DROP VIEW city_country_view ;
Copy the code

Stored procedures and functions

4.1 Overview of Stored Procedures and Functions

Stored procedures and functions are a set of SQL statements that have been compiled in advance and stored in the database. Calling stored procedures and functions can simplify a lot of work of application developers, reduce data transfer between the database and the application server, and improve the efficiency of data processing.

The difference between stored procedures and functions is that functions must have a return value, while stored procedures do not.

Function: is a procedure that returns a value;

Procedure: is a function that returns no value;

4.2 Creating a Stored Procedure

`CREATE PROCEDURE procedure_name ([proc_parameter[,...]])Begin -- end of the SQL statement;
Copy the code

Example:

delimiter $

create procedure pro_test1(a)
begin
	select 'Hello Mysql' ;
end$

delimiter ;
Copy the code

Knowledge Tips

DELIMITER

This keyword is used to declare the delimiter of the SQL statement, telling the MySQL interpreter whether the section of the command has ended and MySQL is ready to execute. By default, delimiter is a semicolon; . On the command line client, if a line of commands ends with a semicolon, mysql will execute the command if you press enter.

4.3 Calling a Stored Procedure

call procedure_name(a) ;
Copy the code

4.4 Viewing a Stored Procedure

Select name from mysql. Proc where db='db_name'; -- Query the status of a stored procedure. Show procedure status; -- Query the definition of a stored procedure show create procedure test.pro_test1 \G;Copy the code

4.5 Deleting a Stored Procedure

'DROP PROCEDURE [IF EXISTS] SP_NAME;Copy the code

4.6 grammar

Stored procedures are programmable, which means that you can use variables, expressions, and control structures to accomplish complex functions.

4.6.1 variable

• DECLARE

  DECLARE defines a local variable that is scoped only to BEGIN… In the END block.

SQL `DECLARE var_name[,...]  type [DEFAULT value]`Copy the code

Example:

 delimiter $

 create procedure pro_test2(a) 
 begin 
 	declare num int default 5;
 	select num+ 10; 
 end$

 delimiter ;
Copy the code

• SET

Direct assignment uses SET, which can be either a constant or an expression. The syntax is as follows:

NGINX SET var_name = expr [, var_name = expr] ... `Copy the code

Example:

 DELIMITER $

  CREATE  PROCEDURE pro_test3(a)
  BEGIN
  	DECLARE NAME VARCHAR(20);
  	SET NAME = 'MYSQL';
  	SELECT NAME ;
  END$

  DELIMITER ;
Copy the code

You can also use select… Into:

DELIMITER $

CREATE  PROCEDURE pro_test5(a)
BEGIN
	declare  countnum int;
	select count(*) into countnum from city;
	select countnum;
END$

DELIMITER ;
Copy the code

4.6.2 If condition judgment

Grammatical structure:

if search_condition then statement_list

	[elseif search_condition then statement_list] ...

	[else statement_list]

end if;
Copy the code

Requirements:

YAML determines the body type to which the current height belongs according to the defined height variable180And above ----------> Tall and tall170 - 180---------> Standard figure170Below ----------> Average figureCopy the code

Example:

delimiter $

create procedure pro_test6(a)
begin
  declare  height  int  default  175; 
  declare  description  varchar(50);

  if  height >= 180  then
    set description = 'Tall and tall';
  elseif height >= 170 and height < 180  then
    set description = 'Standard body';
  else
    set description = 'Average body';
  end if;

  select description ;
end$

delimiter ;
Copy the code

The result of the call is:

4.6.3 Passing parameters

Syntax format:

DELPHI

create procedure procedure_name([in/out/inout] Parameter name Parameter type). IN: This parameter can be used as input, that is, the value to be passed by the caller. Default OUT: This parameter can be used as output, that is, it can be used as a return value. INOUT: This parameter can be used as either an input or an output parameterCopy the code

Input the IN –

Requirements:

For example: ' 'C delimiter $create procedure pro_test5(in height int)
begin
    declare description varchar(50) default' ';
  if height >= 180 then
    set description='Tall and tall';
  elseif height >= 170 and height < 180 then
    set description='Standard body';
  else
    set description='Average body';
  end if;
  select concat('height', height , 'The corresponding body type is :',description);
end$

delimiter ;
Copy the code

Output the OUT –

Requirements:

Gets the body type 'to which the current height belongs, based on the passed height variableCopy the code

Example:

create procedure pro_test5(in height int , out description varchar(100))
begin
  if height >= 180 then
    set description='Tall and tall';
  elseif height >= 170 and height < 180 then
    set description='Standard body';
  else
    set description='Average body';
  end if;
end
Copy the code

Call:

NGINX

`call pro_test5(168, @description)$

select @description
Copy the code

The little knowledge

@description: This variable has an @ sign in front of its name. It is called a user session variable, which means that it will be used throughout the session. This is similar to global variables.

@@globally. sort_buffer_size: A variable preceded by an “@@” symbol is called a system variable

4.6.4 case structure

Grammatical structure:

CASE case_value WHEN when_value THEN statement_list [when_value THEN statement_list]... [ELSE statement_list] END CASE; CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list]... [ELSE statement_list] END CASE; `Copy the code

Requirements:

*   1'Given a month, then calculate the quarter'Copy the code

Example:

`delimiter $

create procedure pro_test9(month int)
begin
  declare result varchar(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 concat('The month you entered is :', month , ', the month is: ' , result) as content ; end$ delimiter ; `Copy the code

4.6.5 while loop

Grammatical structure:

while search_condition do

	statement_list

end while;
Copy the code

Requirements:

Calculation from `1To the value of nCopy the code

Example:

delimiter $

create procedure pro_test8(n int)
begin
  declare total int default 0;
  declare num int default 1;
  while num<=n do
    set total = total + num;
	set num = num + 1;
  end while;
  select total;
end$

delimiter ;

Copy the code

4.6.6 repeat structure

A conditional loop control statement that exits the loop when the condition is met. While is when the condition is met, repeat is when the condition is met and the loop exits.

Grammatical structure:

`REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;
Copy the code

Requirements:

Calculation from `1To the value of nCopy the code

Example:

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  
  end repeat;

  select total ;

end$

delimiter ;
Copy the code

4.6.7 loop statements

LOOP implements a simple LOOP. The condition for exiting the LOOP needs to be defined by another statement. Usually, the syntax of the LEAVE statement is as follows:

`[begin_label:] LOOP

  statement_list

END LOOP [end_label]`

Copy the code

If you do not add a statement to statement_list to exit the LOOP, the LOOP statement can be used to implement a simple infinite LOOP.

4.6.8 leave statement

Used to exit from the annotated process construct, usually with BEGIN… END or loop. Here’s a simple example of using LOOP and LEAVE to exit a LOOP:

delimiter $

CREATE PROCEDURE pro_test11(n int)
BEGIN
  declare total int default 0;

  ins: LOOP

    IF n <= 0 then
      leave ins;
    END IF;

    set total = total + n;
    set n = n - 1;

  END LOOP ins;

  select total;
END$

delimiter ;
Copy the code

4.6.9 Cursor/cursor

A cursor is a data type used to store a query result set, which can be used in stored procedures and functions to iterate over the result set. Use of the cursor includes cursor declaration, OPEN, FETCH, and CLOSE, whose syntax is as follows.

Declare the cursor:

SQL

`DECLARE cursor_name CURSOR FOR select_statement 
Copy the code

OPEN the cursor:

OPEN cursor_name ;
Copy the code

The FETCH cursor:

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

CLOSE the cursor:

CLOSE cursor_name ;
Copy the code

Example:

Initialize script:

create table emp(
  id int(11) not null auto_increment ,
  name varchar(50) not null comment 'name',
  age int(11) comment 'age',
  salary int(11) comment 'salaries',
  primary key(`id`)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,'The Golden Lion'.55.3800).(null,'The Eagle Of the White Eyebrow'.60.4000).(null,'King Green Winged Bat'.38.2800).(null,'Dragon King in Purple'.42.1800);
Copy the code

-- Query the data in the EMP table and obtain it row by row for displaycreate procedure pro_test11(a)
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;

  open emp_result;

  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', the salary is: ',e_salary);

  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', the salary is: ',e_salary);

  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', the salary is: ',e_salary);

  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', the salary is: ',e_salary);

  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', the salary is: ',e_salary);

  close emp_result;
end
Copy the code

Retrieve the data in the cursor via a loop structure:

`DELIMITER $

create procedure pro_test12(a)
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;

  open emp_result;

  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id',id, ', the name of ' ,name , ', the age for ' ,age , And the salary is: ', salary);
    until has_data = 0
  end repeat;

  close emp_result;
end$

DELIMITER ;
Copy the code

4.7 Storage Functions

Grammatical structure:

CREATE FUNCTION function_name([param type ... ] ) 
RETURNS type 
BEGIN
	...
END;
Copy the code

Case study:

Define a stored procedure that requests the total number of records that meet the condition;

delimiter $

create function count_city(countryId int)
returns int
begin
  declare cnum int ;

  select count(*) into cnum from city where country_id = countryId;

  return cnum;
end$

delimiter ;
Copy the code

Call:

CSHARP

`select count_city(1);

select count_city(2);
Copy the code

5. The trigger

5.1 introduction

A trigger is a database object associated with a table that triggers and executes a collection of SQL statements defined in the trigger before or after insert/ Update/DELETE. This feature of the trigger can be used on the database side to ensure data integrity, logging, data verification, and other operations.

The aliases OLD and NEW are used to refer to the changed record content in the trigger, similar to other databases. Trigger firing is currently only supported at row level, not statement level.

Flip-flop type	The use of NEW and OLD
INSERT trigger	NEW indicates the data to be added or has been 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 the data that will or has been deleted

5.2 Creating a Trigger

Grammatical structure:

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ forEach row] -- begin trigger_stmt; end;Copy the code

The sample

demand

Emp table data change log through trigger, including add, modify, delete;Copy the code

First create a log table:

Create table emp_logs(ID int(11) not NULL AUTO_increment, operation VARCHar (20) not NULL COMMENT ' Insert /update/delete', operate_time datetime not null COMMENT ', operate_id int(11) not NULL COMMENT ', Operate_params varchar(500) comment 'iD ', primary key(' id'))engine=innodb default charset= UTF8;Copy the code

Create an INSERT trigger to complete logging when inserting data:

`DELIMITER $

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat(Insert (id:').new.id,', name:'.new.name,', age:'.new.age,', salary:'.new.salary,') '));	
end $

DELIMITER ;
Copy the code

Create an update trigger to complete logging when data 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 change (id:'),old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,'), modified (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 complete the log record when deleting data:

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 make'.30.3500);
insert into emp(id,name,age,salary) values(null, 'Bright right make'.33.3200);

update emp set age = 39 where id = 3;

delete from emp where id = 5;
Copy the code

5.3 Deleting a Trigger

Grammatical structure:

drop trigger [schema_name.]trigger_name
Copy the code

If schema_name is not specified, the current database defaults.

5.4 Viewing Triggers

You can run the SHOW TRIGGERS command to view the status and syntax of the TRIGGERS.

Grammatical structure:

SQL ` show triggers;Copy the code

Finally, I wish you all an early success and satisfactionoffer, fastA promotion and pay increaseTo the top of my life.

Can please give me a three to support me?????? [White Whoring materials]