进阶

进阶篇主要介绍 存储引擎、索引、SQL优化、视图/存储过程/触发器

存储引擎

MySQL体系结构

存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型

查看表使用的存储引擎：SHOW CREATE TABLE salgrade;

CREATE TABLE `salgrade` (
  `grade` int DEFAULT NULL,
  `losal` int DEFAULT NULL,
  `hisal` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='薪资等级表'

1. 在创建表时，指定存储引擎

      CREATE TABLE `salgrade` (
       字段1 字段1类型[COMMENT 字段1注释]
       字段n 字段n类型[COMMENT 字段n注释]
      ) ENGINE=InnoDB COMMENT='薪资等级表'
   

2. 查看当前数据库支持的存储引擎
   SHOW ENGINES;
   
3. 创建表并指定存储引擎

---创建表 my_myisam，并指定MYISAM存储引擎
create table my_myisam(
	id int,
	name varchar(10)
) engine = MyISAM;

--创建表 my_memory，指定Memory存储引擎
create table my_memory(
	id int,
	mame varchar(10)
)engine =Memory;

存储引擎特点

• InnoDB

  • 介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySOL5.5之后，InnoDB是默认的 MySQL 存储引擎
  • 特点
    DML操作遵循ACID模型，支持事务;
    行级锁，提高并发访问性能;支持外键 FOREIGN KEY约束，保证数据的完整性和正确性;
  • 文件
    xxx.ibd: xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。
    参数: innodb_file_per_table
    

  1. 表空间
     是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

  2. 段
     分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。

  3. 区
     表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。

  4. 页
     是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

  5. 行
     InnoDB 存储引擎数据是按行进行存放的。在行中，默认有两个隐藏字段：

     • Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。

     • Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

• InnoDB

  • MyISAM是MySQL早期的默认存储引擎。
  • 特点
    1. 不支持事务，不支持外键
    2. 支持表锁，不支持行锁
    3. 访问速度快

• Memory
  Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用

  特点

  1. 内存存放
  2. hash索引(默认)
  3. 文件 xxx.sdi:存储表结构信息

架构

• 概述
  MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发
  中使用非常广泛。下面是InnoDB架构图，上图为内存结构，下图为磁盘结构。
  
  在左侧的内存结构中，主要分为这么四大块儿：Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 接下来介绍一下这四个部分

• Buffer Pool （缓冲池）
  InnoDB存储引擎基于磁盘文件存储，在物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。

  在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

  缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

  缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

  • free page：空闲page，未被使用
  • clean page：被使用page，数据没有被修改过
  • dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致

  在专用服务器上，通常将多达80％的物理内存分配给缓冲池
  参数设置： show variables like 'innodb_buffer_pool_size';
  

• Change Buffer
  Change Buffer，更改缓冲区（针对于非唯一二级索引页）。在执行DML语句时，如果需要操作的数据Page没有在Buffer Pool中，那么不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中

  这是二级索引的结构图：
  

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组名。

• InnoDB:
  是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MISAM:
  如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。(现在一般使用MongoDB数据库)
• MEMORY:
  将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性(现在一般使用Redis)

索引

有关B+树相关概念请参考此篇文章-- (数据结构——B+树)

索引概述

索引(index) 是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种:

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。

• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。

• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
  

  • 聚集索引的叶子节点下挂的是这一行的数据 。

  • 二级索引的叶子节点下挂的是该字段值对应的主键值。

  当我们执行 select * from user where name = ‘arm’ 这个SQL语句时，具体的查找过程是什么样子的
  
  具体过程如下:

  1. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值10。
  2. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
  3. 最终拿到这一行的数据，直接返回即可。

这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

思考题

假设: 一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8，请问能存储多少数据？

• 高度为2时:
  先计算非叶子节点能存放多少键值和指针
  
  设可以设置非叶子节点有n个键值，因为键值是bigint类型，占用8字节，所以是n*8；指针占6字节，指针数比键值数多一个，所以是(n+1)*6；一行数据大小为1k，一页中可以存储16行这样的数据，所以总数是16*1024；计算公式如下：
  n*8+(n+1)*6=16*1024 算出n约为 1170 个键值，指针数比键值数多一个，所以有1171个指针，每个指针对应一页数据，一页中可以存储16行这样的数据。
  故1171*16=18736
• 高度为3:
  1171 * 1171 * 16=21939856

索引-语法

• 创建索引
  CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name...) ;
  一个索引关联了单个字段这个索引是单列索引，一个索引关联了多个字段这个索引是联合索引
