MySQL进阶

MySQL进阶

函数的使用

1. 字符串函数

• concat(s1,s2,s3,...)：字符串拼接函数，将s1,s2,s3,...拼接成一个字符串。
• lower(str)：将传递的字符串str全部转成小写。
• upper(str)：将传递的字符串str全部转成大写。
• lpad(str, n, pad)：左填充，其中pad为填充字符串，对str的左边进行填充，达到n个字符串长度。
• rpad(str, n, pad)：右填充，其中pad为填充字符串，对str的右边进行填充，达到n个字符串长度。
• trim(str)：去掉字符串str头部和尾部的空格，注意中间部分的空格不会被去掉。
• substring(str, start, len)：返回字符串str从start位置开始len个长度的字符子串，start从1开始计数。
• replace(str, old_str, replace_str)：将str中的所有old_str替换为repalce_str。
• regexp_replace(str, pattern, replace_str, occurrence)：正则替换，pattern为一个字符串格式的正则表达式，replace_str为替换的字符串，occurrence为整型常量，该值取0时替换掉所有满足正则条件的匹配子串，大于0时表示将第几个满足正则条件的匹配子串替换。

2. 数值函数

• ceil(num)：对num进行向上取整。
• floor(num)：对num进行向下取整。
• mod(num1, num2)：返回num1/num2的模，mod()函数也可以用%替换，例如num % num 。
• rand()：返回0-1范围内的随机数。
• round(x, y)：对x进行四舍五入，保留y位小数。

3. 日期函数

• curdate()：返回当前日期。

• curtime()：返回当前时间。

• now()：返回当前日期和时间。

• year(date)：返回date的年份。

• month(date)：返回date的月份。

• day(date)：返回date的日期。

• weekday(date)：返回date对应星期几，0为星期一，1为星期二，以此类推。

• week(date, mode)：返回date位于一年中的第几周，mode指定计算模式。

  Mode	一个星期的第一天	取值范围	Week 1 的计算逻辑
  0	星期日	0-53	从本年的第一个星期日开始是第1周，前面的日期算作第0周
  1	星期一	0-53	若1月1日至本年第一个星期一的日期跨度超过3天则算作第1周，否则算作第0周
  2	星期日	1-53	从本年的第一个星期日开始是第一周，前面的日期并入去年的周数
  3	星期一	1-53	若1月1日至本年第一个星期一的日期跨度超过3天则算作第一周，否则前面的日期并入去年的周数
  4	星期日	0-53	若1月1日至本年第一个星期日的日期跨度超过3天则算作第1周，否则算作第0周
  5	星期一	0-53	从本年的第一个星期一开始是第一周，前面的日期算作第0周
  6	星期日	1-53	若1月1日至本年第一个星期日的日期跨度超过3天则算作第一周，否则前面的日期并入去年的周数
  7	星期一	1-53	从本年的第一个星期一开始是第一周，前面的日期并入去年的周数

• date_add(date, INTERVAL expr type)：返回一个日期或时间date加上一个时间间隔expr后的时间值，其中type指定了间隔的类型，可选YEAR/MONTH/DAY。

  -- 返回当前时间过70天后的日期时间
  select date_add(now(), INTERVAL 70 DAY);
  

• datediff(date1, date2)：返回date1和date2之间的间隔天数，date1-date2。

• last_day(date)：返回date所在月份的最后一天日期。

