公共查询

# 查看分区数据
SELECT table_name,partition_name,partition_description,from_days(partition_description),table_rows
FROM information_schema.PARTITIONS
WHERE table_name='AIR_ORDER';

# 分析分区
ALTER TABLE AIR_ORDER ANALYZE PARTITION issue2022;

# 
SHOW CREATE TABLE AIR_ORDER;

select * from AIR_ORDER partition (issue202301);

EXPLAIN SELECT * FROM AIR_ORDER WHERE create_time >= '2023-03-26' AND create_time <='2023-04-15';

# 创建存储过程
DROP PROCEDURE IF EXISTS p_partition_month;
create PROCEDURE p_partition_month()
BEGIN
    DECLARE v_sysdate DATE;   #当前日期
    DECLARE v_mindate DATETIME; #最小日期
    DECLARE v_maxdate DATETIME; #最大日期
    DECLARE v_pt VARCHAR(20); #分区名称
    DECLARE v_maxval int; # 分区最大日期天数
    DECLARE add_sql VARCHAR(256); # 新增分区sql
    DECLARE del_sql VARCHAR(256); # 删除分区sql
    DECLARE i INT;

    SELECT MAX(CAST(FROM_DAYS(REPLACE(partition_description,'''','')) AS DATE)) AS val
    INTO  v_maxdate
    FROM  information_schema.`PARTITIONS`
    WHERE table_name='AIR_ORDER' AND table_schema='tieniu_dev';  # 读取数据库实例表当前分区名称中最大值
    SET v_sysdate = SYSDATE(); #当前日期

    select v_maxdate;#
    select v_sysdate;

    WHILE v_maxdate<(v_sysdate+INTERVAL 3 MONTH) DO
            SET v_pt = DATE_FORMAT(v_maxdate, '%Y%m');#将要创建的分区名称
            SET v_maxval=TO_DAYS(v_maxdate+ INTERVAL 1 MONTH);# 设置分区最大值
            SET add_sql =  CONCAT('alter table AIR_ORDER add partition (partition p', v_pt, ' values less than(',v_maxval,'))');
            SET @sql=add_sql; #存储于会话变量
            select add_sql;
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SET v_maxdate = v_maxdate + INTERVAL 1 MONTH;
        END WHILE;


END
;

# 执行
call p_partition_month();

# 创建定时任务事件，每月月底 23 点执行存储过程 
# 创建定时事件
CREATE EVENT e_partition
    ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(last_day(curdate()), INTERVAL 1 MONTH),INTERVAL 23 HOUR)
    ON COMPLETION PRESERVE ENABLE
    DO CALL p_partition_month();

# 删除定时事件
drop event e_partition;