mysql存储过程与触发器

🐬 MySQL 存储过程、触发器与 SQL 编程实战指南

服务端集中处理示意

📌 阅读导航

关系型数据库示意图

🧮 存储过程 = 把一段业务逻辑搬进数据库

一、存储过程（Stored Procedure） 🧮

1.1 什么是存储过程

存储过程是一组为了完成特定业务功能的 SQL 语句集合，经编译后存储在数据库服务器端，用户只需要通过

1
2
CALL 存储过程名(参数列表);
CALL 存储过程名(参数列表);

即可执行内部的一系列 SQL 操作，并得到结果。

可以理解为：把复杂的 SQL 逻辑写在数据库里，封装成一个“函数”供外部调用。

1.2 存储过程的特点

1.3 存储过程的优缺点

1.4 存储过程语法

在命令行中书写存储过程时，需要临时修改语句结束符，否则过程体中的分号会被误认为语句结束：

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
-- 1. 修改结束符为 //
DELIMITER //

-- 2. 创建存储过程
CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
    -- 多条 SQL 语句
END //

-- 3. 改回默认结束符 ;
DELIMITER ;
-- 1. 修改结束符为 //
DELIMITER //

-- 2. 创建存储过程
CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
    -- 多条 SQL 语句
END //

-- 3. 改回默认结束符 ;
DELIMITER ;
CALL 存储过程名(参数列表);
CALL 存储过程名(参数列表);
-- 查看某个数据库下的所有存储过程
SELECT *
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = '数据库名';

-- 查看指定存储过程的创建语句
SHOW CREATE PROCEDURE 存储过程名;
-- 查看某个数据库下的所有存储过程
SELECT *
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = '数据库名';

-- 查看指定存储过程的创建语句
SHOW CREATE PROCEDURE 存储过程名;
DROP PROCEDURE [IF EXISTS] 存储过程名;
DROP PROCEDURE [IF EXISTS] 存储过程名;

1.5 存储过程示例：计算学生总分

假设有 exam 表，字段包含 name, chinese, math, english：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DELIMITER //
CREATE PROCEDURE p_calAvg()
BEGIN
    SELECT name,
           chinese + math + english AS total
    FROM exam;
END //
DELIMITER ;

-- 调用
CALL p_calAvg();
DELIMITER //
CREATE PROCEDURE p_calAvg()
BEGIN
    SELECT name,
           chinese + math + english AS total
    FROM exam;
END //
DELIMITER ;

-- 调用
CALL p_calAvg();

说明：

  • 存储过程中可以写任意合法 SQL。
  • 在命令行中一定要正确设置 DELIMITER，避免创建语句提前结束。

🔤 三个维度理解变量

二、MySQL 变量体系 🧾

MySQL 中常见变量分为三类：

  1. 系统变量（GLOBAL / SESSION）

  2. 用户自定义变量（@var）

  3. 局部变量（DECLARE 声明）

2.1 系统变量

系统变量控制 MySQL 服务器的行为和性能，有**全局（GLOBAL）和会话（SESSION）**之分。

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
-- 查看所有系统变量
SHOW [GLOBAL | SESSION] VARIABLES;

-- 模糊查询
SHOW [GLOBAL | SESSION] VARIABLES LIKE 'xxx';
SHOW [GLOBAL | SESSION] VARIABLES LIKE '%xxx%';

-- 使用 SELECT 查看
SELECT @@[GLOBAL | SESSION].变量名;

-- 示例
SHOW GLOBAL VARIABLES LIKE 'auto%';
SHOW SESSION VARIABLES LIKE 'char%';
SELECT @@GLOBAL.autocommit;
-- 查看所有系统变量
SHOW [GLOBAL | SESSION] VARIABLES;

-- 模糊查询
SHOW [GLOBAL | SESSION] VARIABLES LIKE 'xxx';
SHOW [GLOBAL | SESSION] VARIABLES LIKE '%xxx%';

-- 使用 SELECT 查看
SELECT @@[GLOBAL | SESSION].变量名;

-- 示例
SHOW GLOBAL VARIABLES LIKE 'auto%';
SHOW SESSION VARIABLES LIKE 'char%';
SELECT @@GLOBAL.autocommit;
SET [GLOBAL | SESSION] 变量名 = 值;
SET @@SESSION.变量名 = 值;

-- 示例：关闭 / 开启当前会话的自动提交
SET @@SESSION.autocommit = 0;
SET autocommit = 1;
SET [GLOBAL | SESSION] 变量名 = 值;
SET @@SESSION.变量名 = 值;

-- 示例：关闭 / 开启当前会话的自动提交
SET @@SESSION.autocommit = 0;
SET autocommit = 1;

注意：

  • 未显式写 GLOBAL \| SESSION 时，默认是 SESSION。
  • 会话变量在会话结束后失效，新会话从 GLOBAL 变量读默认值。
  • GLOBAL 变量重启后会恢复默认，要永久生效需改配置文件。

2.2 用户自定义变量

用户自定义变量以 @ 开头，无需提前声明，作用域为当前会话。

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
51
52
53
54
55
56
57
58
59
60
-- 写法一
SET @var_name = expr;

-- 写法二（推荐，避免与比较混淆）
SET @var_name := expr;

-- 写法三：在 SELECT 中赋值
SELECT @var_name := expr;