• 查看索引
  SHOW INDEX FROM table_name ;
• 删除索引
  DROP INDEX index_name ON table_name;

题目
按照下列的需求，完成索引的创建

1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
   CREATE INDEX idx_user_name ON USER(username);
2. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引
   CREATE UNIQUE INDEX idx_user_phone ON USER(phone);
3. 为gender、age、status创建联合索引
   CREATE INDEX idx_gender_age_status ON USER(age,STATUS,gender);
4. 为email建立合适的索引来提升查询效率
   CREATE INDEX idx_user_email ON USER(email);

SQL性能优化

SQL性能分析

• SQL执行频率
  MySQL客户端连接成功后，通过 show [session | global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT的访问频次:
  SHOW GLOBAL STATUS LIKE 'Com_______'
  
  再次查询emp表，会发现 Com_select 值会➕1
  

注意：
如果你使用sqlyog这样的客户端查询执行次数，会发现统计次数不是➕1而是➕3。这是因为GUI 客户端（如 Navicat、SQLyog）会自动执行额外的查询，这些查询也会被统计到 Com_select 计数器中。
首先，我们要知道Com_select 是全局状态变量，统计的是 MySQL 服务器自启动以来所有 SELECT 语句的执行次数，包括：

• 你手动执行的查询
• 客户端自动发起的查询
• 存储过程 / 触发器内部的查询

当你手动执行 SELECT * FROM emp; 查询，这是第 1 次 SELECT，会让 Com_select +1。
GUI 客户端为了展示结果、获取表结构或元数据，会自动在后台执行额外的 SELECT 语句，例如：

• 查询表结构 / 字段信息：SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = ‘emp’ …
• 统计结果集行数：SELECT COUNT(*) FROM emp 或类似分页查询
• 刷新状态 / 获取表统计信息

这些后台查询都会被 MySQL 统计为 Com_select，所以每次你在sqlyog中手动执行一次查询，实际会触发 3 次 SELECT 操作。
效果如下：

执行一次select操作，Com_select +3

• 慢查询日志
  慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。
  MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件( /etc/my.cnf )中配置。
  • 查看慢查询日志是否开启语句：SHOW VARIABLES LIKE 'slow_query_log';
  • 查看慢查询日志存放路径语句：SHOW VARIABLES LIKE 'slow_query_log_file';
  • 查询慢查询时间设置语句：SHOW VARIABLES LIKE 'long_query_time';

  # 开启MySOL慢日志查询开关
  slow-query-log=1
  # 设置慢日志的时间为10秒，SOL语句执行时间超过10秒，就会视为慢查询，记录慢查询日志
  long_query_time=10
  

配置完毕之后，通过指令重新启动MySOL服务器进行测试，查看慢日志文件中记录的信息

• linux操作系统下目录：/var/lib/mysql/localhost-slow.log
• windows操作系统下目录：一般在MySQL安装目录下的data文件夹下

打开slow.log日志，内容长这样：

SQL性能分析

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持

profile操作:

SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在 session/global 级别开启 profiling:

SET profiling=1;

查看所有SQL语句的执行耗时：SHOW PROFILES;

执行一系列的业务SOL的操作，然后通过如下指令查看指令的执行耗时

• 查看每一条SOL的耗时基本情况
  show profiles;
  
• 查看指定query_id的SOL语句各个阶段的耗时情况
  show profile for query query_id;
  
• 查看指定query_id的SQL语句CPU的使用情况
  show profile cpu for query query_id;
  

explain执行计划

• explain执行计划
  EXPLAIN 或者 DESC 命令获取 MySOL如何执行 SELECT语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序,语法如下:

#直接在select语句之前加上关键字 explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件

EXPLAIN 执行计划各字段含义:

• id
  select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。

  • id相同
    
  • id不同
    SELECT * FROM emp WHERE dept_id = (SELECT dept_id FROM dept WHERE id = (SELECT id FROM USER WHERE age IN (20)));
    这里先执行id=3的user表，再执行id=2的dept表，最后执行id=1的emp表
    

• select type
  表示 SELECT的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等

• type
  表示连接类型，连接类型的性能由好到差排序为NULL>system>const>eg_ref>ref>range>index>all
  尽量往前优化，null是最好的，但是不太可能，因为一般情况下只有不查询任何表的时候连接类型才会是null，
  唯一索引出现const
  eq_ref 在主键索引或者唯一性索引用于做被驱动表的连接字段时就会出现

• possible_key
  显示可能应用在这张表上的索引，一个或多个

• Key
  实际使用的索引，如果为NULL，则没有使用索引

• Key_len
  表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

• ROWS
  MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的

• filtered
  表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。

索引使用原则

索引使用

• 验证索引效率
  在未建立索引之前，执行如下SQL语句，查看SOL的耗时。
  SELECT * FROM tb_sku WHERE sn=‘100000003145001’;

• 针对字段创建索引
  create index idx_sku_sn on tb_sku(sn);
  会然后再次执行相同的SQL语句，再次查看SOL的耗时

• 最左前缀法则（联合索引最左边的列必须存在，中间不允许跳过任何列）
  如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
  如果跳跃某一列，索引将部分失效(后面的字段索引失效)

• 演示：我们在user表上创建索引
  CREATE INDEX idx_user_age_status_gender_phone ON USER(age,STATUS,phone);
  根据最左前缀法则执行SQL语句 EXPLAIN SELECT * FROM USER WHERE age=21 AND STATUS= 1 AND phone=‘19069575328’
  
  如果我们不按照最左前缀法则查询执行SQL语句
  EXPLAIN SELECT * FROM USER WHERE phone = ‘19069575328’ AND STATUS = 1 AND age = 21 ;
  

• SQL提示
  是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

  use index:
  explain select * from user use index(idx_user_pro) where profession ='软件工程”;
  ignore index:
  explain select * from user ignore index(idx_user_pro) where profession='软件工程;
  force index:
  explain select * from user force index(idx_user_pro) where profession ='软件工程';
  

  • 案例1，忽略 idx_user_phone 索引
    EXPLAIN SELECT * FROM USER IGNORE INDEX(idx_user_phone) WHERE phone = ‘19069575328’ OR age = 20
    
  • 案例2，强制使用 索引
    EXPLAIN SELECT * FROM USER FORCE INDEX(idx_user_age_status_phone) WHERE age = 20 OR phone = ‘19069575328’
    

• 覆盖索引
  尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select*

  • usingindex condition: 查找使用了索引，但是需要回表查询数据
  • using where; usingindex: 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

• 前缀索引
  当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询效率。
  此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
  语法
  create index idx_xxxx on table_name(column(n)); 获取字段前n个字符

  • 如何来决定n大小？
    可以根据索引的选择性来决定前缀长度，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

    select count(distinct email) /count(*)from tb user ;
    SELECT COUNT(DISTINCT SUBSTRING(phone,1,5))/ COUNT(*) FROM USER ;
    

    
  • 前缀索引的数据结构
    

• 单列索引与联合索引
  单列索引: 即一个索引只包含单个列。
  联合索引: 即一个索引包含了多个列，
  在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
  单列索引情况:
  explain select id, phone, name from user where phone= ‘17799990010’ and name =‘李四’；
  多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

索引设计原则

• 针对于数据量较大，且查询比较频繁的表建立索引。
• 针对于常作为查询条件(where)、排序(orderby)、分组(group by)操作的字段建立索引
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

插入数据

insert优化

• 批量插入
  Insert into test values(1,‘Tom’),(2,‘cat’),(3, ‘jerry’)
• 手动提交事务
• 主键顺序插入

大批量插入数据
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令加载本地文件进行插入。操作如下:

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

主键优化

• 数据组织方式
  在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table lOT)