• 日期格式转换：

  • date_format(date, format)：用于转换date的日期格式，其中format定义格式化类型。

    其中可选值为：

    • %Y 年（四位数）
    • %y 年（两位数）
    • %m 月（两位数）
    • %d 日（两位数）
    • %H 小时（两位数）
    • %i 分钟（两位数）
    • %s 秒（两位数）
    • %W 星期名称
    • %w 星期中的天数（0=星期日，1=星期一，…）

  • 将字符串格式转为日期格式

    -- 方法一
    select str_to_date(待转换字段, '%Y-%m-%d');
    
    -- 方法二
    select cast(待转换字段 as date);
    
    -- 方法三
    select convert(待转换字段, date);
    

  • 时间戳与日期格式的互相转换

    -- 时间戳转日期格式
    select from_unixtime(1459338786, '%Y-%m-%d %H:%i:%s');
    
    -- 日期格式转时间戳
    select unix_timestamp(now());
    

  • 将"yyyy-mm-dd"转为"yyyymmdd"

    select substr(regexp_replace(待处理的日期字段, '-', ''),1,8);
    

  • 将"yyyymmdd"转为"yyyy-mm-dd"

    -- 方法一
    select from_unixtime(unix_timestamp(待处理日期字段, 'yyyyMMdd'),'yyyy-MM-dd');
    -- 方法二
    select
     	to_date(
    		concat_ws(
    			'-',   -- 最终以-分隔年月日
    			substring(待处理日期字段, 1, 4),  -- 待处理日期字段中的年部 
    			substring(待处理日期字段, 5, 2),  -- 待处理日期字段中的月部
    			substring(待处理日期字段, 7, 2)  --  待处理日期字段中的日部
    		)
    	);
    -- 方法三
    to_date(待处理日期字段, 'yyyyMMdd')
    	
    

4. 流程函数

• if(condition, true, false)：若条件表达式condition为真则返回内容true，否则返回内容false。

• ifnull(value1, value2)：若value1不为空则返回value1，否则返回value2。

• coalesce(expr1, expr2 [,...])：返回第一个表达式不为空的值，若表达式均为空，则返回NULL，可用于区分空值与非空值或进行字段替补。

• case when [condition1] then [result1] ... else [default] end：若condition1为真则返回result1，…，否则返回默认值default。

  -- 统计学生成绩，分数≥85显示优秀，分数[65,85)显示合格，分数<60显示不及格
  select
  	id as '学号',
  	name as '姓名',
  	(case when score >= 85 then '优秀' when score >= 60 then '及格' else '不及格' end) as '成绩'
  from student_score;
  

