大家好,又见面了,我是你们的朋友全栈君。
PARTITIONS
表提供有关表分区的信息。 此表中的每一行对应于分区表的单个分区或子分区。 有关分区表的更多信息,请参见分区。
PARTITIONS
表有以下列:
CREATE TABLE tp ( c1 INT, c2 INT, c3 VARCHAR(25) ) PARTITION BY HASH(c1 + c2) PARTITIONS 4;
此表中分区的PARTITIONS表行中的PARTITION_EXPRESSION列显示c1 + c2,如下所示:
mysql> SELECT DISTINCT PARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test'; +----------------------+
| PARTITION_EXPRESSION | +----------------------+
| c1 + c2 | +----------------------+
Notes
示例
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE );
mysql> select * from information_schema.partitions where TABLE_SCHEMA='test' and table_name='ts' limit 2\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(purchased)
SUBPARTITION_EXPRESSION: TO_DAYS(purchased)
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: 2018-08-15 11:09:38
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(purchased)
SUBPARTITION_EXPRESSION: TO_DAYS(purchased)
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: 2018-08-15 11:09:38
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
2 rows in set (0.00 sec)
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/124907.html原文链接:https://javaforall.cn