MySQL Essentials
eBook

$8.99

eBookFrenzy.com

Partitioning in MySQL does nothing to disallow NULL as the value of a partitioning expression, whether it is a column value or the value of a user-supplied expression. In general, MySQL will treat a NULL as a zero in such cases. If you wish to circumvent this behavior, you should design tables so as not to allow nulls; most likely you can do so by declaring columns NOT NULL.

In this section, we provide some examples illustrating how MySQL handles NULL values when determining the partition in which a row should be stored.

If you insert a row into a table partitioned by RANGE or LIST such that the column value used to determine the partition is NULL, it is treated as 0. For example, consider these two tables, created and populated as follows:

mysql> CREATE TABLE tnlist (
    ->     id INT,
    ->     name VARCHAR(5)
    -> )
    -> PARTITION BY LIST(id) (
    ->     PARTITION p1 VALUES IN (0),
    ->     PARTITION p2 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE tnrange (
    ->     id INT,
    ->     name VARCHAR(5)
    -> )
    -> PARTITION BY RANGE(id) (
    ->     PARTITION p1 VALUES LESS THAN (1),
    ->     PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO tnlist VALUES (NULL, 'bob');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tnrange VALUES (NULL, 'jim');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tnlist;
+------+------+
| id   | name |
+------+------+
| NULL | bob  |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tnrange;
+------+------+
| id   | name |
+------+------+
| NULL | jim  |
+------+------+
1 row in set (0.00 sec)

In both tables, the id column was not declared as NOT NULL, which means that it admits NULL values. You can verify that the rows were stored in the partitions p1 of the each table by dropping these partitions, and then re-running the SELECT statements:

mysql> ALTER TABLE tnlist DROP PARTITION p1;
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE tnrange DROP PARTITION p1;
Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM tnlist;
Empty set (0.00 sec)

mysql> SELECT * FROM tnrange;
Empty set (0.00 sec)

In the case of partitioning by HASH or KEY, any partition expression that yields a NULL value is treated as though its return value were zero. We can verify this behavior by examining the effects on the filesystem of creating a table partitioned by HASH and populating it with a record containing appropriate values. Suppose that you have a table tnhash, created in the test database, using this statement:

CREATE TABLE tnhash (
    id INT,
    name VARCHAR(5)
)
PARTITION BY HASH(id)
PARTITIONS 2;

Assuming an RPM installation of MySQL on Linux, this statement creates two .MYD files in /var/lib/mysql/test, which can be viewed in the bash shell as follows:

/var/lib/mysql/test> ls *.MYD -l
-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 tnhash#P#p0.MYD
-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 tnhash#P#p1.MYD

(Note: Prior to MySQL 5.1.5, these files would have been named tnhash_p0.MYD and tnhash_p1.MYD. See Section D.1.3, “Changes in release 5.1.6 (01 February 2006)” and Bug #13437 for information regarding how this change impacts upgrades.)

Note that the size of each file is 0 bytes. Now insert a row into tnhash whose id column value is NULL and verify that this row was inserted:

mysql> INSERT INTO tnhash VALUES (NULL, 'sam');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tnhash;
+------+------+
| id   | name |
+------+------+
| NULL | sam  |
+------+------+
1 row in set (0.01 sec)

Recall that for any integer N, the value of NULL MOD N is always NULL. This result is treated for determining the correct partition as 0. Returning to the system shell (still assuming bash for this purpose), we can see that the value was inserted into the first partition (named p0 by default) by listing the data files once again:

var/lib/mysql/test> ls *.MYD -l
-rw-rw----  1 mysql mysql 20 2005-11-04 18:44 tnhash#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2005-11-04 18:41 tnhash#P#p1.MYD

You can see that the INSERT statement modified only the file tnhash_p0.MYD, increasing its size on disk, without affecting the other data file.

Suppose that we have a table such as this one:

CREATE TABLE tndate (
    id INT,
    dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

As with other MySQL functions, YEAR(NULL) returns NULL. A row with a dt column value of NULL is treated as though the partitioning expression evaluated to 0, and is inserted into partition p0.