MySQL 存储过程编写指南

引言

因标签过多，在实际的应用过程中，对标签表的结构进行了变更。

从过去的标签随意选择，现在需要对标签进行分类，简化选择难度。添加科目分类之后，需要对历史上已经被使用过的标签添加科目信息，进行数据的迁移工作。

在数据迁移时，使用到了存储过程，遇到了诸多问题，特此记录，分享爬坑过程。

存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

通俗来说，存储过程就是数据库中被编译的SQL函数，和普通函数一样有函数体，有顺序结构、条件结构、循环结构，有参数，有输入输出。

参考文献

请先阅读以下两篇文章，本文在此基础上对某些细节做了补充。

MYSQL如何对数据进行自动化升级--以如果某数据表存在并且某字段不存在时则执行更新操作为例 - 河北工业大学梦云智软件开发团队

数据库编程实战 - 鲸冬香

标识符替换

因定义存储过程时;遇到分号会进行语句的执行，需要对;进行转义。

-- 标识符替换
DELIMITER $$

-- 存储过程定义

-- 重新定义标识符
DELIMITER ;

定义存储过程

-- 存储过程
CREATE PROCEDURE procedure_name(IN param BIGINT)
BEGIN

  -- 存储过程具体实现

END$$

游标示例

请重点注意以下示例代码中的顺序问题，一定是先声明变量，再声明游标，再处理，否则MySQL会抛错误。

-- 声明变量
DECLARE tag_id BIGINT;
DECLARE done INT DEFAULT FALSE;
-- 声明游标
DECLARE cur CURSOR FOR SELECT `id` FROM `tag`;
-- 声明 not found 处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
FETCH cur INTO tag_id;

WHILE NOT done DO
  -- 遍历标签表 逐条迁移数据
  CALL migrate_tag_info_by_id(tag_id);
  FETCH cur INTO tag_id;
END WHILE;

HANDLER

需要重点注意以下代码：

-- 声明 not found 处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

去查阅相关博客，声明NOT FOUND的处理器是最常用的游标遍历方式。

The DECLARE ... HANDLER statement specifies a handler that deals with one or more conditions. If one of these conditions occurs, the specified statement executes. statement can be a simple statement such as SET var_name = value , or a compound statement written using BEGIN and END.

DECLARE ... HANDLER 语句指定了处理单条件或多条件的处理器，如果条件满足时，指定的语句会被执行。语句可以像SET之类的简单语句，也可以是BGGIN ... END代码块。

所以当复杂业务场景下，游标的嵌套会出问题，这里我选择多存储过程的调用来解决。

需要嵌套的时候，可将内层的游标放到一个新的存储过程中，使用CALL进行调用。

WHILE NOT done DO
  -- 遍历标签表 逐条迁移数据
  CALL migrate_tag_info_by_id(tag_id);
  FETCH cur INTO tag_id;
END WHILE;

调试方法

编写存储过程最难的就是写了几十行代码的存储过程，执行的时候错误不是按行报的，而是一类通用的错误，非常难调试。

在查阅相关资料后，MySQL调试存储过程需要手动进行日志的打印。

思路如下：

建立logging表，编写一个名为debug的存储过程，功能就是将参数写入到logging表中。通过打日志的方式确定存储过程执行情况。

我这里遇到了需要查询SELECT语句的中间值的问题，解决思路如下：

根据相关的数据结构建立一张临时表，通过INSERT INTO SELECT的方式来进行存储过程执行过程中相关的中间值的追踪。

完整示例

完整示例代码如下：

-- 标识符替换
DELIMITER $$

-- 存储过程 根据标签 id 迁移试题对标签的引用数据
CREATE PROCEDURE migrate_subject_tag_ref_by_id(IN tag_id BIGINT)
BEGIN

  DECLARE tag_name VARCHAR(255);
  DECLARE subject_id BIGINT;
  DECLARE course_id BIGINT;
  DECLARE tag_ref_id BIGINT;
  DECLARE done INT DEFAULT FALSE;

  DECLARE cur CURSOR FOR
  SELECT `subject`.`id`, `subject`.`course_id`
  FROM `subject_tags`
  INNER JOIN `subject`
  ON `subject_tags`.`subjects_id` = `subject`.`id`
  WHERE `subject_tags`.`tags_id` = tag_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT `name` INTO tag_name FROM `tag` WHERE `id` = tag_id;

  OPEN cur;
  FETCH cur INTO subject_id, course_id;

  WHILE NOT done DO

    SELECT `id` INTO tag_ref_id FROM `tag`
    WHERE `tag`.`name` = tag_name AND `tag`.`course_id` = course_id;

    UPDATE `subject_tags` SET `tags_id` = tag_ref_id
    WHERE `subjects_id` = subject_id AND `tags_id` = tag_id;

    FETCH cur INTO subject_id, course_id;
  END WHILE;

END$$

-- 存储过程 根据标签 id 迁移新标签数据
CREATE PROCEDURE migrate_tag_info_by_id(IN tag_id BIGINT)
BEGIN

  DECLARE tag_name VARCHAR(255);
  DECLARE course_id BIGINT;
  DECLARE done INT DEFAULT FALSE;

  DECLARE cur CURSOR FOR
  SELECT `subject`.`course_id`
  FROM `subject_tags`
  INNER JOIN `subject`
  ON `subject_tags`.`subjects_id` = `subject`.`id`
  WHERE `subject_tags`.`tags_id` = tag_id
  GROUP BY `subject`.`course_id`;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT `name` INTO tag_name FROM `tag` WHERE `id` = tag_id;

  OPEN cur;
  FETCH cur INTO course_id;

  WHILE NOT done DO
    -- 创建新标签
    INSERT INTO `tag`(`name`, `course_id`) VALUES(tag_name, course_id);
    FETCH cur INTO course_id;
  END WHILE;

  -- 迁移对历史标签的引用
  CALL migrate_subject_tag_ref_by_id(tag_id);
  -- 删除历史标签
  DELETE FROM `tag` WHERE `tag`.`id` = tag_id;

END$$

-- 存储过程 迁移所有标签数据
CREATE PROCEDURE migrate_all_tag_info()
BEGIN

  DECLARE tag_id BIGINT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR SELECT `id` FROM `tag`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  FETCH cur INTO tag_id;

  WHILE NOT done DO
    -- 遍历标签表 逐条迁移数据
    CALL migrate_tag_info_by_id(tag_id);
    FETCH cur INTO tag_id;
  END WHILE;

END$$
-- 重新定义标识符
DELIMITER ;

CALL migrate_all_tag_info();

版权声明

本文作者：河北工业大学梦云智开发团队 - 张喜硕