MySQL 学习记录 2

原文：https://blog.iyatt.com/?p=13818

13 存储引擎

查看一下前面创建的一张表的创建语句，当时并没有显式指定引擎，MySQL 自动指定的 InnoDB，即默认引擎是这个。
创建表的时候要显式指定引擎可以参考这个语句
file

查看当前 MySQL 版本支持的引擎有那些

SHOW ENGINES ;

file

 $\begin{array}{|l|l|l|l|} \hline 特点 & InnoDB & MyISAM & Memory \\ \hline 事务安全 & 支持 & - & - \\ 锁机制 & 行锁 & 表锁 &表锁 \\ B+tree索引 & 支持 & 支持 & 支持 \\ Hash索引 & - & - & 支持 \\ 全文索引 & 支持（5.6版本之后）& 支持 & - \\ 空间使用 & 高 & 低 & N/A \\ 内存使用 & 高 & 低 & 中等 \\ 批量插入速度 & 低 & 高 & 高 \\ 支持外键 & 支持 & - & - \\ \hline \end{array}$

InnoDB：MySQL 的默认存储引擎，支持事务、外键。如果对事务的完整性有比较高的要求，在并发条件下要求数据的一致性。另外对数据的操作除了插入和查询之外，还包含很多的更新、删除操作，那么选择这个引擎比较合适。
MyISAM：如果是以读取和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么可以选择这个引擎。
MEMORY：所有数据都保存在内存中，访问速度快，通常用于临时表及缓存。不过存在一定的缺陷，对表的大小有限制，太大的表无法存储在内存中，并且无法保障数据的安全性（意外断电、宕机等可能造成数据丢失）

14 索引 - 查询优化

 $\begin{array}{|l|l|} \hline 索引结构 & 描述 \\ \hline B+Tree & 最常见的索引类型，大部分存储引擎都支持。 \\ Hash索引 & 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。\\ R-tree & 这是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，使用较少。 \\ Full-text & 通过建立倒排索引，快速匹配文档的方式。\\ \hline \end{array}$

 $\begin{array}{|l|l|l|l|} \hline 索引 & InnoDB & MyISAM & Memory \\ \hline B+tree & 支持 & 支持 & 支持 \\ Hash & 不支持 & 不支持 & 支持 \\ R-tree & 不支持 & 支持 & 不支持 \\ Full-text & 支持（5.6版本后） & 支持 & 不支持 \\ \hline \end{array}$

数据结构可视化：https://iyatt.com/tools/DataStructureVisualizations/Algorithms.html

B+tree 依次插入100、65、169、368、900、556、780、35、215、1200、234、888、158、90、1000、88、120、268、250
file

 $\begin{array}{|l|l|l|l|} \hline 分类 & 含义 & 特点 & 关键字 \\ \hline 主键索引 & 针对表中主键创建的索引 & 默认自动自动创建，只有一个 & PRIMARY \\ 唯一索引 & 避免同一个表中某列数据重复 & 可以有多个 & UNIQUE \\ 常规索引 & 快速定位特定数据 & 可以有多个 & \\ 全文索引 & 全文索引查找的是文本中的关键词，而不是比较索引中的值 & 可以有多个 & FULLTEXT \\ \hline \end{array}$

在 InnoDB 中

 $\begin{array}{|l|l|l|} \hline 分类 & 含义 & 特点 \\ \hline 聚集索引（Clustered Index） & 将数据存储与索引放在了一块，索引结构的叶子节点保存了行数据 & 必须有，且只有一个 \\ 二级索引（Secondary Index） & 将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键 & 可以存在多个 \\ \hline \end{array}$

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
如果前两者都没有合适的，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

创建索引
（如果要创建常规索引，则不指定 UNIQUE 或 FULLTEXT）

CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (索引列名);

查看索引

SHOW INDEX FROM 表名;

删除索引

DROP INDEX 索引名 ON 表名;

14.1 语法

