【MySQL进阶篇】二、SQL优化、视图/存储过程/触发器

参考资料

https://www.bilibili.com/video/BV1Kr4y1i7ru?p=89

1. SQL优化

1.1 插入数据

• insert优化

  • 批量插入

    Insert into tb_lest values(1,'Tom'),(2,'Cal'),(3.'Jerny');
    

  • 手动提交事务

    start transaction;
    insert into tb_test values(1, 'Tom').(2,'Cat').(3,'Beny');
    insert into tb_test values(4,'Tom').(5,'Car'),(6, 'lery');
    insert into tb_lest values(7,'lom').(B,'Cat'),(9,'jeny');
    cornmit;
    

  • 主键顺序插入
    主键乱序插入: 8 1 9 21 88 2 4 15 89 5 7 3
    主键顺序插入: 1 2 3 4 5 7 8 9 15 21 88 89
    主键顺序插入性能高于乱序插入
  • 大批量插入数据
    如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

    	#客户端连接服务端时,加上参数-loca-infile
    mysgl --local-infile -u root -p
    #设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
    set global local_infile=1;
    #执行load指令将准备好的数据,加载到表结构中
    load data local infile '/root/sgl1.log' inta table 'tb_user' fields terminated by ',' lines terminated by '\n';
    

1.2 主键优化

• 数据组织方式
  在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

• 页分裂
  页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。

• 页合并
  当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

• 主键设计原则

  • 满足业务需求的情况下,尽量降低主键的长度。
  • 插入数据时,尽量选择顺序插入,选择使用 AUTO_INCREMENT 自增主键。
  • 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
  • 业务操作时,避免对主键的修改。

1.3 order by优化

• Using filesort :通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直
  接返回排序结果的排序都叫FileSort排序。

• Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高。

• 示例:表数据生成见于【MySQL进阶篇】一、存储引擎、索引2.4.1节。

  #没有创建索引时,根据age, phone进行排序
  explain select id,age,phone from tb_user order by age, phone;
  #创建索引
  create index idx_user_age_phone_aa on tb_user(age,phone);
  #创建索引后,根据age, phone进行升序排序
  explain select id,age,phone from tb_user order by age, phone;
  #创建索引后,根据age, phone进行降序排序
  explain select id,age,phone from tb_user order by age desc, phone desc;
  #根据age, phone进行一个升序,一个降序：升序使用using index排序，降序使用filesort排序
  explain select id,age,phone from tb_user order by age asc, phone desc;
  #创建索引
  create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);
  #根据age, phone进行降序一个升序,一个降序
  explain select id,age,phone from tb_user order by age asc, phone desc;
  

• 注意事项

  • 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
  • 尽量使用覆盖索引。
  • 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC) 。
  • 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

1.4 group by优化

• 在分组操作时，可以通过索引来提高效率。

• 分组操作时，索引的使用也是满足最左前缀法则的

• 示例

  show index from tb_user;
  #删除掉目前的联合索引idx_user_pro_age_sta(如果有的话)
  drop index idx_user_pro_age_sta on tb_user;
  #执行分组操作,根据profession字段分组
  explain select profession, count(*) from tb_user group by profession;
  #创建索引
  Create index idx_user_pro_age_sta on tb_user(profession, age , status);
  #执行分组操作,根据profession字段分组
  explain select profession, count(*) from tb_user group by profession;
  #执行分组操作,根据profession字段分组
  explain select profession , count(*) from tb_user group by profession, age;
  

1.5 limit优化

• 一个常见又非常头疼的问题就是 limit 2000000,10 ，即返回2000000记录后的10记录，此时需要MySQL排序前2000010 记录，仅仅返回2000000 ~2000010的记录，其他记录丢弃，查询排序的代价非常大 。

• 优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

  explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
  

1.6 count 优化

• MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;
• InnoDB 引擎它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
• 优化思路：自己计数

1.6.1 count的几种用法

• count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加,最后返回累计值。