• 页分裂
  页可以为空，也可以填充一半，也可以填充100%。每个页包含了 2 到 N 行数据(如果一行数据多大，会导致行溢出)，根据主键排列
  • 主键顺序插入
    
  • 主键乱序插入
    刚开始插入50这一行数据
    
    此时将23、47移动到新开辟的数据页（3# page），然后再将50 插入到新的数据页中
    
    再将数据链表指针重新设置
    
    
    此为页分裂
• 页合并
  当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE THRESHOLD (默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
  
  
• 主键设计原则
  • 满足业务需求的情况下，尽量降低主键的长度。
  • 插入数据时，尽量选择顺序插入（否则会出现页分裂现象），选择使用AUTOINCREMENT自增主键
  • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
  • 业务操作时，避免对主键的修改

这要从聚集索引和二级索引的数据结构说起，二级索引的叶子节点放的都是主键，如果主键过长二级索引比较多，就会占用大量的磁盘空间，搜索的时候会耗费大量的磁盘I/O

order by优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
2. Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

对order by优化主要是通过索引进行操作，尽量使用覆盖索引，

group by优化

1. 在分组操作时，可以通过索引来提高效率
2. 分组操作时，索引的使用也是满足最左前缀法则的

limit 优化

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

官方给出的优化方案是通过覆盖索引+优化子查询来优化
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

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

count优化

explain select count(*)from user

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count()的时候会直接返回这个数，效率很高;
InnoDB 引擎就麻烦了，它执行 count()的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

• count的几种用法
  • count(主键)
    InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)。
  • count(字段)
    没有not null约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
    有not null 约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
  • count(1)
    InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
  • count(*)
    InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

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

