mysql 分区指定路径_mysql 分区指定分区表数据存储路径，可以支持跨磁盘

INNODB引擎只支持DATA DIRECTORY，不支持INDEX DIRECTORY, 另外 目录要空目录权限归属mysql 组mysql 用户，命令

mkdir /data

chown mysql:mysql /data

主键必须包含分区字段，PRIMARY KEY (id,store_id)

CREATE TABLE orders_list2 (

id INT AUTO_INCREMENT,

customer_surname VARCHAR(30),

store_id INT,

salesperson_id INT,

order_date DATE,

note VARCHAR(500),

PRIMARY KEY (id,store_id),

INDEX idx (id)

) ENGINE = INNODB

PARTITION BY LIST(store_id) (

PARTITION p1

VALUES IN (1, 3, 4, 17)

DATA DIRECTORY = '/data/p1',

PARTITION p2

VALUES IN (2, 12, 14)

DATA DIRECTORY = '/data/p2',

PARTITION p3

VALUES IN (6, 8, 20)

DATA DIRECTORY = '/data/p3',

PARTITION p4

VALUES IN (5, 7, 9, 11, 16)

DATA DIRECTORY = '/data/p4',

PARTITION p5

VALUES IN (10, 13, 15, 18)

DATA DIRECTORY = '/data/p5'

);

分区表扩充

ALTER TABLE orders_list2  ADD PARTITION (PARTITION p6 VALUES IN  (21,22,23)

DATA DIRECTORY = '/var/lib/mysql/monitor/orders/p6'  ;

子分区

CREATE TABLE tb_sub_dir (id INT, purchased DATE)

ENGINE='MYISAM'

PARTITION BY RANGE( YEAR(purchased) )

SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990)

(

SUBPARTITION s0

DATA DIRECTORY = '/data/data_sub1'

INDEX DIRECTORY = '/data/idx_sub1',

SUBPARTITION s1

DATA DIRECTORY = '/data/data_sub1'

INDEX DIRECTORY = '/data/idx_sub1'

),

PARTITION p1 VALUES LESS THAN (2000)

(

SUBPARTITION s2

DATA DIRECTORY = '/data/data_sub2'

INDEX DIRECTORY = '/data/idx_sub2',

SUBPARTITION s3

DATA DIRECTORY = '/data/data_sub2'

INDEX DIRECTORY = '/data/idx_sub2'

),

PARTITION p2 VALUES LESS THAN MAXVALUE

(

SUBPARTITION s4

DATA DIRECTORY = '/data/data_sub3'

INDEX DIRECTORY = '/data/idx_sub3',

SUBPARTITION s5

DATA DIRECTORY = '/data/data_sub3'

INDEX DIRECTORY = '/data/idx_sub3'

)

);

日期字段区分

CREATE TABLE employees1 (

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 RANGE ( YEAR(separated) ) (

PARTITION p0 VALUES LESS THAN (1991),

PARTITION p1 VALUES LESS THAN (1996),

PARTITION p2 VALUES LESS THAN (2001),

PARTITION p3 VALUES LESS THAN MAXVALUE

);