mysql允许RANGE和LIST分区上再进行HASH和KEY的子分区
mysql> create table ts(a int,b date)
-> partition by range(year(b))
-> subpartition by hash(TO_DAYS(b))
-> subpartitions 2(
-> partition p0 values less than (1990),
-> partition p1 values less than (2000),
-> partition p2 values less than maxvalue
-> );
Query OK, 0 rows affected (0.89 sec)
产生6个分区,3*2=6
mysql> select * from information_schema.partitions
-> where table_schema=database() and
-> table_name='ts'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: ts
PARTITION_NAME: p0
SUBPARTITION_NAME: p0sp0
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: 1
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: HASH
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: TO_DAYS(b)
PARTITION_DESCRIPTION: 1990
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: ts
PARTITION_NAME: p0
SUBPARTITION_NAME: p0sp1
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: 2
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: HASH
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: TO_DAYS(b)
PARTITION_DESCRIPTION: 1990
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: ts
PARTITION_NAME: p1
SUBPARTITION_NAME: p1sp0
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: 1
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: HASH
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: TO_DAYS(b)
PARTITION_DESCRIPTION: 2000
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 4. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: ts
PARTITION_NAME: p1
SUBPARTITION_NAME: p1sp1
PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: 2
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: HASH
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: TO_DAYS(b)
PARTITION_DESCRIPTION: 2000
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 5. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: ts
PARTITION_NAME: p2
SUBPARTITION_NAME: p2sp0
PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: 1
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: HASH
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: TO_DAYS(b)
PARTITION_DESCRIPTION: MAXVALUE
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 6. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: ts
PARTITION_NAME: p2
SUBPARTITION_NAME: p2sp1
PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: 2
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: HASH
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: TO_DAYS(b)
PARTITION_DESCRIPTION: MAXVALUE
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
6 rows in set (0.02 sec)
第二种方式:
CREATE TABLE ts2 (
a int(11) DEFAULT NULL,
b date DEFAULT NULL
)
PARTITION BY RANGE (year(b))
SUBPARTITION BY HASH (to_days(b))
(PARTITION p0 VALUES LESS THAN (1990)
(SUBPARTITION s0 ,
SUBPARTITION s1 ),
PARTITION p1 VALUES LESS THAN (2000)
(SUBPARTITION s2 ,
SUBPARTITION s3 ),
PARTITION p2 VALUES LESS THAN MAXVAlUE
(SUBPARTITION s4 ,
SUBPARTITION s5 ));
几条SQL:
1.create table ts3 like ts2;
建立ts3表和ts2一模一样
2.alter table ts3 drop partition p0;
删除ts3表的p0分区
相关推荐
mysql-connector-net-0.1.0.msi mysql-connector-net-6.3.6.msi mysql-connector-net-6.3.7.msi mysql-connector-net-6.3.8.msi mysql-connector-net-6.3.9.msi mysql-connector-net-6.4.0.msi mysql-connector-...
开发工具 mysql-connector-java-5.1.40-bin开发工具 mysql-connector-java-5.1.40-bin开发工具 mysql-connector-java-5.1.40-bin开发工具 mysql-connector-java-5.1.40-bin开发工具 mysql-connector-java-5.1.40-bin...
mysql57驱动jar包mysql-connector-java-5.1.49 mysql57驱动jar包mysql-connector-java-5.1.49 mysql57驱动jar包mysql-connector-java-5.1.49 mysql57驱动jar包mysql-connector-java-5.1.49 mysql57驱动jar包mysql-...
mysql-installer-community-5.7.31.0 mysql-installer-community-5.7.31.0 mysql-installer-community-5.7.31.0 mysql-installer-community-5.7.31.0 mysql-installer-community-5.7.31.0 mysql-installer-community...
amoeba-mysql-binary-2.2.0.tar.gz amoeba-mysql-binary-2.2.0.tar.gz amoeba-mysql-binary-2.2.0.tar.gz amoeba-mysql-binary-2.2.0.tar.gzamoeba-mysql-binary-2.2.0.tar.gz amoeba-mysql-binary-2.2.0.tar.gz ...
JavaEE源代码 mysql-connector-java-5.0.8-binJavaEE源代码 mysql-connector-java-5.0.8-binJavaEE源代码 mysql-connector-java-5.0.8-binJavaEE源代码 mysql-connector-java-5.0.8-binJavaEE源代码 mysql-connector...
java连接mysql数据库的驱动,里边包含两个版本:5.1.47、8.0.28 也可从以下两个地址免费下载: ...3. https://cdn.mysql.com//archives/mysql-connector-java-5.1/mysql-connector-java-5.1.47.zip
赠送jar包:mysql-binlog-connector-java-0.21.0.jar; 赠送原API文档:mysql-binlog-connector-java-0.21.0-javadoc.jar; 赠送源代码:mysql-binlog-connector-java-0.21.0-sources.jar; 赠送Maven依赖信息文件:...
包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-...
mysql-connector-net-6.8.3.msi mysql-connector-net-6.9.9.msi mysql-for-visualstudio-1.2.6.msi mysql-for-visualstudio-1.2.7.msi
mysql-connector-java-5.0.5.jar mysql-connector-java-5.1.6.jar mysql-connector-java-5.1.18.jar mysql-connector-java-5.1.21.jar mysql-connector-java-5.1.35.jar mysql-connector-java-5.1.39.jar mysql-...
centos 下hive连接mysql驱动 ,mysql-connector-java-8.0.26-1.el7.noarch. 使用方法: 1.下载本rpm文件后,上传至你的服务器(虚拟机) 2. rpm -ivh mysql-connector-java-8.0.26-1.el7.noarch进行安装 3.安装完成...
解压缩后会有两个文件:mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar和mysql8安装说明.txt 1、安装 rpm -i 需要安装的包文件名 举例如下: rpm -i example.rpm 安装 example.rpm 包; rpm -iv example.rpm 安装 ...
mysql-connector-java mysql-connector-java-5.1.5 mysql-connector-java-5.1.40
mysql-connector-java-8.0.27 jar包 mysql-connector-java-8.0.27 jar包 mysql-connector-java-8.0.27 jar包 mysql-connector-java-8.0.27 jar包 mysql-connector-java-8.0.27 jar包 mysql-connector-java-8.0.27 ...
mysql-connector-net-6.9.9.msi mysql-connector-net-6.10.7.msi mysql-for-visualstudio-1.2.8.msi Win64OpenSSL-1_1_0i.exe
mysql-connector-java-5.1.40.zip和mysql-connector-java-5.1.10.jar
mysql-connector-java-5.1.46-bin.jar mysql-connector-java-5.1.27-bin.jar
mysql-connector-net-6.10.8.msi + mysql-for-visualstudio-1.2.8.msi ef 连接mysql 工具类
mysql-connector-java-5.1.27.jar mysql-connector-java-5.1.27.jar