mysql 指定分区查询和迁移分区表和事件

阿里查看事件开启

SELECT @@event_scheduler;
-- 创建一个测试表
CREATE TABLE test_event (id INT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

-- 创建一个测试事件
CREATE EVENT test_event_scheduler
ON SCHEDULE EVERY 1 MINUTE
DO
  INSERT INTO test_event (message) VALUES ('Event executed');

-- 等待几分钟，然后检查测试表是否有新记录
SELECT * FROM test_event;

总共的事情开启

-- 检查事件调度器状态
SELECT * FROM information_schema.EVENTS;

指定分区查询

explain select ifnull(max(money),0)  from t_recharge_logs PARTITION(p202109,p202110,p202111);

迁移到分区表记录

create
    definer = baobao_admin@`%` procedure Proc_SyncAndRenameTablesV2(IN p_starttime int, IN p_id int)
BEGIN
    -- 同步最后5分钟的数据
    INSERT INTO games_set_user_play_records_new
    SELECT * FROM games_set_user_play_records
    WHERE (play_at > p_starttime OR (play_at = p_starttime AND id > p_id));

    -- 重命名表
    RENAME TABLE games_set_user_play_records TO games_set_user_play_records_bak,
        games_set_user_play_records_new TO games_set_user_play_records;
END;


SELECT count(*) FROM red_diamond_amount_turnover
WHERE (send_time > 1718343930 OR (send_time = 1718343930 AND id > 187806352));


select * from red_diamond_amount_turnover_new order by send_time desc , id desc limit 1;
-- 锁定表
LOCK TABLES red_diamond_amount_turnover WRITE, red_diamond_amount_turnover_new WRITE;
    call Proc_SyncAndRenameTables(1718344238,187814412);
UNLOCK TABLES;


select * from games_set_user_play_records_new order by play_at desc , id desc limit 1;


-- 锁定表
LOCK TABLES games_set_user_play_records WRITE, games_set_user_play_records_new WRITE;
    call Proc_SyncAndRenameTablesV2(1718345590,95275615);
UNLOCK TABLES;



call Proc_Partition_maintenance();
call Proc_Partition_maintenance_date();
SELECT
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_ORDINAL_POSITION,
    TABLE_ROWS,
    PARTITION_DESCRIPTION
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'baobao' AND
    TABLE_NAME = 'users_amount_turnover';


SELECT
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_ORDINAL_POSITION,
    TABLE_ROWS,
    PARTITION_DESCRIPTION
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'baobao' AND
    TABLE_NAME = 'red_diamond_amount_turnover';


SELECT
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_ORDINAL_POSITION,
    TABLE_ROWS,
    PARTITION_DESCRIPTION
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'baobao' AND
    TABLE_NAME = 'games_set_user_play_records';