MySQL分区处理null值的方式

mysql分区表中，分区键值可以使用null，不同的分区方式对null值的处理不一样。
range分区表：
mysql> create table goolen(
    -> id int ,
    -> name varchar(35)
    -> ) engine=innodb
    -> partition by range(id) (
    -> partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than (30),
    -> partition pmax values less than maxvalue
    -> );
Query OK, 0 rows affected (0.41 sec)

插入null值
mysql> insert into goolen values(null,'c');
Query OK, 1 row affected (0.00 sec)

mysql> select * from goolen;
+------+------+
| id   | name |
+------+------+
| NULL | c    |
+------+------+
1 row in set (0.00 sec)

通过partitions表查询null被插入到哪个分区
mysql> select PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS from information_schema.partitions 
where table_schema='goolen' and table_name='goolen';
+----------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+----------------------+-----------------------+------------+
| p0             | id                   | 10                    |          1 |
| p1             | id                   | 20                    |          0 |
| p2             | id                   | 30                    |          0 |
| pmax           | id                   | MAXVALUE              |          0 |
+----------------+----------------------+-----------------------+------------+
4 rows in set (0.01 sec)

可以看到，range分区表，null被当作最小值处理

list分区
mysql> create table goolen1(
    -> id int ,
    -> name varchar(35)
    -> ) engine=innodb
    -> partition by list(id) (
    -> partition p0 values in (0,2,4),
    -> partition p1 values in (1,3,5)
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> insert into goolen1 values(null,'a');
ERROR 1526 (HY000): Table has no partition for value NULL

如果list分区中没有枚举值null，则插入null会报错

mysql> create table goolen2(
    -> id int ,
    -> name varchar(35)
    -> ) engine=innodb
    -> partition by list(id) (
    -> partition p0 values in (0,2,4),
    -> partition p1 values in (1,3,5),
    -> partition pnull values in (NULL)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> insert into goolen2 values(null,'a'); 
Query OK, 1 row affected (0.00 sec)

mysql> select PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS from information_schema.partitions where table_schema='goolen' and table_name='goolen2';
+----------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+----------------------+-----------------------+------------+
| p0             | id                   | 0,2,4                 |          0 |
| p1             | id                   | 1,3,5                 |          0 |
| pnull          | id                   | NULL                  |          1 |
+----------------+----------------------+-----------------------+------------+

hash分区：
mysql> create table goolen3(
    -> id int ,
    -> name varchar(35)
    -> ) engine=innodb
    -> partition by hash(id)
    -> partitions 3;
Query OK, 0 rows affected (0.27 sec)

mysql> insert into goolen3 values(null,'a'); 
Query OK, 1 row affected (0.00 sec)

mysql> select PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS from information_schema.partitions where table_schema='goolen' and table_name='goolen3';
+----------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+----------------------+-----------------------+------------+
| p0             | id                   | NULL                  |          0 |
| p1             | id                   | NULL                  |          0 |
| p2             | id                   | NULL                  |          1 |
+----------------+----------------------+-----------------------+------------+
3 rows in set (0.00 sec)

mysql> insert into goolen3 values(0,'a');  
Query OK, 1 row affected (0.00 sec)

mysql> select PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS from information_schema.partitions where table_schema='goolen' and table_name='goolen3';
+----------------+----------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+----------------------+-----------------------+------------+
| p0             | id                   | NULL                  |          1 |
| p1             | id                   | NULL                  |          0 |
| p2             | id                   | NULL                  |          1 |
+----------------+----------------------+-----------------------+------------+
3 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1371891/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1371891/