In the MySQL partition table, the partition field is modified online. In the future, we will learn about partition (2)-> subpartitions and how to process null values.

-- MySQL partitions, subpartitions, and processing of null values. Read the notes in the official documents.

-- Key partitioning
Partitioning by key is similar to partitioning by hash, partition t 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
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 wowould 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:
If no primary key is specified and no partition field is specified during definition, the following error occurs:

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 partition field in definition, add the column in define, It is okmysql> Create Table tm3 (-> S1 char (32) ->)-> partition by key (S1)-> partitions 10; query OK, 0 rows affected (0.07 Sec) mysql>

 

Subpartition subpartitioning
Subpartitioning-also known as composite partitioning-is the further division 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 into ss the entire table.

(4) subpartitions can be used with especially large tables to distribute data and indexes when SS then 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    ->                 DATA DIRECTORY = '/disk2'    ->                 INDEX DIRECTORY = '/disk3'    ->         ),    ->         PARTITION p1 VALUES LESS THAN (2000) (    ->             SUBPARTITION s1a    ->                 DATA DIRECTORY = '/disk4/data'    ->                 INDEX DIRECTORY = '/disk4/idx',    ->             SUBPARTITION s1b    ->                 DATA DIRECTORY = '/disk5/data'    ->                 INDEX DIRECTORY = '/disk5/idx'    ->         ),    ->         PARTITION p2 VALUES LESS THAN MAXVALUE (    ->             SUBPARTITION s2a,    ->             SUBPARTITION s2b    ->         )    ->     );Query OK, 0 rows affected (0.04 sec)

 

In future, when the number of purchases for the decade beginning with the year 2000 grows to a point where the default location no longer provides sufficient space, the corresponding rows can be moved using an alter table... reorganize partition statement.
See section 17.3, "partition management", for an explanation of how this can be done.