创建一张表，创建语句使用：https://blog.iyatt.com/?p=12631#101_%E4%B8%80%E8%88%AC%E7%BA%A6%E6%9D%9F%E7%A4%BA%E4%BE%8B
查看这张表的索引
（加上 \G 会按行显示）

SHOW INDEX FROM new_user\G;

可以看到列名 id 的 Key_name 是 PRIMARY 主键索引（创建表指定了主键约束），列名 name 的 Key_name 就是列名，该列在创建表时指定了唯一约束，其它列则没有索引。
file

现在手动为 age 列创建一个常规索引名为 index_age

CREATE INDEX index_age ON new_user(age);

再次查看可以看到 age 列的索引
file

删除创建的常规索引

DROP INDEX index_age ON new_user;

再次为 age 和 status 同时创建一个唯一索引（联合索引）

CREATE UNIQUE INDEX index_age ON new_user(age, status);

查看索引
file

14.2 性能分析

14.2.1 查询 SQL 执行频次

查询状态信息

SHOW [SESSIOn | GLOBAL] STATUS;

筛选出 SQL 语句执行次数，全局查询含有 Com 的且后续还有 7 个字母的变量，用 7 个下划线 _ 匹配

SHOW GLOBAL STATUS LIKE 'Com_______';

这里我在当前博客的服务器数据库上查询（Mariadb 和 MySQL 基本上兼容）
可以看到插入了 948768 次，删除了 339467 次，查询了 34850658 次，修改了 667195 次。可以看到里面查询次数是最多的，毕竟博客大多数时候都是浏览查看，所以优化的重点就在查询上。
file

14.2.2 慢查询日志

当 MySQL 中某个语句执行超过设定时间，就会记录到日志中，默认是没有打开的。

查看是否开启慢查询日志
当前是关闭的

SELECT @@slow_query_log;

file

查看慢查询时间
默认是 10s，查询时间超过它就会记录日志

SELECT @@long_query_time;

file

如果要开启慢查询日志可以配置：

Windows：前往路径 C:\ProgramData\MySQL\MySQL Server 版本，编辑 my.ini（打开显示隐藏文件，不然看不到这个路径）

这个文件默认是没有编辑权限的，可以在这个文件上右键打开属性

给自己的当前用户添加修改权限
file

这样就可以编辑这个文件了，Windows 默认是打开状态的
slow-query-log 设置 1 就是开启，设置 0 就是关闭。
slow_query_log_file 设置文件名，日志文件位于 C:\ProgramData\MySQL\MySQL Server 版本\Data 下。
long_query_time 设置超时时间。
file

修改完保存，并重启 MySQL 服务器
file

Linux：以 root 权限编辑 /etc/my.cnf，我博客服务器用的 Mariadb 10.3.38 中这个文件在 /etc/mysql/my.cnf。配置参数方法同上。

如果要临时设置可以使用下面命令（重启恢复为配置文件中的默认状态），后续其它变量一样

# SESSION 只在当前会话中，GLOBAL 在所有客户端都生效
SET [SESSION | GLOBAL] 变量名 = 变量值；

在 Windows 中默认试打开的，在 Linux 中默认是关闭的。估计因为一般开发是在 Windows 上，这个打开本来就是用于调试，而实际生产环境部署一般是在 Linux 上，所以默认是关闭的，在生产环境上开启这些记录只会增加资源消耗，浪费本该用于业务执行的性能。

14.2.3 profile

查看语句执行耗时

查看是否支持 profile

SELECT @@have_profiling;

file

查看打开状态

SELECT @@profiling;

使用 SET 把这个变量改为 1 即可开启，在执行语句后会记录执行时间，通过命令可以查询

SHOW PROFILES ;

file

查看指定 query_id 的语句的详细耗时

SHOW PROFILE FOR QUERY 查询ID;

file

查看指定 query_id 的语句的 CPU 使用情况

SHOW PROFILE CPU FOR QUERY 查询ID;

file

14.2.4 explain 执行计划

在执行的语句前面加上 EXPLAIN 或 DESC，查询项含义：

