记一次存储过程bug记录
2024-05-30 21:28 第二个卿老师 阅读(2) 评论(0) 编辑 收藏 举报为了快速清空测试数据,把几条删除语句换成了存储过程,如下所示:
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
运行成功,虽然后面查看了各字段的排序规则,感觉没有差别,而且在运行窗口是没问题,就放在存储过程中一起就有问题,害,注意点真多