• 用法: count (*)、count (主键)、count (字段)、 count (1)

• count (主键)
  InnoDB引警会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)

• count (字段)

  • 没有not null 约束: InnoDB 引警会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加
  • 有not nuL约束:InnoDB引警会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加。

• count(1)
  InnoDB 引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加。

• count (*)
  InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。

• 按照效率排序的话， count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。

1.7 update优化

update student set no ='2000100100' where id = 1)
update student set no = '2000100105' where name = '-笑'

• InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

2. 视图/存储过程/触发器

2.1 视图

2.1.1 介绍

• 视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视
  图时动态生成的。
• 通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

2.1.2 操作

• 创建

  CREATE [OR REPLACE] VIEW 视图名[(列名列表)] AS SELECT语句[WITH [CASCADED | LOCAL] CHECK OPTION]
  

  create or replace view user as select id,name from tb_user where id<=10;
  

  创建时 [OR REPLACE]可省略

• 查询

  查看创建视图语句:SHOW CREATE VIEW 视图名称;
  查看视图数据: SELECT*FROM 视图名称...;
  

• 修改

  方式-: CREATE [OR REPLACE] VIEW 视图名[(列名列表)] AS SELECT语句[WITH [ CASCADED | LOCAL] CHECK OPTION]
  方式二: ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [ CASCADED | LOCAL] CHECK OPTION]
  

• 删除

DROP VIEW [IF EXISTS] 视图名称[视图名称]...

• 视图的检查选项

  • 当使用WITH CHECK OPTION子句创建视图时, MySQL会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其符合视图的定义。MySQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围, mysql提供了两个选项：CASCADED和 LOCAL,默认值为 CASCADED。
  • CASCADED：
    
  • LOCAL
    

• 视图的更新
  要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

  1. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
  2. DISTINCT
  3. GROUP BY
  4. HAVING
  5. UNION 或者 UNION ALL

2.1.3 视图作用

• 简单
  视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
• 安全
  数据库可以授权,但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
• 数据独立
  视图可帮助用户屏蔽真实表结构变化带来的影响。

2.1.4 案例

根据如下需求，定义视图

1. 为了保证数据库表的安全性,开发人员在操作tb_user表时,只能看到用户的基本字段,屏蔽手机号和邮箱两个字段。

   create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user;
   
   select * from tb_user_view;
   

2. 查询每个学生所选修的课程(三张表联查),这个功能在很多的业务中都有使用到,为了简化操作,定义一个视图

   相关表数据在【MySQL基础篇】四、约束、多表查询、事务中2.1节多表关系中。

   
   create view tb_stu_course_view as select s.name student_name , s.no student_no , c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;
   
   select * from tb_stu_course_view;
   

2.2 存储过程

2.2.1 介绍

• 存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

• 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用

• 特点

  • 封装，复用
  • 可以接收参数，也可以返回数据
  • 减少网络交互，效率提升

2.2.2 操作

• 创建

  CREATE PROCEDURE 存储过程名称([参数列表])
  BEGIN
  		--SQL语句
  END ;
  

  • 示例

    create procedure p1()
    begin
        select count(*) from student;
    end;
    

• 调用

  CALL 名称([参数])
  

  • 示例

    call p1();
    

• 查看

  SELECT* FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA= 'xxx';--查询指定数据库的存储过程及状态信息
  SHOW CREATE PRDCEDURE 存储过程名称;--查询某个存储过程的定义
  

  • 示例

    select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
    
    show create procedure p1;
    

• 删除

  DROP PROCEDURE [IF EXISTS ] 存储过程名称;
  

  • 示例

    drop procedure if exists p1;
    

  注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

2.2.3 变量