id：表查询的序列号，相同则从上往下，越大的越先执行
select_type：查询类型，常见的有 SIMPLE（简单表，不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（包含子查询）等
type：连接类型，性能从低到高：NULL、system、const、eq_ref、ref、range、index、all
possible_key：表中可能用到的索引。
key：实际使用的索引
key_len：索引字段的最大可能长度
rows：必须要执行查询的行数，在 InnoDB 中是一个估计值
filltered：结果返回的行数占需要读取行数的百分比，越大越好。

采用这里的三张表来演示：https://blog.iyatt.com/?p=12631#1113_%E5%A4%9A%E5%AF%B9%E5%A4%9A

内连接

EXPLAIN SELECT * FROM stu s, course c, stu_course sc WHERE s.id = sc.stu_id AND c.id = sc.course_id \G;

file

子查询

EXPLAIN SELECT * FROM stu s WHERE s.id IN (
    SELECT stu_id FROM stu_course sc WHERE sc.course_id
                                     = (SELECT id FROM course c WHERE c.name = '高数')
    ) \G;

file

14.3 使用

14.3.1 索引有效条件 - 最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则。

创建一张表

CREATE TABLE info (
                      id tinyint,
                      age tinyint,
                      name char(2),
                      class int
);

插入数据

INSERT INTO info VALUES
                     (1, 21, '小强', 1),
                     (2, 21, '小红', 2),
                     (3, 20, '小张', 1);

file

创建联合索引（后续提到的左右都是基于创建索引时指定的列顺序）

CREATE INDEX index_info ON info(age, name, class);

获取每个列的 key_len
age name class 分别为 2 9 5
file

同时指定三列查询

EXPLAIN SELECT * FROM info WHERE age = 21 and name = '小强' and class = 1;

使用了全部索引
file

指定最左列和最右列查询

EXPLAIN SELECT * FROM info WHERE age = 21 and class = 1;

key_len 为 2，只有最左列 age 使用索引
file

不指定最左侧列

EXPLAIN SELECT * FROM info WHERE name = '小强' and class = 1;

没有使用索引
file

age 列指定范围

EXPLAIN SELECT * FROM info WHERE age > 20 and name = '小强' and class = 1;

key_len 为 2，从 age 列右侧下一列开始没有使用索引
file

打乱顺序

EXPLAIN SELECT * FROM info WHERE class = 1 and name = '小强' and age = 21;

顺序不影响索引使用
file

即联合索引必须包含最左列才会使用索引，且中间如果有留空，则从留空列开始不使用索引

14.3.2 索引失效情况 1 - 索引列运算

用上面的表演示，在这个表中查询年龄为 21，名字第二个字为“强”的

EXPLAIN SELECT * FROM info WHERE age = 21 and substring(name, 2, 1) = '强';

key_len 为 2，即 name 字段索引失效了
file

14.3.3 索引失效情况 2 - 范围索引

上面最左前缀法则中，age 指定范围演示过。从范围索引的右侧列开始索引失效。

14.3.4 索引失效情况 3 - 模糊查询

第一个字采用模糊匹配

EXPLAIN SELECT * FROM info WHERE age = 21 AND name LIKE '_强';

key_len 为 2，name 索引失效
file

非第一个字模糊匹配

EXPLAIN SELECT * FROM info WHERE age = 21 AND name LIKE '小_';

索引正常工作
file

第一个字符模糊查询会导致索引失效，非第一个字符模糊查询索引正常工作。

14.3.5 索引失效情况 4 - OR 连接的条件

OR 连接的条件，一个有索引，一个没有索引，那么此时两者都不会使用索引。

这里的 age 有索引，id 没有索引

EXPLAIN SELECT * FROM info WHERE age = 21 OR id = 1;

结果都没有使用索引
file

14.3.6 索引失效情况 5 - 数据分布影响（优化）

当使用索引可能更慢的时候，MySQL 会决定不使用索引。

创建一张表用于演示

CREATE TABLE info1 (
    id int,
    name char(2)
);

