如何在MySQL存储过程中检查COUNT(*)并实现事务回滚校验

需求说明 #

在提交数据库变更前，需要校验指定SQL语句返回的记录数（或count查询的数值）是否在指定范围内，若不在则回滚事务。调用格式如下：

CALL my_database.checkSQLCount(
    "SELECT count(*) FROM my_table WHERE field1='xxx'",
    30,
    35
);

现有代码问题 #

自行编写的checkSQLCount存储过程存在两个问题：

• 传入SELECT * FROM my_table WHERE field1='xxx'时，会直接返回所有查询到的行，不符合预期；
• 传入SELECT count(*) FROM my_table WHERE field1='xxx'时，获取到的rowCount为1（结果集行数），而非实际统计的记录数。

修正后的存储过程 #

1. 核心校验存储过程checkSQLCount #

DELIMITER //

DROP PROCEDURE IF EXISTS my_database.checkSQLCount //
CREATE PROCEDURE my_database.checkSQLCount(IN countQuery VARCHAR(1000), IN minCount INT, IN maxCount INT)
BEGIN
DECLARE success INT;
DECLARE rowCount INT;
DECLARE result_val INT;

-- 打印当前执行的SQL语句
SELECT countQuery AS 'SQL statement:';

-- 创建临时表存储查询结果，避免直接输出原查询内容
DROP TEMPORARY TABLE IF EXISTS temp_query_result;
SET @create_temp_sql := CONCAT('CREATE TEMPORARY TABLE temp_query_result AS ', countQuery);
PREPARE stmt FROM @create_temp_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 获取临时表的行数
SELECT COUNT(*) INTO rowCount FROM temp_query_result;

-- 若结果集仅一行一列（如count查询），则取实际统计值替换行数
IF rowCount = 1 THEN
  SELECT * INTO result_val FROM temp_query_result;
  SET rowCount = result_val;
END IF;

-- 清理临时表
DROP TEMPORARY TABLE temp_query_result;

-- 判断记录数是否在指定范围内
IF rowCount < minCount OR rowCount > maxCount THEN
  SELECT CONCAT('ERROR: ', rowCount, ' not between ', minCount, ' and ', maxCount)
    AS 'Check expected nr matched rows';
  SET success := 0;
ELSE
  SELECT CONCAT('OK: ', rowCount, ' is between ', minCount, ' and ', maxCount)
    AS 'Check expected nr matched rows';
  SET success := 1;
END IF;

-- 将校验结果存入CallResults表
INSERT INTO CallResults (success) VALUES (success);
END //

DELIMITER ;

2. 事务处理存储过程handleCallResults（复用原有代码） #

DELIMITER //

DROP PROCEDURE IF EXISTS my_database.handleCallResults //
CREATE PROCEDURE my_database.handleCallResults()
BEGIN
-- 检查是否有校验失败的记录，有则回滚事务
IF (SELECT COUNT(*) FROM CallResults WHERE success = 0) > 0 THEN
  ROLLBACK;
  SELECT 'Transaction rolled back' AS '=== Data patch end result ===';
ELSE
  -- 无失败则提交事务
  COMMIT;
  SELECT 'Transaction committed' AS '=== Data patch end result ===';
END IF;
END //

DELIMITER ;

使用示例 #

-- 开启事务
START TRANSACTION;

-- 校验普通查询的返回行数是否在30-35之间
CALL my_database.checkSQLCount(
    "SELECT * FROM my_table WHERE field1='xxx'",
    30,
    35
);

-- 或者校验count查询的统计值是否在30-35之间
CALL my_database.checkSQLCount(
    "SELECT count(*) FROM my_table WHERE field1='xxx'",
    30,
    35
);

-- 根据校验结果决定提交或回滚
CALL my_database.handleCallResults();

修正说明 #

• 使用临时表存储查询结果，避免直接输出原查询的行数据；
• 区分普通查询和count查询：若结果集仅一行一列，则取实际统计值作为校验依据；
• 正确获取校验所需的数值，替代原代码中FOUND_ROWS()的错误用法。

内容的提问来源于stack exchange，提问作者Walter A