MySQL 学习笔记 1：存储过程

MySQL 学习笔记 1：存储过程

图源：ubiq.co

存储过程可以看做是将一组 SQL打包执行，并返回最终的执行结果。

其优点是：因为存储过程中的 SQL 是同时一次执行，所以没有数据一致性的问题。其次，不需要由客户端多次传输 SQL 命令，而只需要告诉 MySQL 执行某某存储过程，所以性能上比执行同样内容的多条 SQL 是要更好的。最后，使用存储过程，即使某些表结构发生变化，只要存储过程返回的结果和接收参数不发生改变，相应的调用代码也无需改变。

本文介绍如何创建和使用 MySQL 的存储过程。

本文使用可视化数据库连接工具 SQLyog 连接数据库操作，该工具的下载安装可以通过这里。当然，这并非必须。使用其他客户端（比如命令行客户端）都可以完成文中的相关 SQL 执行。

本文用于测试的表结构如下：

CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `average_score` int NOT NULL,
  `level` enum('FRESH_MAN','JUNIOR','SENIOR','SOPHOMORE') NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_7pb8owoegbhhcrpopw4o1ykcr` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

填充的测试数据类似这样：

mysql> select * from student limit 10;
+-----+---------------+-----------+----------+
| id  | average_score | level     | name     |
+-----+---------------+-----------+----------+
| 190 |             1 | JUNIOR    | Tom      |
| 191 |            14 | FRESH_MAN | icexmoon |
| 192 |             2 | SOPHOMORE | BrusLee  |
| 193 |            28 | SOPHOMORE | Harry    |
| 194 |            44 | JUNIOR    | JackChen |
| 195 |             4 | SENIOR    | Jimmy    |
| 196 |            22 | FRESH_MAN | LiLei    |
| 197 |            22 | JUNIOR    | XiaoMing |
| 198 |            74 | FRESH_MAN | Adam     |
| 199 |            73 | FRESH_MAN | Alex     |
+-----+---------------+-----------+----------+

表结构创建和测试数据填充都由 JPA 和测试用例完成，如果需要使用类似的方式实现，可以参考完整示例代码。

创建

选定要添加存储过程的数据库，右击存储过程，点击创建存储过程。之后需要输入存储过程名称，并确认。

此时会自动打开一个新的查询窗口，内容大概如下：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `jpa`.`showScoreAvg`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN

	END$$

DELIMITER ;

这里的内容实际上是 SQLyog 为我们提供的一个存储过程创建模版，需要我们填写实际的存储过程内容后运行才能真正创建存储过程。

DELIMITER $$语句可以改写 MySQL 的默认 SQL 结束标记（;）为指定字符，这是因为我们在存储过程中往往要编写多条 SQL，而这些 SQL 的结束标识都是;，而;本身又是 SQL 的结束标识，在存储过程创建语句中同样会发挥作用，这就会导致存储过程的创建语句半中被结束，必然会运行失败。

所以存储过程创建语句中通常都会用DELIMITER先指定一个不常见的终止符（比如$$）以能让存储过程中的 SQL 结束符;被正常包含，最后再使用DELIMITER ;语句将终止符替换为正常的;。

之后的CREATE PROCEDURE jpa.showScoreAvg() 表示这是为jpa这个库创建的名为showScoreAvg的存储过程。存储过程本身可以定义多个输入或输出参数，这里没有任何参数，所以参数列表()是空。

存储过程的内容被包含在BEGIN和END$$之间，比如：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `jpa`.`showScoreAvg`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		SELECT AVG(average_score)
		FROM student;
	END$$

DELIMITER ;

执行上边的 SQL 后会显示：

1 queries executed, 1 success, 0 errors, 0 warnings

查询：CREATE PROCEDURE `jpa`.`showScoreAvg`() BEGIN select avg(average_score) from student; END

共 0 行受到影响

执行耗时   : 0.010 sec
传送时间   : 1.007 sec
总耗时      : 1.018 sec

这说明存储过程已经创建好了。

查看

也可以通过 SQL 查询数据库中已有的存储过程：

show procedure status;

这条语句会显示所有的存储过程，可以使用 LIKE 对结果进行过滤：

show procedure status like 'show%'

如果要查看存储过程的创建语句，可以使用以下 SQL：

show create procedure showScoreAvg;

执行

执行存储过程：

call showScoreAvg();

也可以带上库名：

call jpa