存储过程参数用于DELETE查询时误删全表问题排查

我需要创建存储过程删除多张表中指定sapid的记录，过程中遇到两个异常情况：

1. 初始版本调用CALL delete_sap("6moo...Vwm")时，目标表所有记录被误删且无警告
2. 修改为指定表名前缀的版本后，无记录被删除也无报错

初始存储过程代码 #

CREATE OR REPLACE PROCEDURE `delete_sap`(sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    START TRANSACTION;
    SELECT sap_to_delete as ' '; -- 验证参数是否传入
    DELETE FROM data01 WHERE sapid = sap_to_delete;
    DELETE FROM sap_has_owners WHERE sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_reviewers WHERE sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_readonly WHERE sap_sapid=sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_editors WHERE sap_sapid=sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM page WHERE sapid=sap_to_delete;
    DELETE FROM element WHERE element.sapid=sap_to_delete;
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END//

DELIMITER ;

修改后的存储过程代码 #

DELIMITER //

CREATE OR REPLACE PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    START TRANSACTION;
    SELECT sap_to_delete as ' ';
    DELETE FROM data01 WHERE data01.sapid = sap_to_delete;
    DELETE FROM sap_has_owners WHERE sap_has_owners.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_reviewers WHERE sap_has_reviewers.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_readonly WHERE sap_has_readonly.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_editors WHERE sap_has_editors.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM page WHERE page.sapid = sap_to_delete;
    DELETE FROM element WHERE element.sapid = sap_to_delete;
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END//

DELIMITER ;

data01表结构及数据示例 #

表结构 #

DROP TABLE IF EXISTS `data01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `data01` (
  `sapid` char(10) NOT NULL,
  `pageid` char(10) NOT NULL,
  `elemid` char(10) NOT NULL,
  `json` longtext DEFAULT NULL,
  PRIMARY KEY (`sapid`,`pageid`,`elemid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

数据示例 #

通过SHOW CREATE PROCEDURE delete_sap查看发现存储过程的collation_connection为utf8mb3_general_ci，请问：

1. 该排序规则是否会引发上述问题？
2. 如何修改存储过程的排序规则？

一、utf8mb3_general_ci是否会引发问题？ #

是的，排序规则不匹配是导致这两个异常的核心原因：

1. 误删全表的原因：
   存储过程参数sap_to_delete的排序规则继承自创建时的collation_connection（即utf8mb3_general_ci），而表中sapid字段的排序规则是utf8mb4_unicode_ci。当执行sapid = sap_to_delete时，MySQL会进行隐式排序规则转换，部分字符在不同规则下的比较逻辑异常，导致所有行被判定为匹配，触发全表删除。

2. 修改后无记录删除的原因：
   修改后的代码虽然指定了表前缀，但参数的排序规则仍为utf8mb3_general_ci，与表字段的utf8mb4_unicode_ci不匹配。此时参数与字段的比较因规则差异无法匹配到任何记录，且代码中的CONTINUE HANDLER仅捕获异常，不会处理排序规则不匹配的隐性警告，最终表现为无报错也无操作。

二、如何修改存储过程的排序规则？ #

方法1：创建时显式指定参数的字符集和排序规则 #

在创建存储过程前，先设置会话级别的字符集规则，再给参数显式指定匹配表字段的属性：

-- 先设置会话字符集和排序规则，确保存储过程继承正确属性
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建存储过程，给参数指定与表字段一致的字符集和排序规则
DELIMITER //
CREATE OR REPLACE PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci)
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    START TRANSACTION;
    -- 验证参数传入情况
    SELECT sap_to_delete AS '传入的sapid';
    
    -- 无需手动加COLLATE，参数与字段规则一致可直接比较
    DELETE FROM data01 WHERE data01.sapid = sap_to_delete;
    DELETE FROM sap_has_owners WHERE sap_has_owners.sap_sapid = sap_to_delete;
    DELETE FROM sap_has_reviewers WHERE sap_has_reviewers.sap_sapid = sap_to_delete;
    DELETE FROM sap_has_readonly WHERE sap_has_readonly.sap_sapid = sap_to_delete;
    DELETE FROM sap_has_editors WHERE sap_has_editors.sap_sapid = sap_to_delete;
    DELETE FROM page WHERE page.sapid = sap_to_delete;
    DELETE FROM element WHERE element.sapid = sap_to_delete;

    IF `_rollback` THEN
        ROLLBACK;
        SELECT '操作失败，已回滚' AS 结果;
    ELSE
        COMMIT;
        SELECT CONCAT('成功删除sapid为"', sap_to_delete, '"的记录') AS 结果;
    END IF;
END//
DELIMITER ;

方法2：修改已存在的存储过程 #

MySQL不支持直接修改存储过程的参数属性，只能通过重新创建的方式调整，步骤与方法1一致，核心是给参数显式指定utf8mb4_unicode_ci排序规则。

额外优化建议 #

• 添加删除行数验证：在每个DELETE后用ROW_COUNT()查看删除行数，方便排查问题，例如：

  DELETE FROM data01 WHERE data01.sapid = sap_to_delete;
  SELECT CONCAT('data01表删除行数：', ROW_COUNT()) AS 操作详情;
  

• 始终显式用IN定义输入参数，避免隐式参数引发的解析问题
• 测试边界场景：比如传入不存在的sapid、空值等，验证存储过程行为是否符合预期

内容的提问来源于stack exchange，提问作者S. Dale