-- 写法四：查询结果赋值
SELECT 列名 INTO @var_name
FROM 表名
WHERE ...;
-- 写法一
SET @var_name = expr;

-- 写法二（推荐，避免与比较混淆）
SET @var_name := expr;

-- 写法三：在 SELECT 中赋值
SELECT @var_name := expr;

-- 写法四：查询结果赋值
SELECT 列名 INTO @var_name
FROM 表名
WHERE ...;
-- 定义并查看
SET @age := 18;
SELECT @age;

-- 从表中查询并赋值
SELECT sno INTO @sno
FROM student
WHERE id = 1;
SELECT @sno;

-- 统计总数赋值
SELECT COUNT(*) INTO @count
FROM student;
SELECT @count;

-- 访问未赋值变量，返回 NULL
SELECT @var;
-- 定义并查看
SET @age := 18;
SELECT @age;

-- 从表中查询并赋值
SELECT sno INTO @sno
FROM student
WHERE id = 1;
SELECT @sno;

-- 统计总数赋值
SELECT COUNT(*) INTO @count
FROM student;
SELECT @count;

-- 访问未赋值变量，返回 NULL
SELECT @var;

建议使用 := 作为赋值符号，避免与 = 比较运算混淆。

2.3 局部变量

局部变量只在存储过程、函数或触发器内部有效，且必须使用 DECLARE 提前声明。

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
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 示例：
DECLARE stu_count INT DEFAULT 0;
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 示例：
DECLARE stu_count INT DEFAULT 0;
SET var_name = 值;
SET var_name := 值;    -- 推荐
SELECT 列名 INTO var_name
FROM 表名
WHERE ...;
SET var_name = 值;
SET var_name := 值;    -- 推荐
SELECT 列名 INTO var_name
FROM 表名
WHERE ...;
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
    -- 定义局部变量
    DECLARE stu_count INT DEFAULT 0;

    -- 查询并赋值
    SELECT COUNT(*) INTO stu_count
    FROM student;

    -- 使用变量
    SELECT stu_count;
END //
DELIMITER ;

CALL p1();
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
    -- 定义局部变量
    DECLARE stu_count INT DEFAULT 0;

    -- 查询并赋值
    SELECT COUNT(*) INTO stu_count
    FROM student;

    -- 使用变量
    SELECT stu_count;
END //
DELIMITER ;

CALL p1();

  • 变量名不区分大小写。

  • 存储过程 / 函数中，局部变量必须先声明再使用。

  • 用户自定义变量在会话结束时失效，局部变量在过程或函数结束时失效。

  • 避免使用保留字作为变量名。

三、SQL 编程：流程控制与参数 🧠

3.1 IF 条件判断

1
2
3
4
5
6
7
8
9
10
11
12
13
14
IF 条件1 THEN
    -- 语句块
[ELSEIF 条件2 THEN
    -- 语句块
ELSE
    -- 语句块]
END IF;
IF 条件1 THEN
    -- 语句块
[ELSEIF 条件2 THEN
    -- 语句块
ELSE
    -- 语句块]
END IF;

规则：

  • ≥ 90：优秀

  • ≥ 80 且 < 90：良好

  • ≥ 60 且 < 80：及格

  • < 60：不及格

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
DELIMITER //
CREATE PROCEDURE p2()
BEGIN
    DECLARE score  INT         DEFAULT 86;
    DECLARE result VARCHAR(10);

    IF score >= 90 THEN
        SET result := &#x27;优秀&#x27;;
    ELSEIF score >= 80 AND score < 90 THEN
        SET result := &#x27;良好&#x27;;
    ELSEIF score >= 60 AND score < 80 THEN
        SET result := &#x27;及格&#x27;;
    ELSE
        SET result := &#x27;不及格&#x27;;
    END IF;

    SELECT result;
END //
DELIMITER ;

CALL p2();
DELIMITER //
CREATE PROCEDURE p2()
BEGIN
    DECLARE score  INT         DEFAULT 86;
    DECLARE result VARCHAR(10);

    IF score >= 90 THEN
        SET result := &#x27;优秀&#x27;;
    ELSEIF score >= 80 AND score < 90 THEN
        SET result := &#x27;良好&#x27;;
    ELSEIF score >= 60 AND score < 80 THEN
        SET result := &#x27;及格&#x27;;
    ELSE
        SET result := &#x27;不及格&#x27;;
    END IF;

    SELECT result;
END //
DELIMITER ;

CALL p2();

实际项目中，分数应该通过参数传入而不是写死在过程里，这就涉及参数定义。

3.2 存储过程参数

类型 说明
IN 输入参数，调用时传入，默认类型
OUT 输出参数，相当于返回值
INOUT 既可作为输入也可作为输出 
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
DELIMITER //
CREATE PROCEDURE 存储过程名 (
    [IN | OUT | INOUT] 参数名 参数类型,
    ...
)
BEGIN
    -- SQL 语句
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE 存储过程名 (
    [IN | OUT | INOUT] 参数名 参数类型,
    ...
)
BEGIN
    -- SQL 语句
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE p3(IN score INT, OUT result VARCHAR(10))
BEGIN
    IF score >= 90 THEN
        SET result := &#x27;优秀&#x27;;
    ELSEIF score >= 80 AND score < 90 THEN
        SET result := &#x27;良好&#x27;;
    ELSEIF score >= 60 AND score < 80 THEN
        SET result := &#x27;及格&#x27;;
    ELSE
        SET result := &#x27;不及格&#x27;;
    END IF;
