MySQL中的NULL值问题

举例数据库：

mysql> SELECT * FROM PET;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+--------+---------+------+------------+------------+

1. NULL值不能用于比较，因为任何值和NULL值比较永远是NULL

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL, NULL = NULL;
+----------+-----------+----------+----------+-------------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | NULL = NULL |
+----------+-----------+----------+----------+-------------+
|     NULL |      NULL |     NULL |     NULL |        NULL |
+----------+-----------+----------+----------+-------------+

所以，寻找NULL值不能使用比较运算符，应该使用IS [NOT] NULL：

mysql> SELECT * FROM PET WHERE DEATH = NULL;
Empty set (0.01 sec)

mysql> SELECT * FROM PET WHERE DEATH IS NULL;
+----------+--------+---------+------+------------+-------+
| name     | owner  | species | sex  | birth      | death |
+----------+--------+---------+------+------------+-------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL  |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL  |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL  |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL  |
+----------+--------+---------+------+------------+-------+

2. NULL值在ORDER BY或者GROUP BY子句中按照相同对待

mysql> SELECT * FROM PET ORDER BY DEATH;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+--------+---------+------+------------+------------+

3. 0和' '不是NULL，完全可能插入到NOT NULL的列里

将PET表中的name字段改为NOT NULL，然后新加记录：

mysql> ALTER TABLE PET MODIFY COLUMN name VARCHAR(20) NOT NULL;
Query OK, 0 rows affected (0.10 sec)

mysql> DESCRIBE PET;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | NO   |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> INSERT INTO PET VALUES(NULL, 'Matthew', 'cat', 'f', '2010-01-04', NULL);
ERROR 1048 (23000): Column 'name' cannot be null

mysql> INSERT INTO PET VALUES('', 'Matthew', 'cat', 'f', '2010-01-04', NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO PET VALUES(0, 'Matthew', 'cat', 'f', '2010-01-04', NULL);
Query OK, 1 row affected (0.01 sec)

4. 在MySQL中，0和NULL意味着FALSE，其他值意味着TRUE

mysql> SELECT * FROM PET WHERE 1 <> 1;
Empty set (0.00 sec)

mysql> SELECT * FROM PET WHERE 1 <> NULL;
Empty set (0.00 sec)

5. 空值不占用空间，但NULL值占用空间

举个例子：假设你有一个杯子，空值代表杯子是真空的，NULL值代表杯子中装满了空气