update 优化

注意：
update更新时，条件字段最好是有索引的字段或者主键字段，这样加的是行锁。否则就是表锁，并发程度更低

UPDATE USER SET age =22 WHERE id=1;

UPDATE USER SET age =22 WHERE NAME= 'zhangsan';

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

视图 / 存储过程&存储函数 / 触发器

视图

视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上

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

  CREATE OR REPLACE VIEW emp_v_1 AS SELECT id,NAME,age FROM emp; 
  

• 查询
  查看创建视图语句:
  SHOW CREATE VIEW 视图名称
  
  查看视图数据
  SELECT * FROM 视图名称 [where 条件]
  
  
• 修改
  方式一:
  CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

  CREATE OR REPLACE VIEW emp_v_1 AS SELECT * FROM emp WHERE dept_id != 5;
  

  方式二:
  ALTER VIEW 视图名称[(列名列表)] AS SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
  
• 删除
  DROP VIEW [IF EXISTS] 视图名称 ;
  
  
  

视图-检查选项

创建视图
CREATE OR REPLACE VIEW emp_v_1 AS SELECT * FROM emp WHERE id <= 10;

我们需要知道 视图并不存储数据，存储数据是在基表中插入的

当我们执行插入数据语句
INSERT INTO emp_v_1 (id,NAME,age,job,salary,entrydate,manager,dept_id) VALUE (30,'孙齐',35,'前端组长',25000,NOW(),3,5);
此时数据并没有插入到视图中，而是直接插入到emp表中的。

明明我们设置了插入条件（id<=10），执行插入语句也能成功，怎么回避这个问题呢？

解决方法：
在创建视图时增加检查选项 WITH CHECK OPTION，当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义

CREATE OR REPLACE VIEW emp_v_1 AS SELECT * FROM emp WHERE dept_id != 5 WITH CHECK OPTION;

此时我们再插入数据时就会报错，图如下：

MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项:CASCADED和LOCAL，默认值为CASCADED

cascaded翻译过来就是级联，当一张视图基于另一张视图创建时，他不仅要看当前视图的操作是否满足条件，还要满足当前视图所依赖的底层视图的操作是否满足条件

示例：
我们基于v_1创建V_2视图，SQL如下：

CREATE OR REPLACE VIEW emp_v_1 AS SELECT * FROM emp WHERE dept_id != 5 WITH CHECK OPTION;

CREATE OR REPLACE VIEW emp_v_2 AS SELECT * FROM emp_v_1 WHERE id >= 30 WITH CASCADED CHECK OPTION;

两张视图的条件并不相同，此时插入数据

INSERT INTO emp_v_2 (id,NAME,age,job,salary,entrydate,manager,dept_id)VALUE(27,'孙齐',35,'前端组长',25000,NOW(),3,5);

因为id=27并不符合V_2图的条件，所以插入失败

当我们插入一条不符合第一条视图条件的数据,他也会显示插入失败：

