验证分区表对于NULL值的存放位置

1、Handling of NULL with RANGE partitioning

        
 CREATE TABLE t1 (
         c1 INT,
         c2 VARCHAR(20)
     )
     PARTITION BY RANGE(c1) (
         PARTITION p0 VALUES LESS THAN (0),
         PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN MAXVALUE
     );       
      
     CREATE TABLE t2 (
         c1 INT,
         c2 VARCHAR(20)
     )
     PARTITION BY RANGE(c1) (
         PARTITION p0 VALUES LESS THAN (-5),
         PARTITION p1 VALUES LESS THAN (0),
         PARTITION p2 VALUES LESS THAN (10),
         PARTITION p3 VALUES LESS THAN MAXVALUE); 
        
        (root@33306) [test]>   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 |

(root@33306) [test]> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.01 sec)

(root@33306) [test]> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.01 sec)

(root@33306) [test]>  SELECT * FROM t1;
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

(root@33306) [test]>  SELECT * FROM t2;
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

(root@33306) [test]>    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 |

可以发现是NULL值放在p0 分区里面了

 

如果是date类型的列呢，再次测试

(root@33306) [test]>         CREATE TABLE tndate (
    ->     id INT,
    ->     dt DATE
    -> )
    -> PARTITION BY RANGE( YEAR(dt) ) (
    ->     PARTITION p0 VALUES LESS THAN (1990),
    ->     PARTITION p1 VALUES LESS THAN (2000),
    ->     PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );

(root@33306) [test]> insert into tndate VALUES(1,NULL);
Query OK, 1 row affected (0.00 sec)

(root@33306) [test]> SELECT *FROM TNDATE;
+------+------+
| id   | dt   |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

(root@33306) [test]>  SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    ->         FROM INFORMATION_SCHEMA.PARTITIONS
    ->         WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='tndate';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| tndate     | p0             |          1 |          16384 |       16384 |
| tndate     | p1             |          0 |              0 |       16384 |
| tndate     | p2             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
3 rows in set (0.00 sec)

结果：可以发现数据存储在P0的分区里面

 

2、Handling of NULL with LIST partitioning

mysql> CREATE TABLE ts1 (
         c1 INT,
         c2 VARCHAR(20)
     )
     PARTITION BY LIST(c1) (
         PARTITION p0 VALUES IN (0, 3, 6),
         PARTITION p1 VALUES IN (1, 4, 7),
         PARTITION p2 VALUES IN (2, 5, 8)
     );
     
(root@33306) [test]> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1526 (HY000): Table has no partition for value 9
(root@33306) [test]> 
(root@33306) [test]> 
(root@33306) [test]> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1526 (HY000): Table has no partition for value NULL
(root@33306) [test]> 

从上面可以看出只有0-8 可以插入到表ts1中，对于0-8以外和NULL值是无法插入其中

 

NULL值可以作为一个list 值放在list表中
 CREATE TABLE ts2 (
        c1 INT,
        c2 VARCHAR(20)
    )
    PARTITION BY LIST(c1) (
        PARTITION p0 VALUES IN (0, 3, 6),
        PARTITION p1 VALUES IN (1, 4, 7),
        PARTITION p2 VALUES IN (2, 5, 8),
        PARTITION p3 VALUES IN (NULL)
    );

 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)
     );

(root@33306) [test]>  INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

(root@33306) [test]>  INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.01 sec)

     
          SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='tndate';
        
        
 
(root@33306) [test]>           SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    ->         FROM INFORMATION_SCHEMA.PARTITIONS
    ->         WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 'ts_';    
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts1        | p0             |          0 |              0 |       16384 |
| ts1        | p1             |          0 |              0 |       16384 |
| ts1        | p2             |          0 |              0 |       16384 |
| ts2        | p0             |          0 |              0 |       16384 |
| ts2        | p1             |          0 |              0 |       16384 |
| ts2        | p2             |          0 |              0 |       16384 |
| ts2        | p3             |          1 |          16384 |       16384 |
| ts3        | p0             |          0 |              0 |       16384 |
| ts3        | p1             |          1 |          16384 |       16384 |
| ts3        | p2             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
10 rows in set (0.00 sec)

空值插入到对应的分区表中区

3、Handling of NULL with HASH and KEY partitioning

 

CREATE TABLE th (            
         c1 INT,            
         c2 VARCHAR(20)       
     )            
     PARTITION BY HASH(c1)    
     PARTITIONS 2;   
     
INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');

(root@33306) [test]> SELECT * FROM th;
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra |
|    0 | gigan  |
+------+--------+
2 rows in set (0.00 sec)     

     SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='th';
(root@33306) [test]>      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)

可以看到对于hash 分区表，NULL值是放在p0里面     

 

结论：对于range 和hash分区，null值会放在分区表的首个分区p0里面

        对于list 分区，NULL值是作为一个list值 放在分区列表中