mysql5.1分区表如何处理null

2009年05月11日　作者： 大头刚　

从mysql5.1.8开始，分区表把null当作一个比所有非null值都小的一个值，就像做order by排序一样。
根据这个前提，不同类型的分区表，null的处理方法有所不同，下面分别举例说明。

1、rang分区
如果你向一个范围分区表插入一条分区键值为null的数据，那么这条数据会插入到这个分区表的最低的那个分区，例如：

mysql> 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
    ->  );
Query OK, 0 rows affected (0.00 sec)
 
mysql> 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)
    ->  );
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into t1 values(null,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(null,'aaa'); 
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
| NULL | aaa  | 
+------+------+
1 row in set (0.00 sec)
mysql> select * from t2;
+------+------+
| c1   | c2   |
+------+------+
| NULL | aaa  | 
+------+------+
1 row in set (0.00 sec)

我们可以到数据目录下，看看数据插入到哪个分区了。

ll |grep t|grep MYD
-rw-rw----  1 mysql mysql    20 Feb 13 16:05 t1#P#p0.MYD
-rw-rw----  1 mysql mysql     0 Feb 13 16:03 t1#P#p1.MYD
-rw-rw----  1 mysql mysql     0 Feb 13 16:03 t1#P#p2.MYD
-rw-rw----  1 mysql mysql    20 Feb 13 16:05 t2#P#p0.MYD
-rw-rw----  1 mysql mysql     0 Feb 13 16:04 t2#P#p1.MYD
-rw-rw----  1 mysql mysql     0 Feb 13 16:04 t2#P#p2.MYD

很显然，数据都插入到了p0这个分区，也就是最低的分区，切记不是0的这个分区。

2、LIST分区
如果你向一个列表分区表插入一条分区键值为null的数据，那么你需要定义一个值为null的分区。举例：

CREATE TABLE t1 (
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)
);
mysql> CREATE TABLE t1 (
    -> 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)
    -> );
Query OK, 0 rows affected (0.00)
 
mysql> insert into t1 values(null,'aaaa');
ERROR 1526 (HY000): Table has no partition for value NULL
mysql> insert into t1 values(9,'aaaa');    
ERROR 1526 (HY000): Table has no partition for value 9
mysql> insert into t1 values(1,'aaaa'); 
Query OK, 1 row affected (0.00 sec)

当然插入9、null的时候报错一样，没有这个分区。我们可以添加一个值为null的分区

mysql> alter table t1 add partition (partition p3 values in(null));        
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> insert into t1 values(null,'aaaa');      
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 | aaaa | 
| NULL | aaaa | 
+------+------+
2 rows in set (0.00 sec)

OK，已经插入。我们可以用c1字段进行排序，看看是否是null排在最小。

mysql> select * from t1 order by c1;
+------+------+
| c1   | c2   |
+------+------+
| NULL | aaaa | 
|    1 | aaaa | 
+------+------+
2 rows in set (0.00 sec)

我们在数据文件目录看下，null在那个分区。

 
mysql> truncate table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(null,'aaaa');
Query OK, 1 row affected (0.00 sec)
ll |grep t|grep MYD
-rw-rw----  1 mysql mysql     0 Feb 13 16:53 t1#P#p0.MYD
-rw-rw----  1 mysql mysql     0 Feb 13 17:11 t1#P#p1.MYD
-rw-rw----  1 mysql mysql     0 Feb 13 16:53 t1#P#p2.MYD
-rw-rw----  1 mysql mysql    20 Feb 13 17:11 t1#P#p3.MYD

很明显，存在定义为null的这个分区里。

3、hash和key分区
hash分区在处理null的时候和其他类型的分区不一样。
根据hash分区的规则，数据存储在哪个分区，根据把分区键值mod计算得出。
但是mod(null,n)的值永远是null，这样mysql就不知道需要将null存储到哪个分区。所以mysql在处理这种情况的时候，都是把null值当作0来处理，那么mod(0,n)的值永远是0，所以null永远是存储在第一个分区。例如n=4

 
mysql> select mod(null,4); 
+-------------+
| mod(null,4) |
+-------------+
|        NULL | 
+-------------+
1 row in set (0.00 sec)
mysql> select mod(0,4);    
+----------+
| mod(0,4) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)
<pre>
这和rang分区结果是一样的，都存储在第一个分区，但是算法不同。举例说明：
<pre lang="mysql">
 
mysql> CREATE TABLE t1 (
    -> c1 INT,
    -> c2 VARCHAR(20)
    -> )
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
 
 ll|grep MYD|grep t
-rw-rw----  1 mysql mysql     0 Feb 16 14:33 t1#P#p0.MYD
-rw-rw----  1 mysql mysql     0 Feb 16 14:33 t1#P#p1.MYD
-rw-rw----  1 mysql mysql     0 Feb 16 14:33 t1#P#p2.MYD
-rw-rw----  1 mysql mysql     0 Feb 16 14:33 t1#P#p3.MYD
mysql> INSERT INTO t1 VALUES (NULL, 'mothra'); 
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+--------+
| c1   | c2     |
+------+--------+
| NULL | mothra | 
+------+--------+
1 row in set (0.00 sec)
mysql> quit
Bye
ll|grep MYD|grep t
-rw-rw----  1 mysql mysql    20 Feb 16 14:37 t1#P#p0.MYD
-rw-rw----  1 mysql mysql     0 Feb 16 14:33 t1#P#p1.MYD
-rw-rw----  1 mysql mysql     0 Feb 16 14:33 t1#P#p2.MYD
-rw-rw----  1 mysql mysql     0 Feb 16 14:33 t1#P#p3.MYD

第一个分区p0数据发生了改变，很明显数据存储在第一个分区。