INSERT INTO info1 (id, name) VALUES
                                (1, '张三'),
                                (2, '李四'),
                                (3, '王五'),
                                (4, '赵六'),
                                (5, '孙七'),
                                (6, '周八'),
                                (7, '吴九'),
                                (8, '郑十'),
                                (9, '陈一'),
                                (10, '林二'),
                                (11, '罗三'),
                                (12, '何四'),
                                (13, '高五'),
                                (14, '马六'),
                                (15, '刘七'),
                                (16, '梁八'),
                                (17, '黄九'),
                                (18, '曾十'),
                                (19, '彭一'),
                                (20, '胡二'),
                                (21, '许三'),
                                (22, '沈四'),
                                (23, '韩五'),
                                (24, '杨六'),
                                (25, '朱七'),
                                (26, '秦八'),
                                (27, '尤九'),
                                (28, '许十'),
                                (29, '薛一'),
                                (30, '侯二'),
                                (31, '夏三'),
                                (32, '邱四'),
                                (33, '方五'),
                                (34, '石六'),
                                (35, '姚七'),
                                (36, '谭八'),
                                (37, '廖九'),
                                (38, '范十'),
                                (39, '汪一'),
                                (40, '陆二'),
                                (41, '金三'),
                                (42, '魏四'),
                                (43, '陶五'),
                                (44, '戴六'),
                                (45, '郭七'),
                                (46, '洪八'),
                                (47, '邹九'),
                                (48, '江十'),
                                (49, '章一'),
                                (50, '董二');

CREATE INDEX index_info1 ON info1(id);

查询 id > 10 的数据

EXPLAIN SELECT * FROM info1 WHERE id > 10;

没有使用索引
file

查询 id > 30 的数据

EXPLAIN SELECT * FROM info1 WHERE id > 30;

使用了索引
file

当查询的数据是表中的少部分的时候，MySQL 会使用索引，这样速度更快，但是查询的是表中的大部分数据的时候，可能不如直接暴力遍历的速度。

14.3.7 索引提示

当一个列存在多个索引时，可以指定使用某个索引。

这里示例还是使用上面创建的 info 表
为 age 列再创建一个单列索引

CREATE INDEX index_age ON info(age);

此时 age 同时具有前面的联合索引，又有了一个单列索引

EXPLAIN SELECT * FROM info WHERE age = 21;

可能用到的索引有两个，实际用的是联合索引
file

指定使用 index_age 索引（单列）- 建议 MySQL 使用，MySQL 通过一定算法判断是否使用
USE INDEX (索引名)

EXPLAIN SELECT * FROM info USE INDEX (index_age) WHERE age = 21;

file

忽略索引 index_info（联合）
IGNORE INDEX (索引名)

EXPLAIN SELECT * FROM info IGNORE INDEX (index_info) WHERE age = 21;

file

强制使用索引 index_age（单列）
FORCE INDEX (索引名)

EXPLAIN SELECT * FROM info FORCE INDEX (index_age) WHERE age = 21;

file

14.3.8 覆盖索引

查询的列数据都包含在索引中

使用上面的 info 表，删掉创建的索引，只保留原先的联合索引

当查询项都在索引中时

EXPLAIN SELECT age, name, class FROM info WHERE age = 21 AND name = '小强' AND class = 1;

file

当查询项多了一个 id（不在索引中）

EXPLAIN SELECT id, age, name, class FROM info WHERE age = 21 AND name = '小强' AND class = 1;

或者查 *

EXPLAIN SELECT * FROM info WHERE age = 21 AND name = '小强' AND class = 1;

file

Using index condition：表示使用了索引条件下推（Index Condition Pushdown，ICP）的优化，即在存储引擎层对索引进行过滤，减少回表查询的次数。
Using where：表示在服务器层对数据进行过滤，通常是因为索引不能完全满足查询条件，或者没有使用索引。
Using index：表示使用了覆盖索引（Covering Index）的优化，即索引已经包含了所需的所有列，无需访问数据表

14.3.9 前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，但是这样会让索引变得很大，查询的时候磁盘 IO 占用会非常高，影响查询效率。因此可以只将字符串的一部分前缀建立索引，这样可以提高索引效率。

