SHOW PLUGINS; sql 可以查看partition的Status 是否是ACTIVE的

使用mydatetime 进行水平分区案例:

CREATE TABLE test_users (    `id` INT (10) NOT NULL AUTO_INCREMENT,    `mydatetime` datetime NOT NULL,    `email` VARCHAR (255) NOT NULL,    UNIQUE INDEX (`email`),    PRIMARY KEY (`id`));-- 如果表已创建时的操作-- 修改主键的类型ALTER TABLE test_users CHANGE COLUMN `id` `id` INT (10) UNSIGNED NOT NULL;-- 删除主键ALTER TABLE test_users DROP PRIMARY KEY;-- 将 主键id和分区字段mydatetime 同作为主键ALTER TABLE test_users ADD PRIMARY KEY (id, mydatetime);-- 给主机 id 加上 自动增长ALTER TABLE test_users CHANGE COLUMN `id` `id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT;-- 删除索引 emailALTER TABLE test_users DROP INDEX email;-- 将 email和mydatetime字段 同作为名为 email的唯一索引ALTER TABLE test_users ADD UNIQUE KEY `email` (email, mydatetime);-- 根据 mydatetime的日期值，将小于 2018-12-31放在 p1,小于2019-06-30放在p2,小于最大值的放在p8分区,名字是自己定义的alter table test_users PARTITION by range(TO_DAYS(mydatetime))(    PARTITION p1 VALUES LESS THAN (TO_DAYS('2018-12-31')),    PARTITION p2 VALUES LESS THAN (TO_DAYS('2019-06-30')),    PARTITION p8 VALUES LESS THAN MAXVALUE );-- 分析查询语句，测试分区是否有用EXPLAIN SELECT * FROM test_users WHERE mydatetime <= '2019-07-01';

---

[Err]1503 - A UNIQUE INDEX must include all columns in the table's partitioning function[Err] 1486 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

尝试1:

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

主键需要包含分区的字段

尝试了将原来的主键删除掉，然后再重新创建一个组合主键

[Err]1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

删除主键失败

依次执行下列的sql

ALTER TABLE test_table CHANGE COLUMN `id` `id` int(10) unsigned NOT NULL ;ALTER TABLE test_table DROP PRIMARY KEY; #删除主键ALTER TABLE test_table add PRIMARY KEY(id); #添加主键

References

5. 需要和主键，分区的字段作为一个unique keyi进行处理
6. mysql [err] 1075