mysql已有库表增加分区，根据时间戳按月分区

xue2xue

已于 2023-07-06 11:12:53 修改

阅读量790

点赞数

文章标签： mysql sql 数据库

于 2023-07-06 11:10:15 首次发布

本文链接：https://blog.youkuaiyun.com/xue2xue/article/details/131571803

版权

该SQL语句用于对名为dataTable的表进行按时间范围的分区，使用了to_days函数处理OPERTIME列，创建了从2024年1月到2025年10月的分区，每个分区对应一个月，并指定了InnoDB存储引擎。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

			-- 添加分区
ALTER TABLE dataTable PARTITION BY RANGE (to_days(`OPERTIME`))
(
 PARTITION dataTable_202401 VALUES LESS THAN (739282) ENGINE = InnoDB,
 PARTITION dataTable_202402 VALUES LESS THAN (739311) ENGINE = InnoDB,
 PARTITION dataTable_202403 VALUES LESS THAN (739342) ENGINE = InnoDB,
 PARTITION dataTable_202404 VALUES LESS THAN (739372) ENGINE = InnoDB,
 PARTITION dataTable_202405 VALUES LESS THAN (739403) ENGINE = InnoDB,
 PARTITION dataTable_202406 VALUES LESS THAN (739433) ENGINE = InnoDB,
 PARTITION dataTable_202407 VALUES LESS THAN (739464) ENGINE = InnoDB,
 PARTITION dataTable_202408 VALUES LESS THAN (739495) ENGINE = InnoDB,
 PARTITION dataTable_202409 VALUES LESS THAN (739525) ENGINE = InnoDB,
 PARTITION dataTable_202410 VALUES LESS THAN (739556) ENGINE = InnoDB,
 PARTITION dataTable_202411 VALUES LESS THAN (739586) ENGINE = InnoDB,
 PARTITION dataTable_202412 VALUES LESS THAN (739617) ENGINE = InnoDB,
 PARTITION dataTable_202501 VALUES LESS THAN (739648) ENGINE = InnoDB,
 PARTITION dataTable_202502 VALUES LESS THAN (739676) ENGINE = InnoDB,
 PARTITION dataTable_202503 VALUES LESS THAN (739707) ENGINE = InnoDB,
 PARTITION dataTable_202504 VALUES LESS THAN (739737) ENGINE = InnoDB,
 PARTITION dataTable_202505 VALUES LESS THAN (739768) ENGINE = InnoDB,
 PARTITION dataTable_202506 VALUES LESS THAN (739798) ENGINE = InnoDB,
 PARTITION dataTable_202507 VALUES LESS THAN (739829) ENGINE = InnoDB,
 PARTITION dataTable_202508 VALUES LESS THAN (739860) ENGINE = InnoDB,
 PARTITION dataTable_202509 VALUES LESS THAN (739890) ENGINE = InnoDB,
 PARTITION dataTable_202510 VALUES LESS THAN (739921) ENGINE = InnoDB)