CREATE INDEX 索引名 ON 表名(列名(前n个字符))

至于这前n个字符具体取多少个，可以参考“索引的选择性”。比如某列的字段内容较长，考虑建立前缀索引，所在的表中有100行数据，如果每行的该字段都只取前 10 个字符，结果这 100 行都没有重复，那么选择性 = 没有重复的行数 100 / 总行数 100 = 1。然后又尝试往前推进看看，该字段取前 9 个字符，结果有 10 行和已经存在的重复，那么选择性= 去除重复的行数 (100-10) / 总行数 100 = 0.9。前一种前缀取 10，每行都是独一无二的，这种情况下索引速度必然好，后一种情况前缀取 9，但是只有 0.9 的比例为非重复，会影响一定效率（遇到重复等于索引失效，要回表查询），但是索引能节省一个字符的空间。索引的选择性就是一种作为参考的参数，来辅助选择前缀大小，综合前缀长度减小又能保证较好的选择性数值。

下面是一个示例表，创建它

CREATE TABLE info2 (
    name char(2),
    email varchar(32)
);

INSERT INTO info2 VALUES
                      ('小强', '123456789@qq.com'),
                      ('小王', '123459875@foxmail.com'),
                      ('小李', 'xiaoli@baidu.com'),
                      ('小红', '1234567@douyin.com'),
                      ('小张', '123986@360.com'),
                      ('小谢', 'xioaoxie@a.com'),
                      ('小陈', 'xiaochen@b.cn'),
                      ('小杨', 'xiaoyang@c.cpn'),
                      ('小赵', 'xiaozhao@d.com'),
                      ('小唐', '1234587@t.com');

file

SELECT count(DISTINCT substring(email, 1, 前缀长度)) / count(*) FROM info2;

file

前缀取 8 的时候选择性为 1，前缀取到 7、6 都是 0.9，取到 5 就只有 0.7 了，那我就取 6

CREATE INDEX prefix_index_email ON info2(email(6));

查看索引时，其中 Sub_part 字段为 6，非前缀索引这个值就是 NULL
file

第一个 rows 为 1，第二个为 2。因为第一个在前缀索引取 6 时没有重复的索引，而后一个索引有重复的，那么就需要回到数据表中去确认是否重复，也就还需要额外查一次。
file

file

15 其它优化

15.1 插入优化

多条数据，INSERT 最好一次性插入多条数据，而不是每次一条。
当数据量过于太大时，也不要一次性插入，可以一次性插入 500-1000 条
连续执行 INSERT 时最好显式开启事务，执行完所有插入后一次性提交

15.1.1 从文件导入数据

这里写了一个 Python 脚本用来生成 csv 数据

import random

def write_to_file(filename, delimiter, newline, rows):
    with open(filename, 'w', encoding='utf-8') as f:
        for i in range(rows):
            id = i + 1
            line = str(id) + delimiter + ''.join(random.sample('abcdefghijklmnopqrstuvwxyz', 12)) + delimiter + str(random.randint(0,1)) + delimiter + str(random.randint(0, 100))
            f.write(line + newline)

# 生成文件名 test.csv
# 分隔符为英文逗号
# 换行符为 \r
# 数据行数 1000000
write_to_file('test.csv', ',', '\r', 1000000)

这里连接数据库的命令要加上额外参数

mysql --local-infile -u root -p

启用文件导入

SET GLOBAL local_infile = 1;

然后创建一张和数据列匹配的表，比如匹配这里的例子的表

CREATE TABLE person (
    id int PRIMARY KEY COMMENT '唯一标识',
    name char(12) COMMENT '名字',
    gender tinyint COMMENT '性别',
    age tinyint COMMENT '年龄'
);

导入文件

LOAD DATA LOCAL INFILE 文件路径 INTO TABLE 表名 FIELDS TERMINATED BY 分隔符 LINES TERMINATED BY 换行符;

导入一百万行数据用了 22.96s
file

file

15.2 主键优化

