mysql处理null值_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/，如需转载，请注明出处，否则将追究法律责任。