• 系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。
  • 查看系统变量

    --查看所有系统变量
    SHOW [SESSION | GLOBAL] VARIABLES;
    --可以通过LIKE模糊匹配方式查找变量
    SHOW [ SESSION | GLOBAL] VARIABLES LIKE ...;
    --查看指定变量的值
    SELECT @@[SESSION|GLOBAL] 系统变量名  ;
    

  • 设置系统变量

    SET [SESSION| GLOBAL]系统变量名= 值;
    SET @@[SESSION | GLOBAL]系统变量名=值;
    

  • 示例

    set session autocommit = 0; --0为取消自动提交
    
    insert into course(id, name) VALUES (7, 'ES');
    commit;--取消后需要自己手动提交
    

注意:
如果没有指定SESSION/GLOBAL,默认是SESSION,会话变量。
mysql服务重新启动之后,所设置的全局参数会失效,要想不失效,可以在/etc/my.cnf中配置。

• 用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前
  连接

  • 赋值

    SET @var_name= expr [ @var_name= expr]...;
    SET @var_name :=expr [ @var_name := expr] ..;
    SELECT @var_name:= expr [, @var_name:= expr]...;
    SELECT 字段名 INTO @var_name FROM 表名;
    

    • 示例

      set @myname = 'itcast';
      set @myage := 10;
      set @mygender := '男',@myhobby := 'java';
      
      select @mycolor := 'red';
      select count(*) into @mycount from tb_user;
      

  • 使用

    SELECT @var_name;
    

    • 示例

      select @myname,@myage,@mygender,@myhobby;
      
      select @mycolor , @mycount;
      
      select @abc;
      

• 局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。

  • 声明

    DECLARE变量名 变量类型(DEFAULT...];
    

    变量类型就是数据库字段类型：INT、 BIGINT、 CHAR、 VARCHAR、 DATE、 TIME等。
  • 赋值

    SET变量名=值;
    SET变量名:=值;
    SELECT 字段名 INTO 变量名 FROM 表名...;
    

  • 示例

    create procedure p2()
    begin
        declare stu_count int default 0;
        select count(*) into stu_count from student;
        select stu_count;
    end;
    
    call p2();
    
    

2.2.4 if

• 语法：

  IF条件1 THEN
  	...
  ELSEIF 条件2 THEN
  	...
  ELSE
  	...
  END IF;
  

• 练习:定义存储过程，完成如下需求

  根据定义的分数score变量，判定当前分数对应的分数等级。

  1. score >= 85分，等级为优秀。
  2. score >= 60分 且 score < 85分，等级为及格。
  3. score < 60分，等级为不及格。

  create procedure p3()
  begin
      declare score int default 58;
      declare result varchar(10);
  
      if score >= 85 then
          set result := '优秀';
      elseif score >= 60 then
          set result := '及格';
      else
          set result := '不及格';
      end if;
      select result;
  end;
  
  call p3();
  

2.2.5 参数

• 用法:

  CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
  BEGIN
  	--SQL语句
  END:
  

• 练习

  1. 根据传入参数score，判定当前分数对应的分数等级，并返回。
     ① score >= 85分，等级为优秀。
     ② score >= 60分 且 score < 85分，等级为及格。
     ③ score < 60分，等级为不及格。

     create procedure p4(in score int, out result varchar(10))
     begin
     
         if score >= 85 then
             set result := '优秀';
         elseif score >= 60 then
             set result := '及格';
         else
             set result := '不及格';
         end if;
     end;
     
     call p4(88,@result);
     select @result;
     

  2. 将传入的 200分制的分数,进行换算,换算成百分制 , 然后返回。

     create procedure p5(inout score double)
     begin
         set score := score * 0.5;
     end;
     
     set @score = 198;
     call p5(@score);
     select @score;
     

2.2.6 case

• 语法1：

  CASE case_value
  	WHEN when_valuel THEN statement_list
  	[WHEN when_value2 THEN statement_list2] ...
  	[ELSE statement_list]
  END CASE;
  

• 语法2：

  语法二
  CASE
  	WHEN search_condition1 THEN statement_list
  	[WHEN search_condition2 THEN statement_list2]...
  	[ELSE statement_list]
  END CASE;
  