INSERT INTO emp_v_2 (id,NAME,age,job,salary,entrydate,manager,dept_id)VALUE(31,'孙齐',35,'前端组长',25000,NOW(),3,5);

只有当插入的数据同时符合V_1和V_2条件时才能插入。

我们创建V_3视图且没有添加检查选项 WITH CHECK OPTION 后，再插入数据时，因为没有添加检查选项 WITH CHECK OPTION 导致不符合V_3视图条件的数据再次插入了

修改插入数据，依然插入不进去，因为不符合V_2的视图条件

• 视图检查选项
  当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL提供了两个选项:CASCADED和LOCAL，默认值为 CASCADED。
  
  当我们操作视图（修改，插入，删除），他会递归的查找当前视图所依赖的视图，如果当前视图以及所依赖的视图有with check option选项，会判定我们操作的数据是否满足视图的条件，如果没有with check option选项，则不会检查数据

视图-更新

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

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

创建视图emp_v_4并使用聚合函数count()：

此时我们在插入数据会报错，视图不可以插入也不可以更新：

视图-作用

• 简单
  视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询SQL可以被定义为视图，从而使得用户不必为以后每次写查询SQL时每次都要指定全部的查询条件。
• 安全
  数据库的用户操作管理员可以授权，但授权只能控制到表，并不能控制到数据库表中特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据，保证敏感数据的安全性
• 数据独立
  视图可帮助用户屏蔽真实表结构变化带来的影响
  就是如果基表结构有变化 比如名字改了 视图就可以重定别名来屏蔽这种变化 用户看到的还是没有变

案例

1. 为了保证数据库表的安全性，开发人员在操作 user 表时，只能看到的用户的基本字段，屏蔽手机号字段
   
2. 查询每个用户及其所在的部门(两张表联查)，这个功能在很多的业务中都有使用到，为了简化操作，定义一个视
   图
   

存储过程

介绍
存储过程是事先经过编译并存储在数据库中的一段 SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SOL语言层面的代码封装与重用
其特点有：

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

• 创建

  我用的软件是SQLyog，在创建存储过程中出现 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 这样的报错，查百度才知道是语句结束符冲突：MySQL 默认以 ; 作为语句结束符，当编写存储过程时，存储过程内部的 ; 会被 MySQL 误认为是整个 CREATE PROCEDURE 语句的结束，导致解析提前终止，报语法错误。为避免这样的错，需要通过 DELIMITER 关键字临时修改语句结束符。

  -- 1. 临时修改语句结束符为 $$（可自定义，如//、###等，避免与;冲突）
  DELIMITER $$
  
  -- 2. 创建存储过程
  CREATE PROCEDURE 存储过程名称([参数列表])
  BEGIN
      -- 存储过程核心逻辑：查询dept表的行数
      --SQL;
  END $$  -- 用自定义结束符标记存储过程结束
  
  -- 3. 恢复默认结束符;
  DELIMITER ;
  

• 调用

  CALL 名称([参数 ]);
  

示例：

DELIMITER $$      -- 将结束符临时改为 $$
CREATE PROCEDURE p1()
BEGIN
SELECT COUNT(*) FROM dept
END;
DELIMITER ;       -- 恢复默认结束符为 ;

--调用
CALL p1();

结果如下：

• 查看

  SELECT* FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xxx'; --从MySQL自带的ROUTINES 这张表中查询指定数据库的所有的存储过程及状态信息,可以在where后面加条件限定数据库名字
  SHOW CREATE PROCEDURE 存储过程名称;-- 查询某个存储过程的定义
  

  
  
• 删除
  DROP PROCEDURE IF EXISTS 存储过程名1;

系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局系统变量(GLOBAL针对所有会话都有效)、会话系统变量(SESSION仅对当前会话有效)

• 查看/设置 系统变量

  SHOW [SESSION|GLOBAL] VARIABLES;  --查看所有系统变量（没加级别默认查看的是会话系统变量）
  
  SHOW [SESSION|GLOBAL] VARIABLES LIKE '.…';  -- 可以通过LKE模糊匹配方式查找变量
  
  SELECT @@系统变量名;  --查看指定变量的值
  
  SELECT @@[SESSION|GLOBAL].系统变量名;  --查看全局/会话变量中指定变量的值
  

  效果截图：
  

  

  

  

