mysql分区方法_学习MySQL Partition分区方法

参考文档：http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html

Range分区，List分区，Hash分区，Key分区，各自区别见下面的create table语句就能一目了然。

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT NOT NULL,

store_id INT NOT NULL

)

PARTITION BY RANGE (store_id) (

PARTITION p0 VALUES LESS THAN (6),

PARTITION p1 VALUES LESS THAN (11),

PARTITION p2 VALUES LESS THAN (16),

PARTITION p3 VALUES LESS THAN (21)

);

Region

Store ID Numbers

North

3, 5, 6, 9, 17

East

1, 2, 10, 11, 19, 20

West

4, 12, 13, 14, 18

Central

7, 8, 15, 16

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT

)

PARTITION BY LIST(store_id) (

PARTITION pNorth VALUES IN (3,5,6,9,17),

PARTITION pEast VALUES IN (1,2,10,11,19,20),

PARTITION pWest VALUES IN (4,12,13,14,18),

PARTITION pCentral VALUES IN (7,8,15,16)

);

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT

)

PARTITION BY HASH( YEAR(hired) )

PARTITIONS 4;

CREATE TABLE k1 (

id INT NOT NULL,

name VARCHAR(20),

UNIQUE KEY (id)

)

PARTITION BY KEY()

PARTITIONS 2;