代码之家 › 专栏 › 技术社区 › Walter A

如何在mysql过程中检查计数(*)?

mysql

Walter A · 技术社区 · 8 月前

在使用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);

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

调用my_database.checkSQLCount(
“从my_table WHERE field1='xxx'中选择计数(*)”,
30,
35);

结果是我找到了一条记录(包含计数)

1 回复 | 直到 8 月前

Walter A 8 月前

您可以在语句中使用一个变量,然后再使用它:

SELECT count(*) FROM my_table WHERE field1='xxx' into @rowCount

Walter A编辑: 在进行了一些额外的更改后,这项功能发挥了作用。我的最终解决方案看起来像

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 ===';

  SET @stmt = CONCAT(countQuery, ' INTO @rowCount');

  PREPARE stmt FROM @stmt;
  EXECUTE stmt ;
  DEALLOCATE PREPARE stmt;

  -- 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 myDatabase.CallResults (success) VALUES (success);
END //

通过此过程,以及 handleCallResults 在这个问题上,我可以写

-- Start transaction
START TRANSACTION;

-- Create a temporary table to store the results of each SQL call, always needed
CREATE TEMPORARY TABLE IF NOT EXISTS bahman.CallResults (success INT);

CALL myDatabase.checkAffectedRows(
  "UPDATE myDatabase.myTable SET currency='xxx' WHERE id < 100",
   1,100);

CALL myDatabase.checkSQLCount(
  "SELECT count(*) FROM myDatabase.myTable WHERE currency='yyy'",
  30, 35);

-- Handle the results and decide whether to commit or rollback the transaction
CALL myDatabase.handleCallResults();