mysql5.7 分区表_mysql分区表学习

一：怎样对已有数据的表进行表分区

可以直接alter table进行修改。

如：

USE dba;

ALTER TABLE t3 PARTITION BY RANGE(id)

(

PARTITION p1 VALUES LESS THAN(5),

PARTITION p2 VALUES LESS THAN (10),

PARTITIONp3 VALUES LESS THAN maxvalue

);

二：分区表的限制

2.1 不支持外键

当表中一个字段建了外键，引用另一个表字段时，在该表上无法创建分区表。会报错.

Mysql 5.5:

Error Code: 1217

Cannot delete or update a parent row: aforeign key constraint fails

Mysql 5.7:

ERROR 1506 (HY000): Foreign keys are notyet supported in conjunction with partitioning

2.2 分区键必须是INT类型，或者通过表达式返回INT类型

分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键(BLOB or TEXT列除外)

2.3如果表中有主键和唯一索引，按主键字段进行分区时，唯一索引列应该包含分区键。

例如：t4表有个主键和唯一索引。

变成分区表时会报错：

ALTER TABLE t4PARTITION BY RANGE(id)

(

PARTITION p1VALUES LESS THAN(2),

PARTITION p2VALUES LESS THAN maxvalue

)

Error Code: 1503

A UNIQUE INDEX must include all columns in the table’spartitioning function

唯一索引应该这样创建：

CREATE UNIQUEINDEX ind_age ON t4(id,age) ;

再按主键分区，就不会报错了。

2.4 目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引

对临时表分区时，会报错：

Error Code: 1562

Cannot create temporary table withpartitions

CREATE TABLE t10(

id INT,

title VARCHAR(30),

FULLTEXT (title)

) ENGINE=MYISAM

PARTITION BY RANGE(id)

(

PARTITION p1 VALUES LESS THAN(9),

PARTITION p2 VALUES LESS THAN maxvalue

);

报错：

Error Code: 1214

The used table type doesn’t supportFULLTEXT indexes

对有空间类型列的表分区时，报错：

Error Code: 1178

The storage engine for the table doesn’tsupport GEOMETRY

2.5 对象限制

下面这些对象在不能出现在分区表达式

Stored functions, stored procedures, UDFs, orplugins.

Declared variables or user variables.

2.6 运算限制

支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个INT或者NULL。

支持DIV,不支持/

|, &, ^, <>, and ~ 不允许出现在分区表达式中

2.7 sql_mode限制

官方强烈建议你在创建分区表后，永远别改变mysql的sql_mode。因为在不同的模式下，某些函数或者运算返回的结果可能会不一样。

2.8 不支持query_cache

2.9 分区键不能是一个子查询

即使子查询返回的是int值或者null.

2.10 子分区

只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区。

子分区必须是HASHorKEY类型。

2.11 分区表不支持INSERTDELAYED

SQL代码

mysql> insert DELAYED into user_msg_pvalues(18156629,0,0,0,0,0,0,0,0,0);

ERROR 1616 (HY000): DELAYED option not supportedfor table ‘user_msg_p’