偶遇mysql8存储过程中变量命名不当导致的bug

因需要对旧数据库的数据进行迁移,某表某字段原先是多个值的逗号拼接,要拆分到另外一个表中以多行记录的方式保存.

用存储过程来处理, 就遇到了这么个问题

与SQLSERVER的局部变量前加@符号不同, mysql变量前面可以不加任何符号, 这可能会导致mysql分不清是字段还是变量

演示代码如下, 需要对字符串类型的字段title进行处理, 局部变量也是命名为title.

USE `test`;
DROP TABLE IF EXISTS `infos`;
CREATE TABLE `infos`(
    `info_id` int NOT NULL,
    `title` varchar(2000) COMMENT '多个字符串的逗号拼接',
    PRIMARY KEY(`info_id`)
) ENGINE = InnoDB;
INSERT INTO `infos`(`info_id`, `title`)
VALUES(1, 'aben,sky');

DROP PROCEDURE IF EXISTS `mysp_a`;
DELIMITER $$
CREATE PROCEDURE `mysp_a`()
BEGIN
    # 定义变量
    DECLARE done INT DEFAULT 0;
    DECLARE id INT; #注意: 这里也不能用info_id, 否则下面的sql分不清是变量还是字段, 根本不会返回数据(或许是因为执行错误但是又没有抛出错误)
    DECLARE title VARCHAR(2000);
    # 定义cursor
    DECLARE cursor1 CURSOR FOR
    SELECT `info_id`, `title` FROM `infos` WHERE `info_id` = 1;
    -- 游标中的内容执行完后将done设置为true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cursor1;
    FETCH cursor1 INTO id, title;
    WHILE done = 0 DO
        SELECT id AS `info_id`, title AS `title`; #这里只是简单演示, 看字段值能否提取出来.
        FETCH cursor1 INTO id, title;
    END WHILE;
    CLOSE cursor1;
END$$
DELIMITER ;

执行结果就是, int类型的id能获取, 但是varchar类型的title是null:

mysql> CALL `mysp_a`();
+---------+-------+
| info_id | title |
+---------+-------+
|       1 | NULL  |
+---------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

修正方法很简单, 只要把这个varchar类型的变量名称改掉就行, 比如: $title, title2等等都可以的, 本人建议前面加$符号

可以这样改:

DROP PROCEDURE IF EXISTS `mysp_a`;
DELIMITER $$
CREATE PROCEDURE `mysp_a`()
BEGIN
    # 定义变量
    DECLARE done INT DEFAULT 0;
    DECLARE $info_id INT; #变量名前加前缀
    DECLARE $title VARCHAR(2000); #变量名前加前缀
    # 定义cursor
    DECLARE cursor1 CURSOR FOR
    SELECT `info_id`, `title` FROM `infos` WHERE `info_id` = 1;
    -- 游标中的内容执行完后将done设置为true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cursor1;
    FETCH cursor1 INTO $info_id, $title;
    WHILE done = 0 DO
        SELECT $info_id AS `info_id`, $title AS `title`; #这里只是简单演示, 看字段值能否提取出来.
        FETCH cursor1 INTO $info_id, $title;
    END WHILE;
    CLOSE cursor1;
END$$
DELIMITER ;

mysql变量名前面可以加$符号, 但是不支持MSSQL的@