mysql表分区语句记录

建立分区表

CREATE TABLE realtable (
 id int(10)unsigned NOT NULL  COMMENT'表主键',
 pid int(10)unsigned NOT NULL COMMENT'产品ID',
 price decimal(15,2)NOT NULL COMMENT'单价',
 num int(11)NOT NULL COMMENT'购买数量',
 uid int(10)unsigned NOT NULL COMMENT'客户ID',
 atime datetime NOT NULL COMMENT'下单时间',
 utime int(10)unsigned NOT NULL DEFAULT 0 COMMENT'修改时间',

PRIMARY KEY( id , atime )
)
 
PARTITION BY RANGE COLUMNS(atime)(

PARTITION p24 VALUES LESS THAN('2018-01-01'),
PARTITION p25 VALUES LESS THAN('2018-02-01'),
PARTITION p26 VALUES LESS THAN('2018-03-01'),
PARTITION p27 VALUES LESS THAN('2018-04-01'),
PARTITION p28 VALUES LESS THAN('2018-05-01'),
PARTITION p29 VALUES LESS THAN('2018-06-01'),
PARTITION p30 VALUES LESS THAN('2018-07-01'),
PARTITION p31 VALUES LESS THAN('2018-08-01'),
PARTITION p32 VALUES LESS THAN('2018-09-01'),
PARTITION p33 VALUES LESS THAN('2018-10-01'),
PARTITION p34 VALUES LESS THAN('2018-11-01'),
PARTITION p35 VALUES LESS THAN('2018-12-01'),
PARTITION p36 VALUES LESS THAN MAXVALUE
);

建立子分区表

CREATE TABLE realtable (
 id int(10)unsigned NOT NULL  COMMENT'表主键',
 pid int(10)unsigned NOT NULL COMMENT'产品ID',
 price decimal(15,2)NOT NULL COMMENT'单价',
 num int(11)NOT NULL COMMENT'购买数量',
 uid int(10)unsigned NOT NULL COMMENT'客户ID',
 atime datetime NOT NULL COMMENT'下单时间',
 utime int(10)unsigned NOT NULL DEFAULT 0 COMMENT'修改时间',

PRIMARY KEY( id , atime )
)
 
PARTITION BY RANGE COLUMNS(atime)(

PARTITION p24 VALUES LESS THAN('2018-01-01')
 (
        SUBPARTITION s0 
            DATA DIRECTORY = '/disk0/data' 
            INDEX DIRECTORY = '/disk0/idx',
        SUBPARTITION s1 
            DATA DIRECTORY = '/disk1/data' 
            INDEX DIRECTORY = '/disk1/idx'
    ),
PARTITION p25 VALUES LESS THAN('2018-02-01'),
PARTITION p26 VALUES LESS THAN('2018-03-01'),
PARTITION p27 VALUES LESS THAN('2018-04-01'),
PARTITION p28 VALUES LESS THAN('2018-05-01'),
PARTITION p29 VALUES LESS THAN('2018-06-01'),
PARTITION p30 VALUES LESS THAN('2018-07-01'),
PARTITION p31 VALUES LESS THAN('2018-08-01'),
PARTITION p32 VALUES LESS THAN('2018-09-01'),
PARTITION p33 VALUES LESS THAN('2018-10-01'),
PARTITION p34 VALUES LESS THAN('2018-11-01'),
PARTITION p35 VALUES LESS THAN('2018-12-01'),
PARTITION p36 VALUES LESS THAN MAXVALUE
);

查询某张表的分区信息

SELECT
  partition_name part, 
  partition_expression expr, 
  partition_description descr, 
  table_rows 
FROM
  INFORMATION_SCHEMA.partitions 
WHERE
  TABLE_SCHEMA = schema() 
  AND TABLE_NAME='table'; 

查看某个分区数据量

SELECT * FROM table PARTITION (partitionName);