How to implement transaction processing statements in MySQL?

How to implement transaction processing statements in MySQL?

In database operations, transaction processing is a very important part. It can ensure that a series of operations are either all executed or not executed at all. Transaction processing in MySQL can be implemented through the following sample code.

First, let us create a sample table to demonstrate the transaction processing statement:

CREATE TABLE bank_account (
    id INT PRIMARY KEY,
    account_number VARCHAR(20),
    balance DECIMAL(10, 2)
);

Next, we will demonstrate how to use the transaction processing statement in MySQL to perform transfer operations and ensure that the transfer operation Either all are executed successfully, or none are executed.

START TRANSACTION;

-- 查询转出账户余额
SELECT balance INTO @balance_out
FROM bank_account
WHERE id = 1;

-- 查询转入账户余额
SELECT balance INTO @balance_in
FROM bank_account
WHERE id = 2;

IF @balance_out >= 100.00 THEN
    -- 更新转出账户余额
    UPDATE bank_account
    SET balance = @balance_out - 100.00
    WHERE id = 1;

    -- 更新转入账户余额
    UPDATE bank_account
    SET balance = @balance_in + 100.00
    WHERE id = 2;

    COMMIT;
    SELECT '转账成功';
ELSE
    ROLLBACK;
    SELECT '转账失败';
END IF;

In the above example, first we use START TRANSACTION to open a transaction. Then, we query the balance of the transfer-out account and the transfer-in account, and use conditional judgment to determine whether the transfer operation can be performed. If the transfer can be performed, the balances of the transfer-out account and the transfer-in account are updated respectively, and the transaction is committed. If the transfer cannot be performed, the transaction is rolled back.

Through the above example, we can see how to use transaction processing statements in MySQL to ensure that a series of operations are either all executed or none are executed. This ensures data integrity and consistency.

The above is the detailed content of How to implement transaction processing statements in MySQL?. For more information, please follow other related articles on the PHP Chinese website!