当前位置：首页 > article >正文

【日常经验】Mysql中的某个存储过程中如果有查数据，存数据和删除数据，会自动在一个事务中吗

article 2024/11/23 2:51:52

在 MySQL
中，存储过程中的查数据（SELECT）、存数据（INSERT/UPDATE）和删除数据（DELETE）操作不会自动在一个事务中执行。默认情况下，MySQL
对每条单独的 SQL 语句都视为一个独立的事务（如果自动提交模式开启的话）。这意味着，如果存储过程中的某个 SQL
语句执行失败，之前已经执行成功的 SQL 语句并不会自动回滚。

为了确保存储过程中的多个操作保持原子性，即要么全部成功，要么全部失败，你需要在存储过程中显式地使用事务控制语句。这通常包括以下几个步骤：

关闭自动提交：在存储过程的开始，你可以使用 SET AUTOCOMMIT = 0; 语句来关闭自动提交模式。这样，MySQL 就不会在每条 SQL 语句执行后自动提交事务。
开始事务：使用 START TRANSACTION; 或 BEGIN; 语句来显式地开始一个事务。这标志着事务的起点。
执行 SQL 操作：在事务中执行你需要的 SQL 操作，包括查询、插入和删除等。
提交或回滚事务：
如果所有操作都成功执行，使用 COMMIT; 语句来提交事务，使更改永久保存到数据库中。
如果发生错误或异常，使用 ROLLBACK; 语句来回滚事务，撤销所有已经执行的更改。
恢复自动提交（可选）：在存储过程的结尾，你可以使用 SET AUTOCOMMIT = 1; 语句来恢复自动提交模式，但这通常不是必需的，因为存储过程结束后，其上下文也会被销毁。
下面是一个示例存储过程，展示了如何使用事务控制语句来确保多个操作的原子性：

DELIMITER //
 
CREATE PROCEDURE MyAtomicStoredProcedure()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 在发生异常时回滚事务
        ROLLBACK;
    END;
 
    -- 关闭自动提交
    SET AUTOCOMMIT = 0;
 
    -- 开始事务
    START TRANSACTION;
 
    -- 查询数据（通常不需要回滚，但这里为了完整性展示）
    SELECT * FROM my_table WHERE some_column = 'some_value';
 
    -- 插入数据
    INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2');
 
    -- 删除数据
    DELETE FROM my_table WHERE another_column = 'another_value';
 
    -- 提交事务（如果没有异常发生）
    COMMIT;
 
    -- 注意：在存储过程结束时，自动提交模式通常不需要显式恢复，因为存储过程上下文会被销毁
    -- 但为了清晰起见，你也可以在这里加上 SET AUTOCOMMIT = 1;
END //
 
DELIMITER ;