mysql 5.1.73 分区表_mysql5.1分区表如何处理null（原创）

原创文章，转载请注明出处。

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

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

[root@nslave193 test]# 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)

[root@nslave193 test]# 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)

这和rang分区结果是一样的，都存储在第一个分区，但是算法不同。举例说明：

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

[root@nslave193 ~]# cd /u01/data/test/

[root@nslave193 test]# 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

[root@nslave193 test]# 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数据发生了改变，很明显数据存储在第一个分区。