Início / coding / Perguntas / 78478508
Accepted
Walter A
Asked: 2024-05-14 21:44:22 +0800 CST

Como posso verificar a contagem (*) em um procedimento mysql?

  • 772

Antes de confirmar alterações com um script mysql em um banco de dados, desejo realizar verificações adicionais. Quero reverter uma transação, quando a contagem de registros retornados por uma instrução sql não estiver entre os limites indicados na chamada. O procedimento deve ser chamado como

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

Fiz um procedimento semelhante para verificar o número de exclusões/atualizações/inserções:

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);

Eu tentei um procedimento como este:

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 //

Quando eu chamo isso com

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

a saída mostrará todas as linhas selecionadas, que não quero ver. E quando eu mudo para

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

o resultado é que encontrei um registro (contendo a contagem)

mysql

1 respostas

  1. Best Answer

    Você pode usar uma variável em sua instrução e depois usá-la:

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

    Editar por Walter A: Isso funcionou após algumas alterações adicionais. Minha solução final parece

    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 //

    Com este procedimento, assim como o handleCallResultsda pergunta, posso escrever

    -- 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();
    • 0

relate perguntas