mysql 分区指定分区表数据存储路径，可以支持跨磁盘
最新回答
萌妹子
2023-09-08 09:30:30
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
);
热门标签
