升级 AIR_ORDER

主表

sd

# 创建用于分区的分割字段(lq_key_time)
alter table AIR_ORDER add lq_key_time datetime not null;

# 设置 lq_key_time 为创建时间(最好是第一段行程起飞时间)
update AIR_ORDER set lq_key_time = create_time;

# 删除原主键
alter table AIR_ORDER drop primary key;

# 创建联合主键
alter table AIR_ORDER add PRIMARY KEY(local_order_id, lq_key_time);

# 创建分区
ALTER TABLE `AIR_ORDER`  PARTITION BY RANGE(TO_DAYS(lq_key_time)) (
    PARTITION p2018 VALUES LESS THAN (TO_DAYS('2019-01-01')) ENGINE = INNODB,
    PARTITION p2019 VALUES LESS THAN (TO_DAYS('2020-01-01')) ENGINE = INNODB,
    PARTITION p2020 VALUES LESS THAN (TO_DAYS('2021-01-01')) ENGINE = INNODB,
    PARTITION p2021 VALUES LESS THAN (TO_DAYS('2022-01-01')) ENGINE = INNODB,
    PARTITION p2022 VALUES LESS THAN (TO_DAYS('2023-01-01')) ENGINE = INNODB,
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')) ENGINE = INNODB,
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')) ENGINE = INNODB,
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')) ENGINE = INNODB,
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')) ENGINE = INNODB,
    PARTITION p202305 VALUES LESS THAN (TO_DAYS('2023-06-01')) ENGINE = INNODB,
    PARTITION p202306 VALUES LESS THAN (TO_DAYS('2023-07-01')) ENGINE = INNODB,
    PARTITION p202307 VALUES LESS THAN (TO_DAYS('2023-08-01')) ENGINE = INNODB,
    PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-09-01')) ENGINE = INNODB
);
posted @ 2023-05-19 19:22  ddgo's  阅读(11)  评论(0编辑  收藏  举报
.