• 设置系统变量

  SET [SESSION|GLOBAL] 系统变量名 = 值; 
  --也可以写成这样
  SET @@[SESSION|GLOBAL] 系统变量名 = 值;
  

  如果没有指定SESSION/GLOBAL，默认是会话变量(SESSION)，变更session级别的系统变量只会影响当前会话,当重新开启一个会话则不会影响当前会话的系统变量。

  注意：
  如果你使用SQLyog客户端工具连接数据库，当你设置系统变量名后再开启一个查询页面查询变量值，你会发现值没有改变，不是你设置失败了，而是因为这类工具的多标签页默认复用同一个数据库连接：打开新的 “查询” 标签页时，并没有建立新的 MySQL 连接，而是复用了已有的连接。所以两个标签页本质上是同一个会话，autocommit 状态自然保持一致。
  你需要换个客户端连接数据库再查询

  效果复现 :

  //设置
  SET SESSION autocommit = 0;
  //查询设置结果
  SELECT @@session.autocommit ;
  

  
  

  注意：
  设置的全局参数都会生效，但是当重启数据库后，设置的参数会回复到初始值。

  如想永久修改参数需要修改MySQL中的系统配置文件

  • 服务器是linux操作系统 可以在 /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 表名;
  
  //使用
  select @var_name;
  

  推荐使用 := 赋值，因为MySQL中 = 符号既可以作为比较运算符也可以作为赋值运算符

  结果如图所示：
  

  注意：
  用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL
  

局部变量

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

• 声明

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

  变量类型就是数据库字段类型: INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

• 赋值

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

  具体示例如下：

  DELIMITER $$
  CREATE PROCEDURE p2()
  BEGIN
    DECLARE user_count INT DEFAULT 0;
    SET user_count :=100;
  END$$
  CALL p2();
  
  DELIMITER $$
  CREATE PROCEDURE p3()
  BEGIN
     DECLARE user_count INT DEFAULT 0;
     SELECT COUNT(*) INTO user_count FROM USER;
     SELECT user_count;
  END;
  CALL p3();
  

  

• if语法

  IF 条件1 THEN
  	....
  ELSEIF 条件2 THEN	--可选
  	....
  ELSE				--可选
  	....
  END IF;
  

  具体示例：
  根据定义的分数score变量，判定当前分数对应的分数等级。score>=85分，等级为优秀。score >=60分且score<85分，等级为及格。score<60分，等级为不及格。

  结果如图：
  

参数（in，out，inout）

类型	含义	备注
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

用法：

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

具体示例：

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

    DELIMITER $$
    CREATE PROCEDURE p5(IN score INT,OUT result VARCHAR(10))
    BEGIN
      
      IF score >=80 THEN 
    	SET result := '优秀';
      ELSEIF score >=60 THEN
    	SET result := '及格';
      ELSE
    	SET result := '不及格';
      END IF;
    END$$
    -- 调用
    CALL p5(98, @result);
    SELECT @result;
   

   结果如图所示：
   
2. 总分150分，传入一个分数并将其换算成百分制，换算后判断成绩等级

   DELIMITER $$
   CREATE PROCEDURE p6(IN score DOUBLE,OUT result VARCHAR(10))
   BEGIN
     
     SET score := score * 2/3;
     IF score >=80 THEN 
   	SET result := '优秀';
     ELSEIF score >=60 THEN
   	SET result := '及格';
     ELSE
   	SET result := '不及格';
     END IF;
   END$$
   
   CALL p6(100, @result);
   SELECT @result;
   

结果如图：

case语句

语法:

CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2]...
	[ELSE statement_list]
END CASE;

还是以 总分150分，传入一个分数并将其换算成百分制，换算后判断成绩等级 这道题为例，代码如下：

DELIMITER $$
CREATE PROCEDURE p7(IN score DOUBLE)
BEGIN
  -- ✅ 变量声明必须放在最前面！
  DECLARE result VARCHAR(10);
  
  SET score := score*2/3;
  CASE  
	WHEN score >=80 THEN SET result := '优秀';
	WHEN score >=60 THEN SET result := '及格';
	ELSE SET result := '不及格';
  END CASE;
  SELECT CONCAT('您的成绩等级是：',result);
