如何用MySQL触发器或函数高效获取用户钱包余额？

Hey there! Great question—you're absolutely right that recalculating a user's balance by scanning all their transactions every time can lead to unnecessary load as your wallet table grows. Let's break down two reliable approaches to solve this: an optimized function for real-time calculation, and a trigger-based balance table for lightning-fast lookups.

Approach 1: Create a Reusable Balance Calculation Function #

This approach keeps things simple by encapsulating the balance logic in a function, and we'll optimize it with indexes to avoid full-table scans.

Step 1: Add an Index (Critical for Performance) #

First, add a composite index to speed up filtering transactions by user and type:

CREATE INDEX idx_wallet_user_type ON wallet(user_id, type);

Step 2: Create the Balance Function #

This function takes a user_id as input and returns the current balance by summing credits (additions) and subtracting debits (deductions):

DELIMITER //
CREATE FUNCTION get_user_balance(p_user_id INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE user_balance INT;
    SELECT SUM(
        CASE 
            WHEN type = 'CREDIT' THEN amount 
            ELSE -amount 
        END
    ) INTO user_balance
    FROM wallet
    WHERE user_id = p_user_id;
    
    -- Handle NULL if user has no transactions
    RETURN COALESCE(user_balance, 0);
END //
DELIMITER ;

How to Use It #

To get a user's balance, just call the function:

SELECT get_user_balance(1); -- Returns -1110 for your sample data

Pros & Cons #

• ✅ No extra tables to maintain; data is always consistent with transactions
• ✅ Works well for moderate transaction volumes (thanks to the index)
• ❌ Still calculates the balance on each call (though indexed queries are fast)

Approach 2: Trigger-Based Balance Table (For High Performance) #

If you have a high-traffic system with frequent balance queries, maintaining a dedicated balance table is the way to go. Triggers will automatically update the balance whenever transactions are added, updated, or deleted.

Step 1: Create the Balance Table #

This table stores the current balance for each user:

CREATE TABLE wallet_balances (
    user_id INT NOT NULL PRIMARY KEY,
    balance INT NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES wallet(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 2: Initialize Existing Balances #

Populate the table with current balances from your wallet data:

INSERT INTO wallet_balances (user_id, balance)
SELECT user_id, SUM(
    CASE 
        WHEN type = 'CREDIT' THEN amount 
        ELSE -amount 
    END
) AS balance
FROM wallet
GROUP BY user_id;

Step 3: Create Triggers to Auto-Update Balances #

We need three triggers to handle INSERT, UPDATE, and DELETE operations on the wallet table:

Trigger for INSERT

DELIMITER //
CREATE TRIGGER trg_wallet_insert_balance
AFTER INSERT ON wallet
FOR EACH ROW
BEGIN
    UPDATE wallet_balances
    SET balance = balance + 
        CASE 
            WHEN NEW.type = 'CREDIT' THEN NEW.amount 
            ELSE -NEW.amount 
        END
    WHERE user_id = NEW.user_id;
    
    -- If user doesn't exist in balances table (edge case), add them
    IF ROW_COUNT() = 0 THEN
        INSERT INTO wallet_balances (user_id, balance)
        VALUES (NEW.user_id, 
            CASE WHEN NEW.type = 'CREDIT' THEN NEW.amount ELSE -NEW.amount END
        );
    END IF;
END //
DELIMITER ;

Trigger for UPDATE

DELIMITER //
CREATE TRIGGER trg_wallet_update_balance
AFTER UPDATE ON wallet
FOR EACH ROW
BEGIN
    -- Reverse the old transaction's effect, then apply the new one
    UPDATE wallet_balances
    SET balance = balance 
        - CASE WHEN OLD.type = 'CREDIT' THEN OLD.amount ELSE -OLD.amount END
        + CASE WHEN NEW.type = 'CREDIT' THEN NEW.amount ELSE -NEW.amount END
    WHERE user_id = NEW.user_id;
END //
DELIMITER ;

Trigger for DELETE

DELIMITER //
CREATE TRIGGER trg_wallet_delete_balance
AFTER DELETE ON wallet
FOR EACH ROW
BEGIN
    -- Reverse the deleted transaction's effect
    UPDATE wallet_balances
    SET balance = balance 
        - CASE WHEN OLD.type = 'CREDIT' THEN OLD.amount ELSE -OLD.amount END
    WHERE user_id = OLD.user_id;
END //
DELIMITER ;

How to Use It #

Now getting a user's balance is a simple, fast lookup:

SELECT balance FROM wallet_balances WHERE user_id = 1; -- Returns -1110 instantly

Pros & Cons #

• ✅ Blazing-fast balance queries (no calculation needed)
• ✅ Ideal for high-concurrency systems
• ❌ Requires maintaining triggers and an extra table
• ❌ Need to handle edge cases (like users with no transactions) to avoid data inconsistencies

Verification #

For your sample data, the expected balance for user 1 is:

• Total credits: 50 + 50 = 100
• Total debits: 100*11 + 110 = 1210
• Final balance: 100 - 1210 = -1110

Both approaches should return this value when tested.

内容的提问来源于stack exchange，提问作者arun VM