END //
DELIMITER ;

-- 调用
CALL p3(88, @result);
SELECT @result;
DELIMITER //
CREATE PROCEDURE p3(IN score INT, OUT result VARCHAR(10))
BEGIN
    IF score >= 90 THEN
        SET result := &#x27;优秀&#x27;;
    ELSEIF score >= 80 AND score < 90 THEN
        SET result := &#x27;良好&#x27;;
    ELSEIF score >= 60 AND score < 80 THEN
        SET result := &#x27;及格&#x27;;
    ELSE
        SET result := &#x27;不及格&#x27;;
    END IF;
END //
DELIMITER ;

-- 调用
CALL p3(88, @result);
SELECT @result;
DELIMITER //
CREATE PROCEDURE p4(INOUT score INT)
BEGIN
    SET score := score + 10;
END //
DELIMITER ;

SET @score := 98;
CALL p4(@score);
SELECT @score;   -- 结果为 108
DELIMITER //
CREATE PROCEDURE p4(INOUT score INT)
BEGIN
    SET score := score + 10;
END //
DELIMITER ;

SET @score := 98;
CALL p4(@score);
SELECT @score;   -- 结果为 108

3.3 CASE 分支

CASE 有两种写法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

注意：

  • statement_list 不能为空，必要时用 BEGIN ... END 包裹。
  • 没有任何分支匹配且没有 ELSE，会导致 CASE 语句错误。
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
51
52
53
54
55
56
57
58
59
60
61
62
DELIMITER //
CREATE PROCEDURE p5(IN code INT, OUT result VARCHAR(50))
BEGIN
    CASE code
        WHEN 0     THEN SET result := &#x27;成功&#x27;;
        WHEN 10001 THEN SET result := &#x27;用户名或密码错误&#x27;;
        WHEN 10002 THEN SET result := &#x27;您没有对应的权限&#x27;;
        WHEN 20001 THEN SET result := &#x27;你传入的参数有误&#x27;;
        WHEN 20002 THEN SET result := &#x27;没有找到相应的结果&#x27;;
        ELSE            SET result := &#x27;服务器错误，请联系管理员&#x27;;
    END CASE;
END //
DELIMITER ;

CALL p5(10001, @result);
SELECT @result;
DELIMITER //
CREATE PROCEDURE p5(IN code INT, OUT result VARCHAR(50))
BEGIN
    CASE code
        WHEN 0     THEN SET result := &#x27;成功&#x27;;
        WHEN 10001 THEN SET result := &#x27;用户名或密码错误&#x27;;
        WHEN 10002 THEN SET result := &#x27;您没有对应的权限&#x27;;
        WHEN 20001 THEN SET result := &#x27;你传入的参数有误&#x27;;
        WHEN 20002 THEN SET result := &#x27;没有找到相应的结果&#x27;;
        ELSE            SET result := &#x27;服务器错误，请联系管理员&#x27;;
    END CASE;
END //
DELIMITER ;

CALL p5(10001, @result);
SELECT @result;
DELIMITER //
CREATE PROCEDURE p6(IN month INT, OUT result VARCHAR(50))
BEGIN
    CASE
        WHEN month >= 1  AND month <= 3  THEN SET result := &#x27;第一季度&#x27;;
        WHEN month >= 4  AND month <= 6  THEN SET result := &#x27;第二季度&#x27;;
        WHEN month >= 7  AND month <= 9  THEN SET result := &#x27;第三季度&#x27;;
        WHEN month >= 10 AND month <= 12 THEN SET result := &#x27;第四季度&#x27;;
        ELSE SET result := &#x27;非法参数&#x27;;
    END CASE;
END //
DELIMITER ;

CALL p6(6, @result);
SELECT @result;
DELIMITER //
CREATE PROCEDURE p6(IN month INT, OUT result VARCHAR(50))
BEGIN
    CASE
        WHEN month >= 1  AND month <= 3  THEN SET result := &#x27;第一季度&#x27;;
        WHEN month >= 4  AND month <= 6  THEN SET result := &#x27;第二季度&#x27;;
        WHEN month >= 7  AND month <= 9  THEN SET result := &#x27;第三季度&#x27;;
        WHEN month >= 10 AND month <= 12 THEN SET result := &#x27;第四季度&#x27;;
        ELSE SET result := &#x27;非法参数&#x27;;
    END CASE;
END //
DELIMITER ;

CALL p6(6, @result);
SELECT @result;

🔁 三种循环如何选？

四、循环控制：WHILE / REPEAT / LOOP 🔁

4.1 WHILE 循环

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
WHILE search_condition DO
    statement_list;
END WHILE;
WHILE search_condition DO
    statement_list;