插入的数据会按照主键顺序储存，最好是按照主键顺序插入，这样每次插入都是接着前一个的后面顺序插入，乱序插入时需要移动数据来保持主键顺序影响效率（页分裂）。创建表的时候最好使用自增主键，业务操作的时候尽量不要修改主键。
尽量降低主键的长度

15.3 排序优化

使用前面的 100 万行数据的例子，按照年龄排序，并输出前 1000 行

SELECT * FROM person ORDER BY age LIMIT 1000;

用时 0.69s
file

再为年龄创建一个索引（因为数据量大，创建过程可能比较耗时）
file

再次按年龄排序查询，耗时可以忽略不记
file

15.4 分组优化

上面创建了 age 的索引，现在根据 age 分类统计每个年龄的人数

SELECT age, COUNT(*) FROM person GROUP BY age;

file

然后删掉 age 的索引，再次执行，耗时差不多翻倍
file

file

16 视图

现有数据库中的表查询结果创建出一张新的表（虚拟表），这张新的表就是视图，视图可以看作是一个快捷方式，创建的时候给指定语句整体起个别名，后续使用别名查询就是在创建语句的基础上操作，创建视图并不额外存储表，都是重新执行语句去查询。

16.1 基本使用

创建视图

CREATE VIEW 视图名 AS SELECT语句;

file

查询创建语句

SHOW CREATE VIEW 视图名;

查询视图

SELECT * FROM 视图名;

file

修改视图方式一

CREATE OR REPLACE VIEW 视图名 AS SELECT语句;

修改视图方式二

ALTER VIEW 视图名 AS SELECT语句;

删除视图

DROP VIEW 视图名;

16.2 检查选项

创建视图的时候在后面加上检查选项，当查询语句存在一定的 WHERE 条件，而尝试对视图插入的数据不满足 WHERE 条件时会阻止插入。

CREATE VIEW 视图名 AS SELECT语句 WITH [ CASCADED | LOCAL ] CHECK OPTION ;

不指定 CASCADED 或 LOCAL，默认就是 CASCADED。基于表创建视图的时候，两个没有区别，都会对当前视图的创建语句的 WHERE 条件检查。区别在于基于视图创建视图的时候，LOCAL 只检查当前视图的创建语句，而对于它的所有上级视图的条件不管，而 CASCADED 则是对所有的上级视图起作用。

创建一张表进行测试

CREATE TABLE test (
    id int PRIMARY KEY AUTO_INCREMENT,
    num int
);

LOCAL 测试，创建四个视图

CREATE OR REPLACE VIEW test_view1 AS SELECT * FROM test;
CREATE OR REPLACE VIEW test_view2 AS SELECT * FROM test_view1 WHERE num > 0 WITH LOCAL CHECK OPTION ;
CREATE OR REPLACE VIEW test_view3 AS SELECT * FROM test_view2 WHERE num > 5;
CREATE OR REPLACE VIEW test_view4 AS SELECT * FROM test_view3 WHERE num < 10 WITH LOCAL CHECK OPTION ;

插入 -1 不满足 test_view2 的条件
file

插入 2 成功，满足 test_view2 和 test_view4 的条件，虽然不满足 test_view3 的条件，但是 test_view3 没有检查选项不受影响
file

CASCADED 测试，重新创建视图

CREATE OR REPLACE VIEW test_view1 AS SELECT * FROM test;
CREATE OR REPLACE VIEW test_view2 AS SELECT * FROM test_view1 WHERE num > 0;
CREATE OR REPLACE VIEW test_view3 AS SELECT * FROM test_view2 WHERE num > 5;
CREATE OR REPLACE VIEW test_view4 AS SELECT * FROM test_view3 WHERE num < 10 WITH CASCADED CHECK OPTION ;

再次尝试插入 -1 和 2 都失败了，此时 test_view4 的检查条件为 CASCADED，虽然往上都没有检查选项，但是 CASCADED 往上递归，全都会检查，插入 7 的时候成功了，它满足所有视图的条件。
file

17 存储过程

“储存过程”的作用有点像一般编程语言里的函数，把一堆功能语句封装起来调用。