END$$

CALL p7(120);
SELECT @result;

while循环

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

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

示例：输入一个数字n，计算从1开始累加到n的总和

DROP PROCEDURE IF EXISTS p8;
DELIMITER $$
CREATE PROCEDURE p8(IN num INT)
BEGIN
  -- 输入一个数字，计算从1~n累加的和
  -- 
  DECLARE sumnum INT DEFAULT 0;
  DECLARE n INT DEFAULT 1;
  WHILE num>=n DO
    SET sumnum := sumnum + n;
    SET n := n +1;
  END WHILE;
  SELECT CONCAT('您的累加结果是：',sumnum);
END $$
  
CALL p8(100);
SELECT @sumnum;  

结果显示：

repeat

repeat是有条件的循环控制语句,当满足条件的时候退出循环。具体语法为:#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环

REPEAT
     SQL逻辑...
     UNTIL 条件
END REPEAT;

示例：计算从1累加到n的值，n为传入的参数值。

DELIMITER $$
CREATE PROCEDURE p9(IN num INT)
BEGIN
  -- 输入一个数字，计算从1~n累加的和
  -- 
  DECLARE sumnum INT DEFAULT 0;
  DECLARE n INT DEFAULT 1;
  REPEAT
    SET sumnum := sumnum + n;
    SET n := n +1;
    UNTIL num < n  -- 后面不能加;分号
  END REPEAT;
  SELECT CONCAT('您的累加结果是：',sumnum);
END $$
  
CALL p9(100);
SELECT @sumnum;  

截图如下：

loop

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

• LEAVE:配合循环使用，退出循环。
• ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label],
LEAVE label; --退出指定标记的循环体
ITERATE label; --直接进入下一次循环

示例1. 计算从1累加到n的值，n为传入的参数值。

DROP PROCEDURE IF EXISTS p10;
DELIMITER $$
CREATE PROCEDURE p10(IN num INT)
BEGIN
  DECLARE sumnum INT DEFAULT 0;
  DECLARE n INT DEFAULT 1;
  -- 输入一个数字，计算从1~n累加的和
  sumnum:LOOP
    -- 先累加，再判断退出，确保num被计入
    SET sumnum := sumnum + n;
    SET n := n + 1;
    -- 当n > num时退出，覆盖1~num的所有数
    IF n > num THEN
      LEAVE sumnum;
    END IF;
  END LOOP sumnum;
  SELECT CONCAT('您的累加结果是：',sumnum);
END $$
  
CALL p10(110);

示例2. 计算从1到n之间的偶数累加的值，n为传入的参数值

DELIMITER $$
CREATE PROCEDURE p10(IN num INT)
BEGIN
  DECLARE sumnum INT DEFAULT 0;
  DECLARE n INT DEFAULT 1;
  -- 输入一个数字，计算从1~n累加的和
  sumnum:LOOP
    
    -- 当n > num时退出，覆盖1~num的所有数
    IF n > num THEN
      LEAVE sumnum;
    END IF;
    -- 对n取模，当n取模后值为1时表示该数是奇数，跳出循环
    IF n%2=1 THEN 
      SET n := n+1;
      ITERATE sumnum;
    END IF;
    
    SET sumnum := sumnum + n;
    SET n := n + 1;
  END LOOP sumnum;
  SELECT CONCAT('您的累加结果是：',sumnum);
END $$
  
CALL p10(100);

## 或者可以这样写
DELIMITER $$
CREATE PROCEDURE p10(IN num INT)
BEGIN
  DECLARE sumnum INT DEFAULT 0;
  DECLARE n INT DEFAULT 1;
  -- 输入一个数字，计算从1~n累加的和
  sumnum:LOOP
    
    -- 当n > num时退出，覆盖1~num的所有数
    IF n > num THEN
      LEAVE sumnum;
    END IF;
    -- 对n取模，当n取模后值为1时表示该数是奇数，跳出循环
    IF n%2=0 THEN 
      SET sumnum := sumnum + n;
      SET n := n + 1;
    END IF;
    
  END LOOP sumnum;
  SELECT CONCAT('您的累加结果是：',sumnum);
END $$
  
CALL p10(10);
SELECT @sumnum; 

