MySQL 分区表 partition线上修改分区字段，后续进一步学习partition (2) --> 子分区以及对录入Null值的处理情况.
-- KEY Partitioning
Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server. This internal hashing function is based on the
same algorithm as PASSWORD().
KEY is used rather than HASH.
KEY takes only a list of one or more column names. The column or columns used as the partitioning key must comprise part or all of the table's primary key, if the table has one.
KEY takes a list of zero or more column names. Where no column name is specified as the partitioning key, the table's primary key is used, if there is one. For example, the following CREATE TABLE statement is valid in MySQL 5.5:
mysql> CREATE TABLE k1 ( -> id INT NOT NULL PRIMARY KEY, -> name VARCHAR(20) -> ) -> PARTITION BY KEY() -> PARTITIONS 2; Query OK, 0 rows affected (0.06 sec) If there is no primary key but there is a unique key, then the unique key is used for the partitioning key: mysql> CREATE TABLE k2 ( -> id INT NOT NULL, -> name VARCHAR(20), -> UNIQUE KEY (id) -> ) -> PARTITION BY KEY() -> PARTITIONS 2; Query OK, 0 rows affected (0.02 sec)
However, if the unique key column were not defined as NOT NULL, then the previous statement would fail.
In both of these cases, the partitioning key is the id column, even though it is not shown in the output of SHOW CREATE TABLE or in the PARTITION_EXPRESSION column of the INFORMATION_SCHEMA.PARTITIONS table.
As below:
mysql> SELECT t.TABLE_NAME, t.PARTITION_NAME,t.TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS t WHERE table_name='k2'; +------------+----------------+------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | +------------+----------------+------------+ | k2 | p0 | 3 | | k2 | p1 | 4 | +------------+----------------+------------+ 2 rows in set (0.01 sec)
Unlike the case with other partitioning types, columns used for partitioning by KEY are not restricted to integer or NULL values.
For example, the following CREATE TABLE statement is valid:
没有primary key，没有在定义时候指定分区字段，会抱错：
mysql> CREATE TABLE tm3 (
-> s1 CHAR(32)
-> )
-> PARTITION BY KEY()
-> PARTITIONS 10;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
在定义中加入分区字段，add the column in define , it is ok
mysql> CREATE TABLE tm3 (
-> s1 CHAR(32)
-> )
-> PARTITION BY KEY(s1)
-> PARTITIONS 10;
Query OK, 0 rows affected (0.07 sec)
mysql>
子分区 Subpartitioning
Subpartitioning—also known as composite partitioning—is the further pision of each partition in a partitioned table.
For example, consider the following CREATE TABLE statement:
mysql> CREATE TABLE ts (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> 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
-> )
-> );
Query OK, 0 rows affected (0.04 sec)
CREATE TABLE ts3 (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
SUBPARTITION s3
)
);
(1) Each partition must have the same number of subpartitions. if not ,fail
mysql> CREATE TABLE ts3 (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0,
-> SUBPARTITION s1
-> ),
-> PARTITION p1 VALUES LESS THAN (2000),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s2,
-> SUBPARTITION s3
-> )
-> );
ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near '
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
' at line 8
mysql>
(2) Each SUBPARTITION clause must include (at a minimum) a name for the subpartition.
Otherwise, you may set any desired option for the subpartition or allow it to assume its default setting for that option.
(3) Subpartition names must be unique across the entire table.
(4) Subpartitions can be used with especially large tables to distribute data and indexes across many disks. Suppose that you have 6 disks mounted as /disk0, /disk1, /disk2, and so on. Now consider the following example:
mysql> CREATE TABLE ts5 (id INT, purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) )
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0
-> DATA DIRECTORY = '/disk0/data'
-> INDEX DIRECTORY = '/disk0/idx',
-> SUBPARTITION s1
-> DATA DIRECTORY = '/disk1/data'
-> INDEX DIRECTORY = '/disk1/idx'
-> ),
-> PARTITION p1 VALUES LESS THAN (2000) (
-> SUBPARTITION s2
-> DATA DIRECTORY = '/disk2/data'
-> INDEX DIRECTORY = '/disk2/idx',
-> SUBPARTITION s3
-> DATA DIRECTORY = '/disk3/data'
-> INDEX DIRECTORY = '/disk3/idx'
-> ),
-> PARTITION p2 VALUES LESS THAN MAXVALUE (
-> SUBPARTITION s4
-> DATA DIRECTORY = '/disk4/data'
-> INDEX DIRECTORY = '/disk4/idx',
-> SUBPARTITION s5
-> DATA DIRECTORY = '/disk5/data'
-> INDEX DIRECTORY = '/disk5/idx'
-> )
-> );
Query OK, 0 rows affected (0.04 sec)
In this case, a separate disk is used for the data and for the indexes of each RANGE. Many other variations are possible;another example might be:
mysql> CREATE TABLE ts6 (id INT, purchased DATE)
-> PARTITION BY RANGE(YEAR(purchased))
-> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990) (
-> SUBPARTITION s0a
-> DATA DIRECTORY = '/disk0'
-> INDEX DIRECTORY = '/disk1',
-> SUBPARTITION s0b