17.1 语句分隔符修改

MySQL 默认的语句分隔符是英文分号 ;
在 MySQL 客户端中创建存储过程时，还是用分号作为分隔符的话会导致无法正常识别语句，所以需要修改（只在当前会话生效）

DELIMITER 新的分隔符

17.2 基本使用

创建存储过程

DELIMITER $$ # 修改分隔符
CREATE PROCEDURE 存储过程名字()
BEGIN
    执行的操作
END $$
DELIMITER ; # 切换回分号

file

调用自定义的存储过程

CALL 存储过程名字();

file

查看存储过程定义

SHOW CREATE PROCEDURE 存储过程名字;

file

删除存储过程

DROP PROCEDURE 存储过程名字;

file

17.3 变量

17.3.1 系统变量

由 MySQL 定义的具有特殊含义的变量，有全局 GLOBAL 和 SESSION 两类，前者针对整个数据库系统生效，后者只对当前客户端连接生效，不指定默认为 SESSION。

查看系统变量

SHOW [SESSION | GLOBAL] VARIABLES; # 查看全部

SHOW [SESSION | GLOBAL] VARIABLES LIKE '关键词'; # 模糊匹配

SELECT @@[SESSION | GLOBAL] 变量名; # 查看指定的

设置系统变量

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

17.3.2 用户自定义变量

用户自定义变量没有全局变量，只能在当前客户端连接生效，系统变量是两个@，用户自定义变量则只有一个@。

赋值

SET @变量名 = 值;

SET @变量名 := 值;

SELECT @变量名 := 表达式;

SELECT 字段名 INTO @变量名 FROM 表名; # 将表中的字段值赋值给变量

查看值

SELECT @变量名;

17.3.3 局部变量

可以在储存过程中使用，作用范围介于 BEGIN 和 END 之间。

定义局部变量，赋值操作同上

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

演示

DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
    DECLARE info_count int DEFAULT 0; # 定义局部变量 info_count，默认值为 0
    SELECT count(*) INTO info_count FROM info;
    SELECT info_count;
END $$
DELIMITER ;

CALL p1();

file

17.3.4 存储过程的参数传递

类似于一般变成语言中函数传入参数和返回值。

这里给出一个例子，创建一个存储过程 p2，有三个参数，分别是传入参数 in_arg，传出参数 out_arg，传入传出参数 inout_arg

DELIMITER $$
CREATE PROCEDURE p2(IN in_arg int,
                    OUT out_arg int,
                    INOUT inout_arg int)
BEGIN
    SET out_arg := in_arg + inout_arg;
    SET inout_arg := 2 * out_arg;
END $$
DELIMITER ;

调用存储过程

SET @inout = 3;
CALL p2(7, @out, @inout);
SELECT @out, @inout;

file

17.4 条件判断

17.4.1 IF

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

示例

DELIMITER $$
CREATE PROCEDURE p3(score float)
BEGIN
    IF score > 90 THEN
        SELECT '优秀';
    ELSEIF score > 75 THEN
        SELECT '良好';
    ELSEIF score > 60 THEN
        SELECT '及格';
    ELSE
        SELECT '不及格';
    END IF;
END $$
DELIMITER ;

CALL p3(85);

file

17.4.2 CASE

两种语法参考流程函数部分：https://blog.iyatt.com/?p=12631#94_%E6%B5%81%E7%A8%8B%E5%87%BD%E6%95%B0

例一

DELIMITER $$
CREATE PROCEDURE p4(score float)
BEGIN
    CASE
        WHEN score > 90 THEN
            SELECT '优秀';
        WHEN score > 75 THEN
            SELECT '良好';
        WHEN score > 60 THEN
            SELECT '及格';
        ELSE
            SELECT '不及格';
    END CASE;
END $$
DELIMITER ;

CALL p4(59);

例二

DELIMITER $$
CREATE PROCEDURE p5(grades char(1))
BEGIN
    CASE grades
        WHEN 'A' THEN
            SELECT '优秀';
        WHEN 'B' THEN
            SELECT '良好';
        WHEN 'C' THEN
            SELECT '一般';
        ELSE
            SELECT '未知';
    END CASE;
