记一次存储过程bug记录

为了快速清空测试数据，把几条删除语句换成了存储过程，如下所示：

CREATE DEFINER=`deme`@`%` PROCEDURE `delete_user_data`(IN address VARCHAR(255))
BEGIN
    DELETE FROM t_user_bind WHERE uid IN (SELECT id FROM t_user WHERE address = address);
    DELETE FROM t_x_log WHERE uid IN (SELECT id FROM t_user WHERE address = address);
    DELETE FROM t_pipeline WHERE uid IN (SELECT id FROM t_user WHERE address = address);
END

提示成功但是发现表的数据被清空了。

 找了半天才知道存储过程的入参不能与where中列名相同，不然会发生意想不到的错误。。。于是修改入参为addr。

CREATE DEFINER=`deme`@`%` PROCEDURE `delete_user_data`(IN addr VARCHAR(255))
BEGIN
    DELETE FROM t_user_bind WHERE uid IN (SELECT id FROM t_user WHERE address = addr);
    DELETE FROM t_x_log WHERE uid IN (SELECT id FROM t_user WHERE address = addr);
    DELETE FROM t_pipeline WHERE uid IN (SELECT id FROM t_user WHERE address = addr);
END

结果运行报错：Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

查了资料后，说是两个字段的排序规则不一致，采用一种方法优化后：

CREATE DEFINER=`deme`@`%` PROCEDURE `delete_user_data`(IN addr VARCHAR(255))
BEGINDECLARE userID bigint;
    SELECT id INTO userId from t_user WHERE address = addr COLLATE utf8mb4_0900_ai_ci ;
    DELETE from t_user_bind WHERE uid = userId;
    DELETE from t_pipeline WHERE uid = userId;
    DELETE from t_x_log WHERE uid = userId;
END

 运行成功，虽然后面查看了各字段的排序规则，感觉没有差别，而且在运行窗口是没问题，就放在存储过程中一起就有问题，害，注意点真多