mysql分区

mysql新增分区语句

在原有表上新增分区，把U_USER_INFO表按地域分区

ALTER TABLE U_USER_INFO
PARTITION BY RANGE(`areaId`)
(
 PARTITION  areaErr VALUES less than (10),
 PARTITION  area10 VALUES less than (11),
 PARTITION  area11 VALUES less than (12),
 PARTITION  area12 VALUES less than (13),
 PARTITION  area13 VALUES less than (14),
 PARTITION  area14 VALUES less than (15),
 PARTITION  area15 VALUES less than (16),
 PARTITION  area16 VALUES less than (17),
 PARTITION  area17 VALUES less than (18),
 PARTITION  area18 VALUES less than (19),
 PARTITION  area19 VALUES less than (20),
PARTITION  area20 VALUES less than (21)
);

语法为

ALTER TABLE `表名`

PARTITION BY RANGE(`字段名`)

(

PARTITION `分区名1` VALUES LESS THAN(`值1`),

PARTITION `分区名2` VALUES LESS THAN(`值2`)

);

注意：用于分区的字段必须在主键中或者是unique key ，

否则会报 错“[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function”