MySQL分区处理NULL值的方式
一般情况下,MySQL的分区把NULL当做零值,或者一个最小值进行处理
对于range分区
create table test_null(
id int
)
partition by range(id)(
partition p0 values less than (-6),
partition p1 values less than (0),
partition p2 values less than (1),
partition p3 values less than MAXVALUE
);
mysql> insert into test_null values(null);
Query OK, 1 row affected (0.01 sec)
mysql> select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitions where
-> table_schema = schema()
-> and table_name='test_null';
+------+------+----------+------------+
| part | expr | descr | table_rows |
+------+------+----------+------------+
| p0 | id | -6 | 1 |
| p1 | id | 0 | 0 |
| p2 | id | 1 | 0 |
| p3 | id | MAXVALUE | 0 |
+------+------+----------+------------+
4 rows in set (0.00 sec)
对于list分区
当分区定义不包含NULL值是,会报错
create table list_null(
id int
)
partition by list(id)(
partition p1 values in (0),
partition p2 values in (1)
);
mysql> insert into list_null values(null);
ERROR 1526 (HY000): Table has no partition for value NULL
当分区定义包含NULL时
create table list_null_1(
id int
)
partition by list(id)(
partition p1 values in (0),
partition p2 values in (1),
partition p3 values in (null)
);
mysql> insert into list_null_1 values(null);
Query OK, 1 row affected (0.01 sec)
mysql> select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitions where
-> table_schema = schema()
-> and table_name='list_null_1';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p1 | id | 0 | 0 |
| p2 | id | 1 | 0 |
| p3 | id | NULL | 1 |
+------+------+-------+------------+
3 rows in set (0.00 sec)
对于Hash分区和key分区
create table hash_null(
id int
)
partition by hash(id)
partitions 2;
mysql> insert into hash_null values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitions where
-> table_schema = schema()
-> and table_name='hash_null';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p0 | id | NULL | 1 |
| p1 | id | NULL | 0 |
+------+------+-------+------------+
2 rows in set (0.00 sec)
对于Key分区
create table key_null(
id int
)
partition by key(id)
partitions 2;
mysql> select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitions where
-> table_schema = schema()
-> and table_name='key_null';
+------+------+-------+------------+
| part | expr | descr | table_rows |
+------+------+-------+------------+
| p0 | `id` | NULL | 1 |
| p1 | `id` | NULL | 0 |
+------+------+-------+------------+