The data directory and index directory options are disallowed in partition definitions when the no_dir_in_create server SQL mode is in effect. beginning with MySQL 5.5.5, these options are also disallowed when defining subpartitions (bug #42954 ).

How MySQL partitioning handles null
Partitioning in MySQL does nothing to disallow null as the value of a partitioning expression,
Whether it is a column value or the value of a user-supplied expression. even though it is permitted to use null as the value of an expression that must otherwise yield an integer, it is important to keep in mind that null is not a number. mySQL's partitioning
Implementation treats null as being less than any non-null value, just as order by does.

 

This means that treatment of null varies between partitioning of different types, and may produce behavior which you do not have CT if you are not prepared for it.
This being the case, we discuss in this section how each MySQL partitioning type handles null values when determining the partition in which a row shoshould be stored,
And provide examples for each.

 

Handling of null with range partitioning. If you insert a row into a table partitioned by range such that the column value used to determine the partition is null,
The row is inserted into the lowest partition. For example, consider these two tables in a database named P, created as follows:

 

(1) rang partition, OK
You can see the partitions created by these two create table statements using the following query against the partitions table in the information_schema database:

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH    ->    FROM INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_';+------------+----------------+------------+----------------+-------------+| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |+------------+----------------+------------+----------------+-------------+| t1         | p0             |          0 |              0 |       16384 || t1         | p1             |          0 |              0 |       16384 || t1         | p2             |          0 |              0 |       16384 || t2         | p0             |          0 |              0 |       16384 || t2         | p1             |          0 |              0 |       16384 || t2         | p2             |          0 |              0 |       16384 || t2         | p3             |          0 |              0 |       16384 || ts         | p0             |          0 |              0 |       16384 || ts         | p0             |          0 |              0 |       16384 || ts         | p1             |          0 |              0 |       16384 || ts         | p1             |          0 |              0 |       16384 || ts         | p2             |          0 |              0 |       16384 || ts         | p2             |          0 |              0 |       16384 |+------------+----------------+------------+----------------+-------------+14 rows in set (0.00 sec)

 

Now let us populate each of these tables with a single row containing a null in the column used as the partitioning key,
And verify that the rows were inserted using a pair of select statements:

You can see which partitions are used to store the inserted rows by rerunning the previous query against information_schema.partitions and inspecting the output:

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO t2 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM t1;+------+--------+| c1   | c2     |+------+--------+| NULL | mothra |+------+--------+1 row in set (0.01 sec)mysql> SELECT * FROM t2;+------+--------+| c1   | c2     |+------+--------+| NULL | mothra |+------+--------+1 row in set (0.00 sec)mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH    FROM INFORMATION_SCHEMA.PARTITIONS    

WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't_';+------------+----------------+------------+----------------+-------------+| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |+------------+----------------+------------+----------------+-------------+| t1         | p0             |          1 |          16384 |       16384 || t1         | p1             |          0 |              0 |       16384 || t1         | p2             |          0 |              0 |       16384 || t2         | p0             |          1 |          16384 |       16384 || t2         | p1             |          0 |              0 |       16384 || t2         | p2             |          0 |              0 |       16384 || t2         | p3             |          0 |              0 |       16384 || ts         | p0             |          0 |              0 |       16384 || ts         | p0             |          0 |              0 |       16384 || ts         | p1             |          0 |              0 |       16384 || ts         | p1             |          0 |              0 |       16384 || ts         | p2             |          0 |              0 |       16384 || ts         | p2             |          0 |              0 |       16384 |+------------+----------------+------------+----------------+-------------+13 rows in set (0.00 sec)You can also demonstrate that these rows were stored in the lowest partition of each table by dropping these partitions, 

and then re-running the SELECT statements: 

(2) handling of null with list partitioning. You must add null to the definition to input null partition data.

mysql> CREATE TABLE ts3 (    ->     c1 INT,    ->     c2 VARCHAR(20)    -> )    -> PARTITION BY LIST(c1) (    ->     PARTITION p0 VALUES IN (0, 3, 6),    ->     PARTITION p1 VALUES IN (1, 4, 7, NULL),    ->     PARTITION p2 VALUES IN (2, 5, 8)    -> );Query OK, 0 rows affected (0.01 sec)

Otherwise, the partition data of insert null will be wrong:
Error 1504 (hy000): Table has no partition for value null

 

(3) Handling of null with hash and key partitioning.

mysql> CREATE TABLE th (    ->     c1 INT,    ->     c2 VARCHAR(20)    -> )    -> PARTITION BY HASH(c1)    -> PARTITIONS 2;Query OK, 0 rows affected (0.00 sec)There is no data record in beginnig.mysql>   SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH    ->          FROM INFORMATION_SCHEMA.PARTITIONS    ->          WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='th';+------------+----------------+------------+----------------+-------------+| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |+------------+----------------+------------+----------------+-------------+| th         | p0             |          0 |              0 |       16384 || th         | p1             |          0 |              0 |       16384 |+------------+----------------+------------+----------------+-------------+2 rows in set (0.00 sec)mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT * FROM th;+------+--------+| c1   | c2     |+------+--------+| NULL | mothra ||    0 | gigan  |+------+--------+2 rows in set (0.00 sec)mysql>   SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH    ->          FROM INFORMATION_SCHEMA.PARTITIONS    ->          WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='th';+------------+----------------+------------+----------------+-------------+| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |+------------+----------------+------------+----------------+-------------+| th         | p0             |          2 |           8192 |       16384 || th         | p1             |          0 |              0 |       16384 |+------------+----------------+------------+----------------+-------------+2 rows in set (0.00 sec)

Recall that for any integer N, the value of null mod n is always null. for tables that are partitioned by hash or key, this result is treated for determining the correct partition as 0. checking the information_schema.partitions table once again, we can
See that both rows were inserted into partition P0:

 

MySQL processes null values in the partition. rang, key, and hash values are directly put into the min partition. list partitions are placed into partitions with null defined in advance. If list partitions do not have partitions with null values defined in advance, an error will be thrown during the input.