END $$
DELIMITER ;

CALL p5('C');

17.5 循环

17.5.1 WHILE

WHILE 条件 DO
	...
END WHILE

示例
累加 1-100

DELIMITER $$
CREATE PROCEDURE p6(n int)
BEGIN
    DECLARE sum int DEFAULT 0;
    DECLARE counter int DEFAULT 0;
    WHILE counter < n DO
        SET counter := counter + 1;
        SET sum := sum + counter;
    END WHILE;
    SELECT sum;
END $$
DELIMITER ;

CALL p6(100);

17.5.2 REPEAT

WHILE 是满足条件执行循环，REPEAT 是满足条件退出循环

REPEAT
	...
	UNTIL 条件
END REPEAT;

示例

DELIMITER $$
CREATE PROCEDURE p7(n int)
BEGIN
    DECLARE sum int DEFAULT 0;
    REPEAT
        SET sum := sum + n;
        SET n := n - 1;
    UNTIL n = 0
    END REPEAT;
    SELECT sum;
END $$
DELIMITER ;

CALL p7(100);

17.5.3 LOOP

LOOP 循环本身不带退出条件判断，是可以实现无限循环的，通过自行调用 LEAVE 退出循环，或者调用 ITERATE 跳过本轮循环，像一般编程语言里的 break 和 continue。

自定义循环标签名: LOOP

END LOPP 标签名;

示例
累加 1-100 的偶数

DELIMITER $$
CREATE PROCEDURE p8(n int)
BEGIN
    DECLARE sum int DEFAULT 0;
    my_sum: LOOP
        IF n <= 0 THEN
            LEAVE my_sum; # 退出循环
        END IF ;

        IF n % 2 = 1 THEN
            SET n := n -1;
            ITERATE my_sum; # 跳过本轮循环
        END IF ;

        SET sum := sum + n;
        SET n := n - 1;
    END LOOP my_sum;
    SELECT sum;
END $$
DELIMITER ;

CALL p8(100);

17.6 游标和条件处理程序

游标的作用和一般编程语言里面的迭代器类似。

声明

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

打开

OPEN 游标名称;

获取游标记录

FETCH 游标名称 INTO 变量;

关闭游标

CLOSE 游标名称;

条件处理程序
这个有点像 Linux 捕获信号

DECLARE hander_action HANDLER FOR condition_value statement;

hander_action：

CONTINUE 继续执行当前程序
EXIT 终止执行当前程序

condition_value：

SQLSTATE sqlstate_value 状态码，如 02000
SQLWARNING 所有以 01 开头的 SQLSTATE 的简写
NOT FOUND 所有以 02 开头的 SQLSTATE 的简写
SQLEXCEPTIO 所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 的简写

示例
查询 info 表的 name 列和 age 列，并将它们存到另外一张表 temp_table 中

DELIMITER $$
CREATE PROCEDURE p9()
BEGIN
    DECLARE name1 char(2);
    DECLARE age1 tinyint;
    DECLARE my_cursor CURSOR FOR SELECT name, age FROM info; # 声明一个遍历 info 表 name、age 列的游标
    DECLARE EXIT HANDLER FOR SQLSTATE '02000' CLOSE my_cursor; # 没有这个条件处理程序，当 FETCH 遍历到没有数据的位置会报错 02000，所以这里捕获这个状态码，来主动执行退出

    OPEN my_cursor; # 打开游标

    # 临时表
    DROP TABLE IF EXISTS temp_table;

    CREATE TABLE temp_table (
        id tinyint PRIMARY KEY AUTO_INCREMENT,
        name char(2),
        age tinyint
    );

    WHILE TRUE DO
        FETCH my_cursor INTO name1, age1; # 从游标中取值
        INSERT INTO temp_table (name, age) VALUES (name1, age1); # 将取值插入新表中
    END WHILE ;

    CLOSE my_cursor; # 关闭游标

END $$
DELIMITER ;

CALL p9();

info 表
file