• 练习：根据传入的月份，判定月份所属的季节（要求采用case结构）。

  1. 1-3月份，为第一季度
  2. 4-6月份，为第二季度
  3. 7-9月份，为第三季度
  4. 10-12月份，为第四季度

  create procedure p6(in month int)
  begin
      declare result varchar(10);
  
      case
          when month >= 1 and month <= 3 then
              set result := '第一季度';
          when month >= 4 and month <= 6 then
              set result := '第二季度';
          when month >= 7 and month <= 9 then
              set result := '第三季度';
          when month >= 10 and month <= 12 then
              set result := '第四季度';
          else
              set result := '非法参数';
      end case ;
  		# 字符串拼接
      select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
  end;
  
  call p6(6);
  

2.2.7 while

• while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

  #先判定条件,如果条件为true,则执行逻辑,否则,不执行逻辑
  WHILE 条件 DO
  	SQL逻辑...
  END WHILE;
  

• 练习:计算从1累加到n的值，n为传入的参数值。

  create procedure p7(in n int)
  begin
      declare result int default 0;
      while n>0 do
          set result = result+n;
          set n=n-1;
          end while;
      select result;
  end;
  
  call p7(100);
  

2.2.8 repeat

• repeat是有条件的循环控制语句, 当满足条件的时候退出循环 。具体语法为：

  #先执行一次逻辑,然后判定逻辑是否满足,如果满足,则退出。如果不满足,则继续下一次循环
  REPEAT
  	SQL逻辑...
  	UNTIL 条件
  END REPEAT;
  

• 练习:计算从1累加到n的值，n为传入的参数值。

  -- A. 定义局部变量, 记录累加之后的值;
  -- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
  create procedure p8(in 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;
  
  call p8(100);
  

2.2.9 loop

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：

• LEAVE：配合循环使用，退出循环。

• ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

  [begin_label]: LOOP
  	SQL逻辑...
  END LOOP [end_label];
  

退出指定标记的循环体
LEAVE label;
直接进入下一次循环
ITERATE label;

• 练习:计算从1累加到n的值，n为传入的参数值。

  create procedure p9(in n int)
  begin
      declare total int default 0;
  
      sum:loop
          if n<=0 then
              leave sum;
          end if;
  
          set total := total + n;
          set n := n - 1;
      end loop sum;
  
      select total;
  end;
  
  call p9(100);
  

• 练习：计算从1到n之间的偶数累加的值，n为传入的参数值。

  create procedure p10(in n int)
  begin
      declare total int default 0;
  
      sum:loop
          if n<=0 then
              leave sum;
          end if;
  
          if n%2 = 1 then
              set n := n - 1;
              iterate sum;
          end if;
  
          set total := total + n;
          set n := n - 1;
      end loop sum;
  
      select total;
  end;
  
  call p10(100);
  

2.2.10 游标

游标（CURSOR） 是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、 OPEN、 FETCH 和 CLOSE，其语法分别如下。

• 声明游标，存储查询结果集

  DECLARE 游标名称 CURSOR FOR查询语句;
  

• 打开游标

  OPEN 游标名称;
  

• 获取游标记录

  FETCH 游标名称 INTO 变量,变量...;
  

• 关闭游标

  CLOSE 游标名称;
  

• 练习
  根据传入的参数uage，来查询用户表 tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession）， 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

  create procedure p11(in uage int)
  begin
      declare uname varchar(100);
      declare upro varchar(100);
      -- A. 声明游标, 存储查询结果集
      declare u_cursor cursor for select name,profession from tb_user where age <= uage;
      -- B. 准备: 创建表结构
  #     drop table if exists tb_user_pro;
      create table if not exists tb_user_pro(
          id int primary key auto_increment,
          name varchar(100),
          profession varchar(100)
      );
      -- C. 开启游标
      open u_cursor;
      while true do
          -- D. 获取游标中的记录
          fetch u_cursor into uname,upro;
          -- E. 插入数据到新表中
          insert into tb_user_pro values (null, uname, upro);
      end while;
      -- F. 关闭游标
      close u_cursor;
  
  end;
  
  
  call p11(30);
  

  会报错，下一小节解决

2.2.11 条件处理程序

条件处理程序（Handler） 可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR condition_value, condition_value.. statement;

相应解释：

handler_action
	CONTINUE:继续执行当前程序
	EXIT:终止执行当前程序
condition_value
	SQLSTATE sqlstate_value: 状态码,如 02000
	SQLWARNING:所有以01开头的SQLSTATE代码的简写
	NOT FOUND:所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

更多解释可以查看官方文档

• 练习： 根据传入的参数uage，来查询用户表 tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession）， 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

  create procedure p11(in uage int)
  begin
      declare uname varchar(100);
      declare upro varchar(100);
      declare u_cursor cursor for select name,profession from tb_user where age <= uage;
      # 法1
      declare exit handler for SQLSTATE '02000' close u_cursor;
      # 法2
    # declare exit handler for not found close u_cursor;
  
      drop table if exists tb_user_pro;
      create table if not exists tb_user_pro(
          id int primary key auto_increment,
          name varchar(100),
          profession varchar(100)
      );
  
      open u_cursor;
      while true do
          fetch u_cursor into uname,upro;
          insert into tb_user_pro values (null, uname, upro);
      end while;
      close u_cursor;
  
  end;
  
  
  call p11(30);
  

2.3 存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic ...]
BEGIN
	--SQL语句
	RETURN...;
