MySQL 存储过程

需求

有时候我们需要写一些逻辑代码来处理MySQL中的数据，尤其是在版本升级过程中，历史数据需要处理。这时候我们希望通过sql脚本就能完成数据的处理。

本例将介绍一个简单的业务逻辑

准备工作

创建3张表，一张学生表，一张班级表，一张学生与班级绑定关系表。 Version-1: 班级表中没有学生数量，导致每次查询班级学生数量都需要通过关联表统计。 Version-2: 新版本决定将学生数量统计出来，并存放到班级表中，方便查询。

这只是一个假想的需求，不要较真，只是演示如何通过sql实现。

Version-1 版本数据库

CREATE TABLE flyway.student (
    id INT UNSIGNED auto_increment NOT NULL,
    name varchar(16) NULL,
    age TINYINT NULL,
    CONSTRAINT student_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

CREATE TABLE `class` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `class_name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE flyway.student_with_class (
    id INT UNSIGNED auto_increment NOT NULL,
    student_id INT UNSIGNED NOT NULL,
    class_id INT UNSIGNED NOT NULL,
    CONSTRAINT student_with_class_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

INSERT INTO flyway.student
(name, age)
VALUES('小明', 7),('小王', 7),('小红', 7),('小花', 7);

INSERT INTO flyway.class
(class_name)
VALUES('一年级1班'),('一年级2班'),('一年级3班');

INSERT INTO flyway.student_with_class
(student_id, class_id)
VALUES(1, 1),(2, 2),(3, 3),(4, 1);

Version-2 升级脚本

-- 班级表新增学生数量字段
ALTER TABLE flyway.class ADD student_count INT UNSIGNED DEFAULT 0 NULL;

编写sql脚本处理数据

思路：查询出所有班级，循环遍历每个班级，并对每个班级进行数量统计，然后更新这个班级的学生数量。

-- 删除存储过程（如果存在）
drop procedure if exists countStudent;

-- 定义语句结束符号，默认是逗号，每写一个语句以分号结尾，MySQL会立即执行，在这里不能这样做。
DELIMITER $$

create procedure countStudent()
begin
    --  这里声明一个 int 类型的本地变量，用于后面存放值
    declare studentCount int DEFAULT 0;
    declare classId int;
    declare className varchar(16);
    --  这里定义一个结束标识变量，当所有班级都更新后，就可以结束任务了。
    declare done TINYINT DEFAULT 0;

    --  将查询出来的班级列表定义，这里用 cursor 来表明
    declare classes cursor for select id, class_name from class;
    declare CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    --  开启 cursor
    open classes;
        --  这里开始循环处理每个班级
        class: LOOP
            --  这里是获取 classes 中的一行数据，并将其复制给定义好的变量上。注意保持和查询顺序一致
            fetch from classes into classId, className;
            --  如果处理完了，就停止循环
            IF done THEN LEAVE class; END IF;
            -- 这里开启事务，视情况而定
            start transaction;
                select count(*) into studentCount from student_with_class swc where swc.class_id = classId;
                update class set student_count = studentCount where id = classId and class_name = className;
            commit;
        end loop;
    close classes;

end $$

-- 恢复结束符号为分号
delimiter ;

-- 调用存储过程，也即执行我们的业务逻辑
call countStudent();

-- 如果是一次性的就删除。
drop procedure countStudent;

注意事项：

一定要使用 DELIMITER $$ 重新声明语句结束符号
declare 变量一定要在 cursor 以及 HANDLER 之前定义。

难点（以前困扰我自己的）：

查询出来的数据用什么东西接收：通过 declare 声明变量
如何接收：通过 into 关键字，将 select 中得字段存储在声明的变量中
列表数据如何处理：循环加上获取每一行数据（fetch … into）

其他：

业务逻辑根据实际情况在循环体中处理，需结合MySQL特有的语法

总之，对我来说难点就是查询出来的数据如何处理，其他业务逻辑都有对应的语法。