• case [expr] when [value1] then [result1] ... else [default] end：若expr的值为value1则返回result1，…，否则返回默认值default。

  -- 查询emp表员工的姓名和工作地址
  -- 若工作地址为北京或上海则返回一线城市，其他为二线城市
  select
  	name as '员工姓名',
  	(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
  from emp;
  

---

并发事务

1. 事务简介

事务是一组操作的集合，对于这组操作，要么全部执行成功，要么全部执行失败，对应SQL的ACID原则（原子性、一致性、隔离性、持久性）。MySQL默认自动提交事务，可以通过设置autocommit为0进行手动提交。

-- 查询目前的事务提交机制
select @@[GLOBAL|SESSION] autocommit;

-- 设置为手动提交事务
set [GLOBAL|SESSION] autocommit = 0;

2. 事务操作

-- 开始事务，事务开始后不自动提交
start transaction;

-- 提交事务
commit;

-- 回滚事务
rollback;

3. 并发事务问题

• 脏读：一个事务读到另一个事务还未提交的数据。
• 不可重复读：一个事务先后读取同一条记录，但是两次读取到的数据不同。
• 幻读：一个事务按条件查询数据时没有对应的数据行，但是在插入数据时又提示这行数据已存在无法插入，好像出现了一个数据“幻影”。

4. 事务隔离机制

5. 查看事务隔离级别

select @@TRANSACTION_ISOLATION;

6. 设置事务隔离级别

set [SESSION|GLOBAL] transaction isolation level {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}

---

引擎

1. innoDB

innoDB在MySQL5.5版本后是默认的存储引擎。

• 特点
  • DML操作遵循ACID，支持事务
  • 行级锁
  • 支持外键

2. MyISAM

MyISAM是MySQL早期的默认存储引擎。

• 特点：

  • 不支持事务，不支持外键
  • 仅支持表锁，不支持行锁
  • 访问速度快

• 适用场景：

  若应用是以读操作和插入操作为主，较少涉及更新和删除，并且对事物的完整性、并发性的要求不高，那么可以使用MyISAM作为存储引擎（项目中一般使用MongoDB作为上位替代）。

3. Memory

Memory引擎会将数据存储在内存中，若受到硬件或断电问题影响数据将会丢失，因此只能作为临时存储结构或缓存表。

• 特点：

  • 存放在内存中，访问速度快
  • 默认采用hash索引

• 适用场景：

  若应用需要有临时数据支持，那么可以使用Memory作为存储引擎（项目中一般使用Redis作为上位替代）。

---

索引

1. 索引的优缺点

2. 索引结构

a. B-树

以一棵最大度数为5的B-树（5阶B-树，每个节点最多存储4个key，5个指针）为例：

B-树的构建过程：B-Tree Visualization (usfca.edu)

b. B+树

以一棵最大度数为4的B+树（4阶B+树）为例：

B+数的构建过程：B+ Tree Visualization (usfca.edu)

相比B-树，B+树每一个元素都会出现在叶子结点上，且图中绿框部分仅存储查找索引，红框部分存放索引和数据。此外，每一个叶子结点会以链表的形式相连接。MySQL对经典的B+树进行了优化，在原B+树的基础上，增加一个指向相邻叶子结点的链表指针，形成了带顺序指针的B+树（双向循环链表），提高了区间的访问性能。

思考：为什么数据库中使用B+树作为底层的数据存储结构？

答：对比二叉树，极端二叉树在顺序查找时会出现横向链表的情况，最差需要遍历全表才可以找到所需数据。对比红黑树，红黑树解决了二叉树在极端情况下的效率低下问题，但是本质上也是一类二叉树，数据量一大便会造成层级过多的问题。对比B-树，B-树将数据存储在了每一个结点中，导致每一页可存放的索引指针位置偏少，因此B+树在B-树的基础上仅让叶子结点存储数据，提高了每一页中可存放的数据指针，能有效减少树的层级，提高IO效率。

c. Hash索引

• 特点：

  • 只能用于对等比较（=，in）不支持范围查询（between，>，<，…）
  • 无法利用索引完成排序操作
  • 查询效率高，通常只需要一次检索（无冲突情况下），效率通常要高于B+树索引

• 哈希冲突：

  不同数据随机生成了相同的哈希索引，便会造成哈希冲突，可以使用链表解决冲突。

d. 常见索引结构总结

3. 索引分类

a. 按功能分类

• 主键索引：针对表中主键创建的索引，默认自动创建，只能有一个，关键字primiary。
• 唯一索引：避免表中的某列数据重复，可以有多个，关键字unique。
• 常规索引：用于快速定位特定数据，可以有多个。
• 全文索引：用于查找文本中的关键词，可以有多个，关键字fulltext。

b. 按存储形式分类

（在innoDB中）

• 聚集索引：将数据与索引存储在一起，索引结构的叶子结点保存了行数据，必须有，且只能有一个。

• 二级索引：又名辅助索引，将数据与索引分别存储，索引结构的叶子结点关联的是对应的主键。

• 聚集索引的选取规则：

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一索引作为聚集索引。
  • 如果表没有主键或没有适合的唯一索引，innoDB会自动生成一个row_id作为隐藏的聚集索引。

• 聚集索引和二级索引的效率分析：

  聚集索引的效率明显要优于二级索引，因此在SQL语句中最好选用存在聚集索引的字段作为数据筛选的判别依据。

4. 索引语法

a. 创建索引

create [UNIQUE|FULLTEXT] index index_name on table_name(col_name1, col_name2, ...);

b. 查看索引

show index from table_name;

c. 删除索引

drop index index_name on table_name(col_name1, col_name2, ...);

5. 索引的使用原则

a. 最左前缀

如果索引了多列（联合索引）要遵循最左前缀法则，即查询时筛选条件从索引的最左列开始，并且不跳过索引中的列，若不包含索引的最左列，则索引全部失效，若跳过某一列，则索引将部分失效（具体指跳过之后的字段索引失效）。

注意：只要存在即可，和查询时字段出现的位置无关。

b. 范围查询

如果联合索引中出现范围查询（>,<,…）则范围查询右侧的列索引失效，在业务允许的条件下使用大于等于或小于等于来替换条件中的大于或小于。

c. 索引运算

如果在查询条件时对索引字段进行了运算或函数操作，索引将失效。

注意：字符串类型的索引字段在查询条件时要加引号，因为不加MySQL会隐式转换成字符串类型，相当于也对索引字段进行了操作，因此会导致索引失效。

d. 模糊查询

对索引字段进行尾部模糊匹配，索引不会失效，但对索引字段进行头部模糊匹配，索引会失效（like 'xxx%'不失效，like '%xxx’失效，like '%xxx%'失效 ）。

e. 复合条件判断

• 用or分割的条件，若or前的条件判断列中有索引而or后的条件判断列中没有索引，则所涉及的索引均会失效。
• 用and分割的条件不影响。

f. 前缀索引

当要对类型为varchar/text等字段建立索引时，由于索引需要包含字段的很长一段字符串，会使得索引本身的查询开销陡增，可以只将该字段字符串的一部分前缀建立索引，节约索引空间，提高索引效率。

create index index_name on table_name(col_name(n));

对于前缀的长度n该如何选择能兼顾查询准确度和磁盘空间呢？

-- 获取字段的不重复率（选择率）
select count(distinct col_name) / count(*) from table_name;

-- 尝试通过substring函数截取前n个字符串后使得不重复率降低最小
-- 选择在可容忍的不重复率范围内最小的n
select count(distinct substring(col_name, 1, n)) / count(*) from table_name;

6. 索引的设计原则

• 应该对于数据量大（百万级以上）且查询较为频繁的表建立索引。
• 应该针对于一些常作为查询条件where、排序order by、分组group by操作的字段建立索引。
• 尽量选择区分度较高的列作为索引，尽量建议唯一索引，索引所在字段的区分度越高，使用索引的效率就越高。
• 对于较长的字符串类型字段，应该针对该字段的特点建立提前索引。
• 尽量使用联合索引，减少单列索引，查询时联合索引很多时候可以覆盖索引，避免回表
• 索引不是多多益善，应该控制索引的数量，索引越多，维护索引结构的代价也越大。
• 如果索引列不能存储null值，在创建表结构时应使用not null约束此字段，当优化器知道每列是否包含null值时，可以更好地确定哪个索引能最有效地应用于当前查询。

---

SQL优化

1. SQL性能分析

a. 查看SQL的执行频率

show GLOBAL status like 'Com______';

b. 慢查询日志

慢查询日志记录了所有执行时间超过指定参数long_query_time（单位：秒，默认10秒）的所有SQL语句的日志。

查看慢查询日志的状态：

show variables like 'slow_query_log';

慢查询日志默认不开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢查询开关
slow_query_log = 1

# 设置慢查询日志的时间阈值，超过执行时间阈值的语句会被记录于慢查询日志,默认10秒
long_query_time = 10

开启慢查询后可以访问MySQL安装目录下data文件夹下的slow_query.log文件查询。

c. profile详情

profile操作可以帮助我们了解到执行SQL语句时时间都耗费在什么地方，可以通过参数have_profiling了解当前版本的MySQL是否支持profile操作：

select @@have_profiling;

查看profile操作的状态：

show variables like 'profiling';

profile操作默认不开启，需要通过set语句在会话级别或全局级别开启：

set [SESSION|GLOBAL] profiling = 1

profile操作语法：

-- 查看每一条SQL耗时的基本情况
show profiles;

-- 查看指定query_id的SQL语句各阶段的耗时情况
show profile for query query_id;

-- 查看指定query_id的SQL语句的CPU使用情况
show profile cpu for query query_id;

d. explain执行计划

使用explain或者desc命令可以获取MySQL将如何执行查询语句的信息（包括在语句执行过程中表如何连接和表连接的顺序）。

-- 直接在语句前加上关键字explain/desc
explain select * from table_name; 

执行计划中各字段含义：

• id：查询序列号，表示查询中执行的子句或操作表的顺序（当id相同时，执行顺序从上到下，当id不同时，id值越大表示命令越先执行）。

• select_type：表示查询的类型，常见的取值有SIMPLE（简单表即不用表连接或子查询）、PRIMARY（主查询即外层的查询）、UNION（UNION中的第二个或后面的查询语句）、SUBQUERY（包含子查询）等。

• type：表示连接的类型，性能由好到差分别为NULL、SYSTEM、CONST、EQ_REF、REF、RANGE、INDEX、ALL。

• possible_key：显示可能应用于此表上的索引，一个或多个。

• key：实际查询使用的索引，若为null则没有使用索引。

• key_len：表示索引中所使用的字节数，为索引字段的最大可能长度，而非实际使用长度，在不损失精准性的前提下长度越短越好。

• rows：MySQL认为必须要执行查询的行数，在innoDB引擎中是一个估计值，并不总是准确的执行行数。

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

• extra：表示额外信息。

2. SQL优化方法

a. 插入数据优化

若需要一次性插入大批量数据，可以使用load指令批量添加。

-- 客户端连接服务器端时添加参数--local-infile
mysql --local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set GLOBAL local_infile = 1;

-- 执行load指令批量添加本地数据
load data local infile 'file_url' into table table_name fields terminated by '分隔符', lines terminated by '\n(换行符)';

b. 主键优化

• 尽量降低主键的长度（例如身份证号不适合作为索引）。
• 插入数据时尽量使用顺序插入主键。
• 使用AUTO_INCREMENT自增主键。

c. 排序优化

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

• Using Index：通过有序索引顺序扫描并直接返回有序数据的排序叫Using Index排序，不需要额外排序因此操作效率高。

• 在创建索引时可以对索引字段指定排序顺序：

  create index index_name on table_name(col_name1 asc, col_name2 desc);
  

  注意：对查询结果不同为升序排列或不同为降序排列的字段排序时，可以再创建一个指定排序顺序的索引；针对排序优化的索引也符合最左前缀原则，因此排序的字段顺序最好与建立索引的字段顺序一致。

d. 分组优化

• Using Temporary：通过表的索引或全表扫描读取满足条件的数据行，然后在临时表中完成分组操作，所有不是通过索引直接返回分组结果的都是Using Temporary分组。

• Using Index：通过索引扫描并直接返回数据的分组叫Using Index分组，不需要额外临时空间因此IO开销小。

  注意：针对分组优化的索引也符合最左前缀原则，因此分组的字段顺序最好与建立索引的字段顺序一致。

e. 分页优化

大数据下分页查询的耗时很高，越往后效能越低。

eg：查询sku表第90000000条开始的10条数据，耗时19.48s。

select * from sku limit 90000000, 10;

通过覆盖索引和子查询的方式对分页进行优化。

eg：查询sku表第90000000条开始的10条数据，耗时11.59s。

select tb1.* from sku tb1, (select id from sku order by id limit 90000000, 10) tb2 where tb1.id = tb2.id;

f. count()优化

效率排序：count(字段)<count(主键)<count(1)<count(*)

g. 更新优化

innoDB的行锁是针对索引加的锁，不是针对记录加的锁，在进行并行更新时若更新的判断条件选择了未创建索引的字段时，会升级成表锁，因此执行更新时的判断条件尽量选取索引字段，若业务需要使用未建立索引的字段作为更新的筛选条件，则需要提前建立该字段的索引以避免并行更新的表锁。

---

视图

1. 基本介绍

视图是一种虚拟存在的表，视图中的数据并不在数据库中真实存在，行列数据均来自定义视图的查询中所使用的表（基表），在使用视图时动态生成。

视图仅保留了SQL的查询逻辑，不保存查询结果。

2. 视图的操作语法

a. 创建视图

create [or replace] view 视图名称[(列名列表)] as select语句 [with [CASCADED|LOCAL] check option];

• 视图创建完毕后，可以对视图进行数据的插入，但由于视图并不是一张真实的表，因此插入的数据会直接写入基表。

• 视图创建完毕后，可以对视图进行数据的更新，更新的前提为视图中的行与基表中的行必须一一对应，且当视图中包含聚合函数或窗口函数、DISTINCT关键字、分组操作、UNION关键字等任意一项时，该视图不可更新。

• 命令with check option的存在使得对视图进行数据插入、更新、删除时会验证所操作的数据是否满足视图的定义范围。默认选项为CASCADED。

• 当使用CASCADED时，依赖其他视图创建的视图也会同时进行级联检查；当使用LOCAL时仅检查当前视图和其所依赖的第一个父层视图，若其依赖的第一个父层视图没有检查选项则不再检查。

b. 查询视图

-- 查看创建视图的语句
show create view 视图名称;
     
-- 查看视图数据(作为一张表进行查询)
select * from 视图名称 ...;

c. 修改视图

-- 方式一
create or replace view 视图名称[(列名列表)] as select语句 [with [CASCADED|LOCAL] check option];
     
-- 方式二
alter view 视图名称[(列名列表)] as select语句 [with [CASCADED|LOCAL] check option];

d. 删除视图

drop view [if exists] 视图名称 [,视图名称, ...];

3. 视图的作用

• 简单化：视图可以简化用户对数据的理解，定义一些经常使用的查询视图，可以让用户不必为之后的操作每一次都指定全部的条件。
• 安全：数据库可以授权，但只能授权到表，不能授权数据库表中特定的行和特定的列，通过视图用户只能查询和修改他们所能见到的数据。
• 数据独立：视图可以帮助用户屏蔽用户基表的变化，避免因基表结构变化所带来的业务影响。

---

存储过程

1. 存储过程的操作语法

a. 存储过程的创建

create procedure 存储过程名称([IN/OUT/INOUT 参数名 参数类型, ...])
begin
	sql语句;
end;

注意：在命令行中需指定delimiter，将其更改为另一特殊字符象征结束，否则读不到存储过程的创建结束就止于分号了。

b. 存储过程的调用

call 存储过程名称([参数列表]);

c. 存储过程的查看

-- 查询指定数据库的存储过程及状态信息
select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = '数据库名称';
     
-- 查询某个存储过程的定义语句
show create procedure 存储过程名称;

d. 存储过程的删除

drop procedure [if exists] 存储过程名称;

2. 变量

• 系统变量
  由MySQL服务器提供，属于服务层面，分为全局变量GLOBAL与会话变量SESSION，若不指定则默认为会话变量。

  -- 查看所有的系统变量
  show [GLOBAL|SESSION] variables;
  
  -- 模糊查询系统变量
  show [GLOBAL|SESSION] variables like 'xxxx';
  
  -- 查看指定系统变量
  select @@[GLOBAL|SESSION] 系统变量名称;
  
  -- 设置系统变量
  set [GLOBAL|SESSION] 系统变量名称 = 值;
  set @@[GLOBAL|SESSION] 系统变量名称 = 值;
  
  

• 用户自定义变量
  用户根据需要自定义的变量，用户自定义变量不需要提前声明，在使用时使用@变量名即可，其作用域为当前会话。获取不到的自定义变量会被认为是null。

  -- 自定义变量并赋值（'='可以用':='替换）
  set @自定义变量名称 = expr [,@自定义变量名称 = expr, ...];
  select @自定义变量名称 = expr [,@自定义变量名称 = expr, ...];
  select 字段名 into @自定义变量名称 from 表名;
  
  -- 使用自定义变量
  select @自定义变量名称;
  

• 局部变量
  用户根据需要局部声明的变量，访问前需要使用关键字declare声明，可作为存储过程内部的局部变量和输入参数，定义的局部变量仅在局部生效（例如存储过程中的begin...end块中）。

  -- 声明局部变量
  declare 局部变量名 变量类型 [default ...];
  
  -- 对局部变量赋值
  set 局部变量名 = 值;
  set 局部变量名 := 值;
  select 字段名 into 局部变量名 from 表名;
  

  eg：定义局部变量用于存储过程中统计学生的人数。

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

3. 参数

4.条件分支

• if条件分支

  -- 相当于单条语句的流程函数
  if 条件1 then
  	...
  elseif 条件2 then
  	...
  else
  	...
  end if;
  

  eg：根据传入的分数判定对应的分数等级并返回，当分数≥85分时为优秀，分数[60,85)为及格，分数<60为不及格。

  create procedure score_to_grade(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 score_to_grade(68, @result);
  select @result;
  

• case条件分支

  -- 相当于单条语句的流程函数
  case case_value
  	when when_value then statement1;
  	when when_value then statement2;
  	...
  	else default_statement;
  end case;
  
  case
  	when condition1 then statement1;
  	when condition2 then statement2;
  	...
  	else default_statement;
  end case;	
  

  eg：根据传入的月份判定月份所属的季度。

  create procedure month_to_quarter(in month int)
  begin
  	declare result varchar(10);
  	case
  		when month >= 1 and month <= 3 then
  			set result := 'Q1';
  		when month >= 4 and month <= 6 then
  			set result := 'Q2';
  		when month >= 7 and month <= 9 then
  			set result := 'Q3';
  		when month >= 10 and month <= 12 then
  			set result := 'Q4';
  		else
  			set result := '非法参数';
  	end case;
  	select concat('您输入的月份为：', month, '，所属的季度为：', result);
  end;
  
  call month_to_quarter(4);
  

5. 循环控制

• while循环

  while 条件 do
  	statement;
  end while;
  

  eg：从1累加至n的值，n为传入的参数。

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

• repeat循环

  repeat
  	statement;
  	unit condition
  end repeat;
  

  eg：从1累加至n的值，n为传入的参数。

  create procedure accumulate(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 accumulate(100);
  

• loop循环

  [begin_label:] loop
  	statement;
  end loop [end_label];
  

  eg：从1累加至n的值，n为传入的参数。

  create procedure accumulate(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 accumulate(100);
  

  eg：从1至n之间的偶数累加值，n为传入的参数。

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

6. 条件处理程序

条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

• 条件处理程序的声明：

  declare handler_action handler for condition_value [, condition_value, ...] statement;
  

• handler_action：

  • continue：继续执行当前程序
  • exit：终止执行当前程序

• condition_value：

  • SQLSTATE sqlstate_value：状态码，如02000。
  • SQLWARNNING：所有以01开头的SQLSTATE代码的简写。
  • NOT FOUND：所有以02开头的SQLSTATE代码的简写。
  • SQLEXCEPTION：所有没有被SQLWARNNING以及NOT FOUND捕获的SQLSTATE代码的简写。

7. 游标

   游标的存在使得存储过程和函数可以对查询结果集进行条件分支或循环控制的处理。

   游标的声明：

   declare 游标名称 cursor for 查询语句;
   

   打开游标：

   open 游标名称;
   

   获取游标记录：

   fetch 游标名称 into 变量名称 [, 变量名称, ...];
   

   关闭游标：

   close 游标名称;
   

   游标的使用：

   eg：根据传入参数uage，查询用户表tb_user中所有用户年龄小于等于uage的用户姓名name和专业profession，并将用户的姓名和专业插入到一张新表中。

   create procedure sel_ins_proc(in uage int)
   begin
   	-- 声明变量
   	declare uname varchar(10);
   	declare uprofession varchar(20);
   	-- 声明游标
   	declare u_cursor cursor for (select name, profession from tb_user where age < uage;);
   	-- 声明条件处理程序(SQLSTATE '02000'可以替换为NOT FOUND)
   	declare exit handler for SQLSTATE '02000' close u_cursor;
   	-- 创建新表
   	drop table if exists tb_user_prof;
   	create table if not exists tb_user_prof(
           id int primary key auto_increment,
           name varchar(10),
           profession varchar(20)
       );
       -- 插入值
       open u_cursor;
       while true do
       	fetch u_cursor into uname, uprofession;
       	insert into tb_user_prof values (null, uname, upro);
       end while;
       close u_cursor;
   end
   

---

存储函数

1. 存储函数的概念

存储函数是有返回值的存储过程，且存储函数的参数只能是IN类型的。存储函数一般用的比较少，因为存储函数能实现的功能存储过程也可以实现。

2. 存储函数的语法

create function 存储函数名称([参数列表])
returns type [characteristic ...]
begin
	statement;
	return ...;
end;

characteristic说明：

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

3. 存储函数的使用

eg：从1累加至n的值，n为传入的参数。

create function accumulate(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 accumulate(100);

注意：MySQL8.x版本默认开启二进制日志，此时characteristic必须定义。

---

触发器

1. 触发器的概念

触发器可以在插入、更新、删除之前或之后执行指定的SQL语句，一般应用于数据的校验、日志记录等。MySQL目前仅支持行级触发器。

2. 触发器的语法

• 创建触发器

  create trigger 触发器名称
  before/after insert/update/delete
  on 表名 for each row
  begin
  	statement;
  end;
  

• 查看触发器

  show triggers;
  

• 删除触发器

  drop trigger [数据库名称] 触发器名称;
  

---

锁

1. 锁的概述

a. 锁的简单介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统计算资源（CPU/RAM/IO）外，数据也是一种供许多用户共享的资源，需要保证数据并发访问的一致性和有效性，降低锁冲突对数据库并发访问性能的影响。

b. 锁的分类

（在MySQL中，按粒度分）

• 全局锁：锁定数据库中的所有表。
• 表级锁：每次操作锁住整张表。
• 行级锁：每次操作锁住对应的行数据。

2. 全局锁

a. 适用场景

全局锁是对整个数据库实例枷锁，加锁后整个实例处于只读状态，所有有关数据库实例的修改语句都将被阻塞，适用于做全库的逻辑备份。

b. 全局锁操作语法

-- 加锁语法
flush tables with read lock;

-- 解锁语法
unlock tables;

c. 全局锁应用（全库备份）

-- 切换数据库
use 数据库名;
-- 对当前数据库添加全局锁
flush tables with read lock;
-- 备份该数据库至.sql文件（命令行操作语句）
mysqldump -u root -p 'password' 数据库名 > 路径\xxx.sql
-- 解锁
unlock tables;

-- innoDB引擎下可以实现不加锁的一致性数据备份（通过快照机制实现）
mysqldump --single-transaction -u root -p 'password' 数据库名 > 路径\xxx.sql

3.表级锁

a. 表级锁的分类

• 表锁：

  • 表共享读锁（Read Lock）

  

  • 表独占写锁（Write Lock）

  

• 元数据锁（MDL）：维护表结构的数据一致性，避免DML与DDL的冲突。

  

• 意向锁：避免DML执行时加的行锁与表锁的冲突，使得表锁不用检查每一行数据是否加了行锁，减少表锁的检查。

  • 意向共享锁（IS）：由select ... lock in share mode添加。
  • 意向排他锁（IX）：由insert、update、delete、select ... for update添加。
  • 兼容性：
    • IS与表共享读锁兼容，与表独占写锁互斥。
    • IX与表共享读锁和表独占写锁均互斥，但意向锁之间不会互斥。

b. 表级锁操作语法

-- 加锁语法
lock tables 表名[, 表名, ...] read/write

-- 解锁语法
unlock tables;

4. 行级锁

a. 行级锁的分类

• 行锁（Record Lock）：锁定单行记录，防止其他事务对此进行更新和删除操作。
• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录的间隙不变，防止其他事务在这个间隙进行插入操作。
• 临键锁（Next-Key Lock）：同时锁住单行记录和记录前的间隙。

b. 行锁详解

• 共享锁（S）：允许一个事务读一行，阻止其他事务获取相同数据集的排他锁。

• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

• 兼容性：

  

• innoDB中操作与行锁之间的对应关系：

  

• 行锁的执行逻辑：

  • 默认情况下innoDB在REPEATABLE READ事务隔离级别下运行，使用临键锁进行搜索和索引扫描，以防止幻读。
  • 针对唯一索引进行检索时，对已存在的记录进行等值匹配操作会自动优化为行锁。
  • 若不通过索引条件检查数据，则会对表中的所有记录加锁，从行锁升级为表锁。