END;

characteristic说明:
	DETERMINISTIC:相同的输入参数总是产生相同的结果
	NO SQL:不包含SQL语句。
	READS SQL DATA:包含读取数据的语句,但不包含写入数据的语句。

• 练习：计算从1累加到n的值，n为传入的参数值。

  create function fun1(n int)
  returns int deterministic
  begin
      declare total int default 0;
  
      while n>0 do
          set total := total + n;
          set n := n - 1;
      end while;
  
      return total;
  end;
  
  
  select fun1(50);
  

2.4 触发器

2.4.1 介绍

• 触发器是与表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特
  性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
• 使用别名 OLD 和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器只支持行级触发，不支持语句级触发。

2.4.2 语法

• 创建

  CREATE TRIGGER trigger_name
  BEFORE/AFTER INSERT/UPDATE/DELETE
  ON tbl_name FOR EACH ROW --行级触发器
  BEGIN
  	trigger_stmt;
  END;
  

• 查看

  SHOW TRIGGERS;
  

• 删除

  DROP TRIGGER [schema_name.]trigger_name;--如果没有指定schema_name,默认为当前数据库。
  

2.4.2 练习

需求: 通过触发器记录 user 表的数据变更日志(user_logs) , 包含增加, 修改 , 删除 ;

• 准备工作 : 日志表 user_logs

  create table user_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 '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
  )engine=innodb default charset=utf8;
  

  -- 插入数据触发器
  create trigger tb_user_insert_trigger
      after insert on tb_user for each row
  begin
      insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
      (null, 'insert', now(), new.id, concat('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
  end;
  
  
  -- 查看
  show triggers ;
  
  -- 删除
  drop trigger tb_user_insert_trigger;
  
  -- 插入数据到tb_user
  insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());
  
  
  

  • 修改数据触发器

  -- 修改数据触发器
  create trigger tb_user_update_trigger
      after update on tb_user for each row
  begin
      insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
      (null, 'update', now(), new.id,
          concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
              ' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
  end;
  
  show triggers ;
  
  update tb_user set profession = '会计' where id = 23;
  
  update tb_user set profession = '会计' where id <= 5;
  

  • 删除数据触发器

  create trigger tb_user_delete_trigger
      after delete on tb_user for each row
  begin
      insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
      (null, 'delete', now(), old.id,
          concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
  end;
  
  show triggers ;
  
  
  delete from tb_user where id = 26;