mysql数据统计表

-- 示例更新脚本
DELIMITER //
CREATE PROCEDURE UpdateStats()
BEGIN
    -- 删除旧数据
    DELETE FROM stats_table;
    
    -- 插入新数据
    INSERT INTO stats_table (date, total_sales)
    SELECT DATE(order_date) AS date, SUM(amount) AS total_sales
    FROM orders
    GROUP BY DATE(order_date);
END //
DELIMITER ;

-- 定期调用更新脚本
CALL UpdateStats();

-- 示例分区表
CREATE TABLE stats_table (
    date DATE,
    total_sales DECIMAL(10, 2)
)
PARTITION BY RANGE (TO_DAYS(date)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-01-01')),
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 示例事务更新
START TRANSACTION;
DELETE FROM stats_table;
INSERT INTO stats_table (date, total_sales)
SELECT DATE(order_date) AS date, SUM(amount) AS total_sales
FROM orders
GROUP BY DATE(order_date);
COMMIT;