MySQL创建分区方法，及分区中常用SQL示例

本文总结如何通过sql创建表分区，查看分区，以及日常如何使用分区表。

通过sql语句创建

mysql > CREATE TABLE `database_test`.`table_test`  (  `id` int NOT NULL AUTO_INCREMENT,  `name` varchar(50) NOT NULL,  `create_time` datetime(0) NOT NULL,  PRIMARY KEY (`id`,`create_time`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8PARTITION BY RANGE(YEAR(create_time))(    PARTITION partition1 VALUES LESS THAN (2020),    PARTITION partition2 VALUES LESS THAN (2021));

查看分区情况：

mysql> SELECT PARTITION_NAME        ,PARTITION_METHOD        ,PARTITION_EXPRESSION        ,PARTITION_DESCRIPTION        ,TABLE_ROWS        ,SUBPARTITION_NAME,SUBPARTITION_METHOD    FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='your_table';

分区中常用SQL示例
1、查询指定分区内的数据：

mysql>  select * from `your_table` partition(your_partition_name);

 2、对已存在的表进行分区

mysql> ALTER TABLE `your_table` PARTITION by HASH(YEAR(`day`)) PARTITIONS 10;

删除表的所有分区

mysql>  ALTER TABLE `your_table` REMOVE PARTITIONING;

4、删除表的特定分区

mysql> ALTER TABLE `your_table` DROP PARTITION your_partition_name;

5、 删除表单个分区内的数据

mysql >  ALTER TABLE `your_table` TRUNCATE PARTITION your_partition_name;

把分区内的数据复制到另一张表

mysql> INSERT INTO `your_new_table` SELECT * FROM `your_old_table` PARTITION(your_partition_name);

新增分区

mysql> alter table `your_table` add partition(partition your_partition_name values less than(2021));

8、原表已有主键，需要新增复合主键，则需要先丢弃再重新设置主键。

mysql> ALTER TABLE `your_database`.`your_table` DROP PRIMARY KEY,ADD PRIMARY KEY (`id`, `login_time`) USING BTREE;

查询分区信息。

mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHODFROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='your_table';

原表设置了MAXVALUE分区，若想添加新分区又不丢失数据，则需要重新分区。

Alter table `your_table` partition by RANGE(YEAR(login_time))(partition p1 values less than (2014),partition p2 values less than (2015),partition p3 values less than (2016),partition p4 values less than (2019),partition p5 values less than (MAXVALUE));

来源地址：https://blog.csdn.net/qiuweifan/article/details/131268321