mysql自动数据统计

-- 示例：优化触发器逻辑
DELIMITER $$
CREATE TRIGGER after_insert_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 仅在订单金额大于1000时更新统计表
    IF NEW.amount > 1000 THEN
        UPDATE order_stats SET total_amount = total_amount + NEW.amount WHERE date = CURDATE();
    END IF;
END$$
DELIMITER ;

-- 示例：启用事件调度器并创建任务
SET GLOBAL event_scheduler = ON;

CREATE EVENT daily_order_stats
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    -- 每天凌晨1点更新订单统计
    UPDATE order_stats SET total_orders = (SELECT COUNT(*) FROM orders WHERE date = CURDATE());
END;

# 示例：使用Python连接MySQL数据库
import mysql.connector
from mysql.connector import pooling

db_config = {
    "host": "localhost",
    "user": "user",
    "password": "password",
    "database": "mydatabase"
}

connection_pool = mysql.connector.pooling.MySQLConnectionPool(pool_name="mypool", pool_size=5, **db_config)

def get_connection():
    return connection_pool.get_connection()

# 使用连接池中的连接
conn = get_connection()
cursor = conn.cursor()
cursor.execute("SELECT * FROM orders")
result = cursor.fetchall()
cursor.close()
conn.close()