MySQL分区表的一些问题

分区表的特点

MySQL支持多种分区表。我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。

分区表建表

 1. 分区表不能使用非分区的其他单独的列做主键

  2. 分区表新建时要定义好每一个分区的详细信息

 3. 分区表可以建立联合主键，也可以不用主键，指定两个列建立唯一索引（如果不建主键，在一个列上建唯一索引也是不会成功的）

 4. 分区表建立主键后，如果分区发生改变，比如新建或删除了分区，插入语句不会受影响

5.  因为是联合主键，如果需要单一字段ID 唯一，这时想单独给ID 设置 唯一索引是不会成功的，可以自行生成数据插入（比如uuid) 。

 

 下面是几种错误建表报错示例：

CREATE TABLE sales (

    dt datetime not null,
    person_id varchar(64) not null

)ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION by RANGE (YEAR(dt))

[SQL] CREATE TABLE sales (

    dt datetime not null,
    person_id varchar(64) not null

)ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION by RANGE (YEAR(dt))

[Err] 1492 - For RANGE partitions each partition must be defined

CREATE TABLE sales (
 `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
	dt datetime not null,
	person_id varchar(64) not null,
	PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION by RANGE (YEAR(dt))
( partition p_2019 VALUES LESS THAN (2019),
	PARTITION p_2020 VALUES LESS THAN (2020));
)

[SQL] CREATE TABLE sales (
 `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
	dt datetime not null,
	person_id varchar(64) not null,
	PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION by RANGE (YEAR(dt))
( partition p_2019 VALUES LESS THAN (2019),
	PARTITION p_2020 VALUES LESS THAN (2020));
[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

正确的建表语句

CREATE TABLE sales (
 `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
	dt datetime not null,
	person_id varchar(64) not null,
	PRIMARY KEY (`id`,dt)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION by RANGE (YEAR(dt))
( partition p_2019 VALUES LESS THAN (2019),   //注：值小于2019的存到p_2019分区，也就是存的数据是2018年或之前的
	PARTITION p_2020 VALUES LESS THAN (2020),
	PARTITION p_2021 VALUES LESS THAN (2021)
);

增加分区

ALTER TABLE sales  ADD PARTITION (PARTITION p_2022 VALUES LESS THAN (2022));  //按范围分分区

ALTER TABLE sales  ADD PARTITION (partition p6 values in ('Technique Leader') ); //也可以指定按具体值分分区

删除分区

ALTER TABLE sales  DROP PARTITION  p_2019

查看各个分区的数据

select * from sales PARTITION(p_2019);

select * from sales  //查看所有数据

 

个人觉得，分区表不如直接分表来的干脆