END WHILE;
DELIMITER //
CREATE PROCEDURE p7(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 //
DELIMITER ;

CALL p7(100);
DELIMITER //
CREATE PROCEDURE p7(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 //
DELIMITER ;

CALL p7(100);

4.2 REPEAT 循环

1
2
3
4
5
6
7
8
REPEAT
    statement_list;
UNTIL search_condition
END REPEAT;
REPEAT
    statement_list;
UNTIL search_condition
END REPEAT;

特点：先执行一次，再判断，类似 C / Java 中的 do ... while。

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
DELIMITER //
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 //
DELIMITER ;

CALL p8(100);
DELIMITER //
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 //
DELIMITER ;

CALL p8(100);

4.3 LOOP 循环 + LEAVE / ITERATE

LOOP 本身是一个简单的无限循环结构，一般配合：

  • LEAVE label：退出整个循环，相当于 break

  • ITERATE label：跳过本次，进入下一轮，相当于 continue

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
[begin_label:] LOOP
    statement_list;
END LOOP [end_label];
[begin_label:] LOOP
    statement_list;
END LOOP [end_label];
DELIMITER //
CREATE PROCEDURE p9(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;

    sum_label: LOOP
        IF n <= 0 THEN
            LEAVE sum_label;
        END IF;

        SET total := total + n;
        SET n := n - 1;
    END LOOP sum_label;

    SELECT total;
END //
DELIMITER ;

CALL p9(100);
DELIMITER //
CREATE PROCEDURE p9(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;

    sum_label: LOOP
        IF n <= 0 THEN
            LEAVE sum_label;
        END IF;

        SET total := total + n;
        SET n := n - 1;
    END LOOP sum_label;

    SELECT total;
END //
DELIMITER ;

CALL p9(100);
DELIMITER //
CREATE PROCEDURE p10(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;

    sum_label: LOOP
        IF n <= 0 THEN
            LEAVE sum_label;
        END IF;

        -- 奇数，直接跳过
        IF n % 2 = 1 THEN
            SET n := n - 1;
            ITERATE sum_label;
        END IF;

        SET total := total + n;
        SET n := n - 1;
    END LOOP sum_label;

    SELECT total;
END //
DELIMITER ;

CALL p10(100);
DELIMITER //
CREATE PROCEDURE p10(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;

    sum_label: LOOP
        IF n <= 0 THEN
            LEAVE sum_label;
        END IF;

        -- 奇数，直接跳过
        IF n % 2 = 1 THEN
            SET n := n - 1;
            ITERATE sum_label;
        END IF;

        SET total := total + n;
        SET n := n - 1;
    END LOOP sum_label;

    SELECT total;
END //
DELIMITER ;

CALL p10(100);

五、游标（Cursor）与条件处理程序（Handler） 🎯

5.1 游标概念

游标是 MySQL 中用于逐行遍历结果集的对象，只能在存储过程 / 函数中使用，且为只读。

例如：

1
2
SELECT * FROM student;
SELECT * FROM student;

得到多行记录时，游标可以一行一行 FETCH 出来做进一步处理（如写入另一张表、统计、生成报表等）。

5.2 游标使用语法

使用顺序：声明 → 打开 → 取数 → 关闭。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 1. 声明游标（必须在 Handler 之前，变量之前）
DECLARE cur_name CURSOR FOR 查询语句;

-- 2. 打开游标
OPEN cur_name;

-- 3. 取当前行到变量
FETCH cur_name INTO 变量1, 变量2, ...;

-- 4. 关闭游标
CLOSE cur_name;
-- 1. 声明游标（必须在 Handler 之前，变量之前）
DECLARE cur_name CURSOR FOR 查询语句;

-- 2. 打开游标
OPEN cur_name;

-- 3. 取当前行到变量
FETCH cur_name INTO 变量1, 变量2, ...;

-- 4. 关闭游标
CLOSE cur_name;

5.3 游标综合示例：按班级写入新表

需求：传入班级编号 class_id，查询 student 表中属于该班级的学生，将数据写入新表 t_student_class(id, student_name, class_name)。

逻辑拆解：

  1. 定义变量接收每一行的学生姓名、班级名。

  2. 声明游标，查询目标结果集。

  3. 创建目标表（如不存在）。

  4. 打开游标。

  5. 循环 FETCH 游标中的每一行，并写入新表。

  6. 关闭游标。

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
51
52
53
54
55
56
57
58
59
60
61
62
DELIMITER //
CREATE PROCEDURE p11(IN class_id INT)
BEGIN
    DECLARE student_name VARCHAR(20);
    DECLARE class_name   VARCHAR(20);

    DECLARE s_cursor CURSOR FOR
        SELECT s.name, c.name
        FROM student s
        JOIN class c ON s.class_id = c.id
        WHERE s.class_id = class_id;

    -- 创建目标表（如果不存在）
    CREATE TABLE IF NOT EXISTS t_student_class (
        id           INT PRIMARY KEY AUTO_INCREMENT,
        student_name VARCHAR(20),
        class_name   VARCHAR(20)
    );

    OPEN s_cursor;

    WHILE TRUE DO
        FETCH s_cursor INTO student_name, class_name;
        
        INSERT INTO t_student_class (student_name, class_name)
        VALUES (student_name, class_name);
    END WHILE;

    CLOSE s_cursor;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE p11(IN class_id INT)
BEGIN
    DECLARE student_name VARCHAR(20);
    DECLARE class_name   VARCHAR(20);

    DECLARE s_cursor CURSOR FOR
        SELECT s.name, c.name
        FROM student s
        JOIN class c ON s.class_id = c.id
        WHERE s.class_id = class_id;

    -- 创建目标表（如果不存在）
    CREATE TABLE IF NOT EXISTS t_student_class (
        id           INT PRIMARY KEY AUTO_INCREMENT,
        student_name VARCHAR(20),
        class_name   VARCHAR(20)
    );

    OPEN s_cursor;

    WHILE TRUE DO
        FETCH s_cursor INTO student_name, class_name;
        
        INSERT INTO t_student_class (student_name, class_name)
        VALUES (student_name, class_name);
    END WHILE;

    CLOSE s_cursor;
END //
DELIMITER ;

问题：WHILE TRUE 是死循环，游标读到末尾没有更多记录时会触发“无数据”错误，需要条件处理程序来优雅退出循环。

5.4 条件处理程序（Condition Handler）

Handler 用于在存储过程 / 函数中捕获 SQL 异常或特定状态，并指定处理方式：

  • CONTINUE：处理后继续执行后续语句

  • EXIT：处理后退出当前 BEGIN ... END 块

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
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
    statement;

handler_action:
    CONTINUE | EXIT;

condition_value:
    mysql_error_code           -- 具体错误码
  | SQLSTATE [VALUE] &#x27;xxxxx&#x27;  -- 五位 SQLSTATE 码
  | SQLWARNING                -- 01 开头的 SQLSTATE
  | NOT FOUND                 -- 02 开头的 SQLSTATE，常用于“未找到/游标耗尽”
  | SQLEXCEPTION              -- 其余所有错误
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
    statement;

handler_action:
    CONTINUE | EXIT;

condition_value:
    mysql_error_code           -- 具体错误码
  | SQLSTATE [VALUE] &#x27;xxxxx&#x27;  -- 五位 SQLSTATE 码
  | SQLWARNING                -- 01 开头的 SQLSTATE
  | NOT FOUND                 -- 02 开头的 SQLSTATE，常用于“未找到/游标耗尽”
  | SQLEXCEPTION              -- 其余所有错误

官方错误码参考：

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
DELIMITER //
CREATE PROCEDURE p12(IN class_id INT)
BEGIN
    DECLARE student_name VARCHAR(20);
    DECLARE class_name   VARCHAR(20);
    DECLARE is_done      BOOL DEFAULT FALSE;  -- 游标是否结束标识

    DECLARE s_cursor CURSOR FOR
        SELECT s.name, c.name
        FROM student s
        JOIN class c ON s.class_id = c.id
        WHERE s.class_id = class_id;

    -- 声明条件处理程序:当游标读到末尾(NOT FOUND)时,将 is_done 设为 TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = TRUE;

    -- 创建目标表(如果不存在)
    CREATE TABLE IF NOT EXISTS t_student_class (
        id           INT PRIMARY KEY AUTO_INCREMENT,
        student_name VARCHAR(20),
        class_name   VARCHAR(20)
    );

    OPEN s_cursor;

    read_loop: LOOP
        FETCH s_cursor INTO student_name, class_name;
        
        -- 如果游标已读完,退出循环
        IF is_done THEN
            LEAVE read_loop;
        END IF;

        INSERT INTO t_student_class (student_name, class_name)
        VALUES (student_name, class_name);
    END LOOP read_loop;

    CLOSE s_cursor;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE p12(IN class_id INT)
BEGIN
    DECLARE student_name VARCHAR(20);
    DECLARE class_name   VARCHAR(20);
    DECLARE is_done      BOOL DEFAULT FALSE;  -- 游标是否结束标识

    DECLARE s_cursor CURSOR FOR
        SELECT s.name, c.name
        FROM student s
        JOIN class c ON s.class_id = c.id
        WHERE s.class_id = class_id;

    -- 声明条件处理程序:当游标读到末尾(NOT FOUND)时,将 is_done 设为 TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = TRUE;

    -- 创建目标表(如果不存在)
    CREATE TABLE IF NOT EXISTS t_student_class (
        id           INT PRIMARY KEY AUTO_INCREMENT,
        student_name VARCHAR(20),
        class_name   VARCHAR(20)
    );

    OPEN s_cursor;

    read_loop: LOOP
        FETCH s_cursor INTO student_name, class_name;
        
        -- 如果游标已读完,退出循环
        IF is_done THEN
            LEAVE read_loop;
        END IF;

        INSERT INTO t_student_class (student_name, class_name)
        VALUES (student_name, class_name);
    END LOOP read_loop;

    CLOSE s_cursor;
END //
DELIMITER ;

六、存储函数（Function） 🧩

存储函数与存储过程类似，但存在几个关键区别：

  • 必须有返回值（RETURNS type + RETURN 语句）。

  • 参数类型只能是 IN。

  • 使用方式类似内置函数，可以直接出现在 SELECT 中。

6.1 定义语法

在 MySQL 8.0 且开启 binlog 时，定义函数必须声明其特性（DETERMINISTIC、NO SQL、READS SQL DATA 等），否则会报错。

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
CREATE FUNCTION 函数名 (参数列表)
RETURNS 返回类型 [characteristic ...]
BEGIN
    -- SQL 语句
    RETURN ...;
END;

characteristic:
    [NOT] DETERMINISTIC
  | NO SQL
  | READS SQL DATA
  | MODIFIES SQL DATA;

-- 调用
SELECT 函数名(参数列表);
CREATE FUNCTION 函数名 (参数列表)
RETURNS 返回类型 [characteristic ...]
BEGIN
    -- SQL 语句
    RETURN ...;
END;

characteristic:
    [NOT] DETERMINISTIC
  | NO SQL
  | READS SQL DATA
  | MODIFIES SQL DATA;

-- 调用
SELECT 函数名(参数列表);

6.2 示例：1 累加到 n

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
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 //
DELIMITER ;

SELECT fun1(100);
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 //
DELIMITER ;

SELECT fun1(100);

⚡ 触发器适合做什么？

七、触发器（Trigger） ⚡

7.1 什么是触发器

触发器是与表绑定的数据库对象，当对表执行 INSERT、UPDATE 或 DELETE 操作时，按照事先定义好的逻辑自动触发执行。

特点：

  • 触发时机：

BEFORE（操作前）

  • AFTER（操作后）

触发事件：

  • INSERT、UPDATE、DELETE

通过 OLD / NEW 访问数据行：

  • INSERT：NEW 表示新增行

  • UPDATE：OLD 为修改前数据，NEW 为修改后数据

  • DELETE：OLD 为被删除的数据

行级触发器：一条语句影响多行时，每行都会触发一次。

语句级触发器：一条语句无论影响多少行，只触发一次。

MySQL 只支持行级触发器，不支持语句级触发器。

7.2 触发器定义与管理

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
DELIMITER //
CREATE TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
    -- trigger_stmt
END //
DELIMITER ;

trigger_time : BEFORE | AFTER;
trigger_event: INSERT | UPDATE | DELETE;
DELIMITER //
CREATE TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
    -- trigger_stmt
END //
DELIMITER ;

trigger_time : BEFORE | AFTER;
trigger_event: INSERT | UPDATE | DELETE;
SHOW TRIGGERS;
SHOW TRIGGERS;
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

7.3 触发器实战：记录学生表变更日志

目标：对 student 表的插入、更新、删除操作做审计，将变更信息写入 student_log 日志表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE student_log (
    id             BIGINT PRIMARY KEY AUTO_INCREMENT,
    operation_type VARCHAR(10)  NOT NULL COMMENT &#x27;操作类型：insert/update/delete&#x27;,
    operation_time DATETIME     NOT NULL COMMENT &#x27;操作时间&#x27;,
    operation_id   BIGINT       NOT NULL COMMENT &#x27;操作的记录ID&#x27;,
    operation_data VARCHAR(500)          COMMENT &#x27;操作数据&#x27;
);
CREATE TABLE student_log (
    id             BIGINT PRIMARY KEY AUTO_INCREMENT,
    operation_type VARCHAR(10)  NOT NULL COMMENT &#x27;操作类型：insert/update/delete&#x27;,
    operation_time DATETIME     NOT NULL COMMENT &#x27;操作时间&#x27;,
    operation_id   BIGINT       NOT NULL COMMENT &#x27;操作的记录ID&#x27;,
    operation_data VARCHAR(500)          COMMENT &#x27;操作数据&#x27;
);

日志表常见字段：操作类型、时间、目标主键 ID、新旧数据快照等。

7.4 插入触发器：记录新增数据

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
DELIMITER //
CREATE TRIGGER trg_student_insert
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    )
    VALUES (
        &#x27;insert&#x27;,
        NOW(),
        NEW.id,
        CONCAT(&#x27;姓名:&#x27;, NEW.name, &#x27; | 性别:&#x27;, NEW.gender, &#x27; | 班级ID:&#x27;, NEW.class_id)
    );
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER trg_student_insert
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    )
    VALUES (
        &#x27;insert&#x27;,
        NOW(),
        NEW.id,
        CONCAT(&#x27;姓名:&#x27;, NEW.name, &#x27; | 性别:&#x27;, NEW.gender, &#x27; | 班级ID:&#x27;, NEW.class_id)
    );
END //
DELIMITER ;

测试插入触发器：

1
2
3
4
5
6
INSERT INTO student (name, gender, class_id) VALUES (&#x27;张三&#x27;, &#x27;男&#x27;, 1);

SELECT * FROM student_log;
INSERT INTO student (name, gender, class_id) VALUES (&#x27;张三&#x27;, &#x27;男&#x27;, 1);

SELECT * FROM student_log;

结果：

1
2
3
4
5
6
7
8
9
10
+----+----------------+---------------------+--------------+------------------------------------------+
| id | operation_type | operation_time      | operation_id | operation_data                           |
+----+----------------+---------------------+--------------+------------------------------------------+
|  1 | insert         | 2026-01-14 20:30:00 |            1 | 姓名:张三 | 性别:男 | 班级ID:1              |
+----+----------------+---------------------+--------------+------------------------------------------+
+----+----------------+---------------------+--------------+------------------------------------------+
| id | operation_type | operation_time      | operation_id | operation_data                           |
+----+----------------+---------------------+--------------+------------------------------------------+
|  1 | insert         | 2026-01-14 20:30:00 |            1 | 姓名:张三 | 性别:男 | 班级ID:1              |
+----+----------------+---------------------+--------------+------------------------------------------+

7.5 更新触发器：记录新旧值

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
DELIMITER //
CREATE TRIGGER trg_student_update
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    )
    VALUES (
        &#x27;update&#x27;,
        NOW(),
        NEW.id,
        CONCAT(&#x27;旧值 => 姓名:&#x27;, OLD.name, &#x27; | 性别:&#x27;, OLD.gender, &#x27; | 班级ID:&#x27;, OLD.class_id,
               &#x27; || 新值 => 姓名:&#x27;, NEW.name, &#x27; | 性别:&#x27;, NEW.gender, &#x27; | 班级ID:&#x27;, NEW.class_id)
    );
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER trg_student_update
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    )
    VALUES (
        &#x27;update&#x27;,
        NOW(),
        NEW.id,
        CONCAT(&#x27;旧值 => 姓名:&#x27;, OLD.name, &#x27; | 性别:&#x27;, OLD.gender, &#x27; | 班级ID:&#x27;, OLD.class_id,
               &#x27; || 新值 => 姓名:&#x27;, NEW.name, &#x27; | 性别:&#x27;, NEW.gender, &#x27; | 班级ID:&#x27;, NEW.class_id)
    );
END //
DELIMITER ;

测试更新触发器:

1
2
3
4
5
6
UPDATE student SET name = &#x27;李四&#x27; WHERE id = 1;

SELECT * FROM student_log;
UPDATE student SET name = &#x27;李四&#x27; WHERE id = 1;

SELECT * FROM student_log;

结果:

1
2
3
4
5
6
+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+
| id | operation_type | operation_time      | operation_id | operation_data                                                               |
+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+
|  1 | insert         | 2026-01-14 20:30:00 |            1 | 姓名:张三 | 性别:男 | 班级ID:1                                            |
|  2 | update         | 2026-01-14 20:31:00 |            1 | 旧值 => 姓名:张三 | 性别:男 | 班级ID:1 || 新值 => 姓名:李四 | 性别:男 | 班级ID:1 |
+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+

可以看到，当一条 UPDATE 语句影响多行时，触发器会逐行记录日志，每行都写一条到 student_log。

7.6 删除触发器：记录被删数据

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
DELIMITER //
CREATE TRIGGER trg_student_delete
AFTER DELETE ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    )
    VALUES (
        &#x27;delete&#x27;,
        NOW(),
        OLD.id,
        CONCAT(&#x27;姓名:&#x27;, OLD.name, &#x27; | 性别:&#x27;, OLD.gender, &#x27; | 班级ID:&#x27;, OLD.class_id)
    );
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER trg_student_delete
AFTER DELETE ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    )
    VALUES (
        &#x27;delete&#x27;,
        NOW(),
        OLD.id,
        CONCAT(&#x27;姓名:&#x27;, OLD.name, &#x27; | 性别:&#x27;, OLD.gender, &#x27; | 班级ID:&#x27;, OLD.class_id)
    );
END //
DELIMITER ;

测试删除触发器：

1
2
3
4
5
6
DELETE FROM student WHERE id = 1;

SELECT * FROM student_log;
DELETE FROM student WHERE id = 1;

SELECT * FROM student_log;

结果：

1
2
+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+| id | operation_type | operation_time      | operation_id | operation_data                                                               |+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+|  1 | insert         | 2026-01-14 20:30:00 |            1 | 姓名:张三 | 性别:男 | 班级ID:1                                            ||  2 | update         | 2026-01-14 20:31:00 |            1 | 旧值 => 姓名:张三 | 性别:男 | 班级ID:1 || 新值 => 姓名:李四 | 性别:男 | 班级ID:1 ||  3 | delete         | 2026-01-14 20:32:00 |            1 | 姓名:李四 | 性别:男 | 班级ID:1                                            |+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+
+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+| id | operation_type | operation_time      | operation_id | operation_data                                                               |+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+|  1 | insert         | 2026-01-14 20:30:00 |            1 | 姓名:张三 | 性别:男 | 班级ID:1                                            ||  2 | update         | 2026-01-14 20:31:00 |            1 | 旧值 => 姓名:张三 | 性别:男 | 班级ID:1 || 新值 => 姓名:李四 | 性别:男 | 班级ID:1 ||  3 | delete         | 2026-01-14 20:32:00 |            1 | 姓名:李四 | 性别:男 | 班级ID:1                                            |+----+----------------+---------------------+--------------+------------------------------------------------------------------------------+

至此，我们利用 3 个触发器实现了对同一张表的插入、更新、删除全量审计，这是非常经典的面试与实战场景。

八、面试题汇总与详细解答 📚

下面对讲义中的面试题逐条进行回答，可直接作为面试复习提纲使用。

1. 存储过程的作用是什么？

  • 封装复杂业务逻辑，简化应用层代码。

  • 通过预编译提升执行性能。

  • 统一管理数据访问逻辑，方便维护和变更。

  • 通过权限控制提升安全性，只暴露过程而不暴露底层表结构。

  • 支持事务，在过程内实现复杂的事务控制。

2. 如何创建一个存储过程？

核心步骤：修改结束符 → CREATE PROCEDURE → BEGIN … END → 恢复结束符。

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER //
CREATE PROCEDURE proc_name(IN p1 INT)
BEGIN
    -- SQL 语句
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE proc_name(IN p1 INT)
BEGIN
    -- SQL 语句
END //
DELIMITER ;

然后使用 CALL proc_name(参数); 调用。

3. MySQL 中的变量都有哪几种？

  • 系统变量：GLOBAL / SESSION 级别，控制服务器行为。

  • 用户自定义变量：以 @ 开头，会话范围，无需声明。

  • 局部变量：在存储过程 / 函数 / 触发器内部，使用 DECLARE 声明。

4. 如何定义一个变量？

  • 局部变量：
1
2
3
4
DECLARE v_name INT DEFAULT 0;
SET v_name := 10;
DECLARE v_name INT DEFAULT 0;
SET v_name := 10;
  • 用户自定义变量：
1
2
3
4
SET @v_name := 10;
SELECT col INTO @v_name FROM t WHERE id = 1;
SET @v_name := 10;
SELECT col INTO @v_name FROM t WHERE id = 1;

5. MySQL 中使用变量是否需要提前声明？

  • 用户自定义变量（@var）：不需要提前声明，第一次赋值即创建。

  • 局部变量：必须使用 DECLARE 在 BEGIN ... END 中最前面声明后才能使用。

6. MySQL 中的参数分为哪几种？

  • IN：输入参数，调用时传入，默认类型。

  • OUT：输出参数，用于向调用者返回值。

  • INOUT：即可以作为输入，也可以在过程内部修改后作为输出。

7. 用过游标吗？游标的作用是什么？

是的。游标用于：

  • 在存储过程或函数中逐行遍历查询结果集。

  • 对每一行执行更细粒度的逻辑处理，如写入新表、做复杂计算、生成报表等。

本质：解决“一条 SQL 查询多行结果，如何在过程里一行一行处理”的问题。

8. 了解条件处理程序吗？介绍一下如何使用。

条件处理程序（Handler）用于在存储过程 / 函数中捕获特定 SQL 状态或错误，并指定处理动作（CONTINUE 或 EXIT）。

常见用法：

  • 与游标配合，当 NOT FOUND（结果集耗尽）时，设置标志变量，在循环中退出。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE is_done BOOL DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET is_done := TRUE;

read_loop: LOOP
    FETCH cur INTO v1, v2;
    IF is_done THEN
        LEAVE read_loop;
    END IF;
    -- 正常处理
END LOOP;
DECLARE is_done BOOL DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET is_done := TRUE;

read_loop: LOOP
    FETCH cur INTO v1, v2;
    IF is_done THEN
        LEAVE read_loop;
    END IF;
    -- 正常处理
END LOOP;

9. 存储函数与存储过程的区别是什么？

主要区别：

  1. 返回值：

  • 存储函数必须有返回值（RETURNS + RETURN）。

  • 存储过程可以没有返回值，通过 OUT / INOUT 参数返回。

  1. 调用方式：

  • 存储函数可以直接出现在 SELECT、WHERE 等表达式中。

  • 存储过程通过 CALL proc_name(...) 调用，不能直接用于表达式。

  1. 参数类型：

  • 存储函数参数只能是 IN。

  • 存储过程支持 IN、OUT、INOUT。

  1. 使用场景：

  • 存储函数更偏向“计算一个值”，类似内置函数。

  • 存储过程更偏向“封装一段业务流程”。

10. 如何查看数据库中创建的存储过程？

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = &#x27;数据库名&#x27;;

SHOW CREATE PROCEDURE 存储过程名;
SELECT *
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = &#x27;数据库名&#x27;;

SHOW CREATE PROCEDURE 存储过程名;

11. 什么是触发器？

触发器是与表绑定的特殊对象，当对表执行 INSERT / UPDATE / DELETE 等操作时，会在指定时机（BEFORE / AFTER）自动执行预先定义好的 SQL 逻辑，无需在应用层显式调用。

常用于：审计日志、数据校验、级联维护、统计更新等场景。

12. MySQL 中触发器分为几种类型？

按触发事件：

  • INSERT 触发器

  • UPDATE 触发器

  • DELETE 触发器

按触发时机：

  • BEFORE 触发器

  • AFTER 触发器

MySQL 内部实现为行级触发器（对每一行记录触发一次）。

13. 行级触发器与语句级触发器的区别是什么？

  • 行级触发器：

一条 SQL 语句影响多行数据时，对每一行都会触发一次。

  • 可以使用 OLD / NEW 访问每一行的具体数据。

语句级触发器：

  • 无论影响多少行，一条 SQL 只触发一次。

  • 主要用于语句级别的统计或控制。

MySQL 仅支持行级触发器，不支持语句级触发器。

14. 触发器有哪些典型使用场景？

常见场景包括：

  1. 操作审计和日志记录：
  • 对重要表的增删改操作记录到日志表，例如 student_log。
  1. 数据校验与约束补充：
  • BEFORE 触发器中校验字段合法性，不满足时抛错。
  1. 自动维护冗余字段或统计字段：
  • 插入订单明细时自动更新订单总金额、库存数量等。
  1. 历史版本或数据归档：
  • 删除或更新前，把旧数据转存到归档表。

15. 如果更新一张表的记录，需要在日志表中记录更新前和更新后的值，如何实现？

思路：

  • 在目标表上创建一个 AFTER UPDATE 行级触发器。

  • 使用 OLD 和 NEW 读取更新前后的记录。

  • 把新旧数据以某种格式（比如用分隔符连接）写入日志表。

示例即前文 trg_student_update：

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
CREATE TRIGGER trg_student_update
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    )
    VALUES (
        &#x27;update&#x27;,
        NOW(),
        
CREATE TRIGGER trg_student_update
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    )
    VALUES (
        &#x27;update&#x27;,
        NOW(),

16. 如何查看数据库中创建的触发器？

1
2
SHOW TRIGGERS;
SHOW TRIGGERS;

或者在可视化工具（如 Navicat、DBeaver）中，展开对应数据库下的 Triggers 目录查看。

✅ 总结