存储函数

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

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

characteristic说明:

• DETERMINISTIC:相同的输入参数总是产生相同的结果
• NO SQL :不包含 5QL语句。
• READS SQL DATA:包含读取数据的语句，但不包含写入数据的语句。

示例1. 计算从1累加到n的值，n为传入的参数值。

DELIMITER $$
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(100);

结果如下：

触发器

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

触发器类型	new 和 old
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

• 创建

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

• 查看

  show TRIGGER 
  

• 删除

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

示例：
通过触发器记录 user 表的数据变更日志，将变更日志插入到日志表user_logs中,包含增加,修改，删除;
创建user_logs表（代码不在此展示）创建触发器，当插入user表数据后，会在user_logs表中再插入一条记录数据，
触发器创建语句：

-- 插入数据时触发器
DELIMITER $$
CREATE TRIGGER user_insert_trigger
  AFTER INSERT ON 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, ',username=', new.username, ', phone=', new.phone));
END$$

输入一条插入语句，展示效果
INSERT INTO USER (username,age,STATUS,gender,phone) VALUE (‘周五’,18,1,‘女’,‘18658988684’)

注意：
触发器中 new.字段必须要和原表中的字段相同，即 NEW.xxx 取的是 原表 的字段值，否则触发器会获取不到数据

-- 修改数据时触发器
DELIMITER $$
CREATE TRIGGER user_update_trigger
  AFTER UPDATE ON 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, ',username=', old.username,', age =', old.age, ',status = ', old.status, ',phone=', old.phone,
                ' | 更新后的数据内容为：id=', new.id, ',username=', new.username,', age =', new.age, ',status = ', new.status, ',phone=', new.phone));
END$$

输入一条更新语句，展示效果
UPDATE USER SET age = 99,STATUS = 2, phone = ‘18673816789’ WHERE id = 3

效果我们是展示出来了，但是还有待优化的地方，每次更新一个字段都会记录整个更新内容，那能不能每次只记录更新的内容呢？答案是可以的优化后的SQL如下：

-- 修改数据时触发器
DELIMITER $$
CREATE TRIGGER user_update_trigger
  AFTER UPDATE ON USER FOR EACH ROW
BEGIN
  
  DECLARE log_msg VARCHAR(1000) DEFAULT '';
  -- 判断每个字段是否被修改，只拼接变化的字段
  IF OLD.username != NEW.username THEN
    SET log_msg = CONCAT(log_msg, 'username：', OLD.username, ' → ', NEW.username, ' ');
  END IF;
  IF old.age != new.age THEN
    SET log_msg = CONCAT(log_msg, 'age: ', old.age, ' → ', new.age, ' ');
  END IF;
  IF old.status != new.status THEN
    SET log_msg = CONCAT(log_msg, 'status: ', old.status, ' → ', new.status, ' ');
  END IF;
  IF old.phone != new.phone THEN
    SET log_msg = CONCAT(log_msg, 'phone: ', old.phone, ' → ', new.phone, ' ');
  END IF;
  -- 判断内容是否更新
  IF log_msg != '' THEN 
    -- 插入日志
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
                VALUES (NULL, 'UPDATE', NOW(), NEW.id, CONCAT('更新的内容是：', log_msg));
  END IF;
END$$

那假如我是范围更新，比如我更新 id <= 3 ，那触发器会执行几次呢？

答案是3次，因为触发器是行级触发器(FOR EACH ROW)，每影响一行就会触发一次

效果复现：

DROP TRIGGER user_delete_trigger;
-- 删除数据时触发器
DELIMITER $$
CREATE TRIGGER user_delete_trigger
  AFTER DELETE ON 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,',username=', OLD.username,',age=', OLD.age,',status=', OLD.status,',phone=', OLD.phone));
END$$

SELECT * FROM USER;
DELETE FROM USER WHERE id = 3;
SELECT * FROM USER_LOGS;

效果演示：

注意：
MySQL 里，字段 != NULL 永远不成立！永远返回 false！
判断字段是否不为空，必须用：字段 IS NOT NULL
而且 NULL表示没有值、未设置、未知 而 ''表示空字符串，有值，只是值为空
要想同时排除 NULL 和空串可以这样写：字段 IS NOT NULL AND username != ''