主页 / user-3220113

Walter A's questions

Martin Hope
Walter A
Asked: 2024-05-14 21:44:22 +0800 CST
  • 5

在使用 mysql 脚本将更改提交到数据库之前，我想执行额外的检查。当 sql 语句返回的记录数不在调用中给出的限制之间时，我想回滚事务。该过程应该被称为

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

我做了一个类似的过程来检查删除/更新/插入的数量：

DELIMITER //
 
DROP PROCEDURE IF EXISTS my_database.checkAffectedRows;
CREATE PROCEDURE my_database.checkAffectedRows(IN sqlStatement VARCHAR(1000), IN minAffectedRows INT, IN maxAffectedRows INT)
BEGIN
DECLARE success INT;
DECLARE affectedRows INT;
 
SET @sql := sqlStatement;
-- Print current SQL statement
SELECT @sql AS 'SQL statement:';

-- Execute the provided SQL statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
SELECT ROW_COUNT() INTO affectedRows;

DEALLOCATE PREPARE stmt;
 
-- If the affected rows are outside the specified range, set the success flag to 0
IF affectedRows < minAffectedRows OR affectedRows > maxAffectedRows THEN
  SELECT CONCAT('ERROR: ', affectedRows, ' not between ',  minAffectedRows, ' and ', maxAffectedRows)
    AS 'Check expected nr updates';
  SET success := 0;
ELSE
  SELECT CONCAT('OK: ', affectedRows, ' is between ',  minAffectedRows, ' and ', maxAffectedRows)
    AS 'Check expected nr updates';
  SET success := 1;
END IF;
 
-- Insert the result into the CallResults table
INSERT INTO CallResults (success) VALUES (success);
END //
 
DROP PROCEDURE IF EXISTS my_database.handleCallResults;
CREATE PROCEDURE my_database.handleCallResults()
BEGIN
-- Check if any of the calls failed, if so, rollback the transaction
IF (SELECT COUNT(*) FROM CallResults WHERE success = 0) > 0 THEN
  ROLLBACK;
  SELECT 'Transaction rolled back' AS '=== Data patch end result ===';
ELSE
  -- No Rollback for above calls? Commit!
  COMMIT;
  SELECT 'Transaction committed' AS '=== Data patch end result ===';
END IF;
END //
 
DELIMITER ;
 
-- Start transaction
START TRANSACTION;
 
-- Update table but rollback when amount of updates is not between 1 and 100
CALL my_database.checkAffectedRows(
  "UPDATE myTable SET value='xxx' WHERE idMyTable < 100",
  1,100);

我尝试了这样的过程：

DROP PROCEDURE IF EXISTS myDatabase.checkSQLCount;
CREATE PROCEDURE myDatabase.checkSQLCount(IN countQuery VARCHAR(1000), IN minCount INT, IN maxCount INT)
BEGIN
DECLARE success INT;
DECLARE rowCount INT;
 
SELECT countQuery AS 'SQL statement:';

-- Execute the provided SQL statement without fetching results
SET @stmt = countQuery;
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
-- Retrieve the number of affected rows using FOUND_ROWS() or with ROW_COUNT()
SELECT FOUND_ROWS() INTO rowCount;
 
-- If the matched rows are outside the specified range, set the success flag to 0
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;

-- Insert the result into the CallResults table
INSERT INTO CallResults (success) VALUES (success);
END //

当我用

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

输出将显示所有选定的行，这是我不想看到的。当我把它变成

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

结果是我找到了一条记录（包含计数）

mysql