如何自动更新用户创建的累算余额列？

1. 重构表结构（推荐方案） #

动态添加列的设计会导致表结构膨胀，违反数据库范式，后续维护成本极高。更合理的做法是将宽表改为窄表，用行存储每日数据，再通过视图计算累计余额：

步骤1：创建交易明细表 #

CREATE TABLE daily_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    day_number INT NOT NULL, -- 对应原day1、day2...
    amt DECIMAL(18,2) NOT NULL,
    UNIQUE KEY idx_user_day (user_id, day_number) -- 避免同一用户同一天重复数据
);

步骤2：创建计算累计余额的视图 #

CREATE VIEW user_daily_balances AS
SELECT 
    user_id,
    day_number,
    amt,
    SUM(amt) OVER (PARTITION BY user_id ORDER BY day_number) AS bal_up_to_day
FROM daily_transactions;

优势 #

• 无需维护bal_up_to_day列，更新amt后视图自动返回最新累计余额
• 支持任意天数扩展，无需动态添加列
• 符合数据库设计规范，查询和维护更高效

2. 保留宽表结构的动态更新方案 #

如果因业务限制必须保留原有宽表结构，可以通过存储过程+动态SQL实现自动更新：

步骤1：创建更新余额的存储过程 #

DELIMITER //
CREATE PROCEDURE refresh_user_balances(IN p_user_id INT, IN p_start_day INT)
BEGIN
    DECLARE max_day INT;
    DECLARE current_day INT;
    DECLARE prev_bal DECIMAL(18,2);
    DECLARE current_amt DECIMAL(18,2);
    
    -- 获取当前表中最大的day编号
    SELECT MAX(CAST(SUBSTRING(column_name, 7) AS UNSIGNED)) INTO max_day
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'your_table_name' -- 替换为你的表名
      AND column_name LIKE 'amt_day%';
    
    -- 初始化起始余额
    IF p_start_day = 1 THEN
        SELECT amt_day1 INTO prev_bal FROM your_table_name WHERE user_id = p_user_id;
        UPDATE your_table_name SET bal_up_to_day1 = prev_bal WHERE user_id = p_user_id;
        SET current_day = 2;
    ELSE
        -- 动态获取上一天的余额
        SET @prev_bal_col = CONCAT('bal_up_to_day', p_start_day - 1);
        SET @sql_get_prev = CONCAT('SELECT ', @prev_bal_col, ' INTO prev_bal FROM your_table_name WHERE user_id = ', p_user_id);
        PREPARE stmt_get_prev FROM @sql_get_prev;
        EXECUTE stmt_get_prev;
        DEALLOCATE PREPARE stmt_get_prev;
        SET current_day = p_start_day;
    END IF;
    
    -- 循环更新后续所有余额列
    WHILE current_day <= max_day DO
        -- 获取当日金额
        SET @amt_col = CONCAT('amt_day', current_day);
        SET @sql_get_amt = CONCAT('SELECT ', @amt_col, ' INTO current_amt FROM your_table_name WHERE user_id = ', p_user_id);
        PREPARE stmt_get_amt FROM @sql_get_amt;
        EXECUTE stmt_get_amt;
        DEALLOCATE PREPARE stmt_get_amt;
        
        -- 计算并更新当日余额
        SET prev_bal = prev_bal + COALESCE(current_amt, 0);
        SET @bal_col = CONCAT('bal_up_to_day', current_day);
        SET @sql_update_bal = CONCAT('UPDATE your_table_name SET ', @bal_col, ' = ', prev_bal, ' WHERE user_id = ', p_user_id);
        PREPARE stmt_update_bal FROM @sql_update_bal;
        EXECUTE stmt_update_bal;
        DEALLOCATE PREPARE stmt_update_bal;
        
        SET current_day = current_day + 1;
    END WHILE;
END //
DELIMITER ;

步骤2：在Python中触发更新 #

当用户修改amt_dayN后，直接调用存储过程：

import mysql.connector

def on_amt_updated(user_id, updated_day):
    conn = mysql.connector.connect(
        host='your_host',
        user='your_user',
        password='your_pass',
        database='your_db'
    )
    cursor = conn.cursor()
    cursor.callproc('refresh_user_balances', (user_id, updated_day))
    conn.commit()
    cursor.close()
    conn.close()

可选：自动创建触发器 #

如果希望数据库层面自动触发更新，可以在Python创建新列时，动态生成触发器：

def create_amt_trigger(day_number):
    conn = mysql.connector.connect(...)
    cursor = conn.cursor()
    trigger_sql = f"""
    DELIMITER //
    CREATE TRIGGER trg_amt_day{day_number}_update AFTER UPDATE ON your_table_name
    FOR EACH ROW
    BEGIN
        IF NEW.amt_day{day_number} <> OLD.amt_day{day_number} THEN
            CALL refresh_user_balances(NEW.user_id, {day_number});
        END IF;
    END //
    DELIMITER ;
    """
    cursor.execute(trigger_sql)
    conn.commit()
    cursor.close()
    conn.close()

3. 应用层直接计算更新 #

如果数据库层面的动态SQL不好实现，也可以在Python中直接读取数据、计算余额后批量更新：

import pandas as pd
import mysql.connector

def update_balances(user_id, updated_day):
    conn = mysql.connector.connect(...)
    cursor = conn.cursor()
    
    # 获取所有amt列并按day排序
    cursor.execute("""
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = DATABASE() 
          AND table_name = 'your_table_name' 
          AND column_name LIKE 'amt_day%'
        ORDER BY CAST(SUBSTRING(column_name, 7) AS UNSIGNED)
    """)
    amt_cols = [col[0] for col in cursor.fetchall()]
    max_day = int(amt_cols[-1].split('_')[2])
    
    # 读取用户的所有amt值
    cursor.execute(f"SELECT {', '.join(amt_cols)} FROM your_table_name WHERE user_id = %s", (user_id,))
    amt_values = cursor.fetchone()
    
    # 计算累计余额
    bal_values = []
    current_bal = 0
    for amt in amt_values:
        current_bal += amt or 0
        bal_values.append(current_bal)
    
    # 生成更新语句
    update_parts = []
    for day in range(updated_day, max_day + 1):
        bal_col = f'bal_up_to_day{day}'
        update_parts.append(f"{bal_col} = {bal_values[day-1]}")
    
    if update_parts:
        update_sql = f"UPDATE your_table_name SET {', '.join(update_parts)} WHERE user_id = %s"
        cursor.execute(update_sql, (user_id,))
        conn.commit()
    
    cursor.close()
    conn.close()

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