MySQL中NULL和NOT NULL探究

MySQL中有几个比较迷人的地方，例如NULL、NOT NULL和''（空字符）就是其中之一，下面就来认识下他们

1. 声明在MySQL中，0或 NULL意味着假而其它值意味着真。

2. NULL值与其他值比较（即使是NULL）永远不会为“真”，包含NULL的表达式总是会导出NULL值，除非在关于操作符的文档中以及表达式的函数中作了其他规定。

3. 对于NULL值的相关判断使用IS 或者 IS NOT。

下面举例说明

结论：从上图中可以看出，0和''都不是NULL值，都是NOT NULL值

4. 如果列声明为NOT NULL则不能插入NULL值，可以插入相应的0或者''（空字符）。

创建如下数据表

CREATE TABLE test (id MEDIUMINT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT,col1 VARCHAR(255) NOT NULL,col2 VARCHAR(255)) ENGINE = MyISAM;

插入操作

INSERT INTO test (col1, col2) VALUES (NULL, NULL);ERROR 1048 (23000): Column 'col1' cannot be null

再继续插入操作

INSERT INTO test (col1, col2) VALUES ('Tom', NULL);INSERT INTO test (col1, col2) VALUES ('', NULL);INSERT INTO test (col1, col2) VALUES ('NULL', NULL);INSERT INTO test (col1, col2) VALUES ('Jack', '');INSERT INTO test (col1, col2) VALUES ('', 'Jim');

此时表中数据如下

继续查询操作，针对col1列

mysql> SELECT * FROM test WHERE col1 = NULL;Empty set (0.00 sec)/* WHERE col1 = NULL 相当于WHERE NULL */mysql> SELECT * FROM test WHERE col1 IS NULL;Empty set (0.00 sec)mysql> SELECT * FROM test WHERE col1 IS NOT NULL;+----+------+------+| id | col1 | col2 |+----+------+------+|  1 | Tom  | NULL ||  2 |      | NULL ||  3 | NULL | NULL ||  4 | Jack |      ||  5 |      | Jim  |+----+------+------+5 rows in set (0.00 sec)mysql> SELECT * FROM test WHERE col1 = '';+----+------+------+| id | col1 | col2 |+----+------+------+|  2 |      | NULL ||  5 |      | Jim  |+----+------+------+2 rows in set (0.00 sec)mysql> SELECT * FROM test WHERE col1 <> '';+----+------+------+| id | col1 | col2 |+----+------+------+|  1 | Tom  | NULL ||  3 | NULL | NULL ||  4 | Jack |      |+----+------+------+3 rows in set (0.00 sec)

继续查询操作，针对col2列

mysql> SELECT * FROM test WHERE col2 IS NULL;+----+------+------+| id | col1 | col2 |+----+------+------+|  1 | Tom  | NULL ||  2 |      | NULL ||  3 | NULL | NULL |+----+------+------+3 rows in set (0.00 sec)mysql> SELECT * FROM test WHERE col2 IS NOT NULL;+----+------+------+| id | col1 | col2 |+----+------+------+|  4 | Jack |      ||  5 |      | Jim  |+----+------+------+2 rows in set (0.00 sec)mysql> SELECT * FROM test WHERE col2 = '';+----+------+------+| id | col1 | col2 |+----+------+------+|  4 | Jack |      |+----+------+------+1 row in set (0.00 sec)mysql> SELECT * FROM test WHERE col2 <> '';+----+------+------+| id | col1 | col2 |+----+------+------+|  5 |      | Jim  |+----+------+------+1 row in set (0.00 sec)

继续查询操作

mysql> SELECT COUNT(*) FROM test;+----------+| COUNT(*) |+----------+|        5 |+----------+1 row in set (0.00 sec)mysql> SELECT COUNT(col2) FROM test;+-------------+| COUNT(col2) |+-------------+|           2 |+-------------+1 row in set (0.00 sec)mysql> SELECT COUNT(*), col2 FROM test GROUP BY col2;+----------+------+| COUNT(*) | col2 |+----------+------+|        3 | NULL ||        1 |      ||        1 | Jim  |+----------+------+3 rows in set (0.01 sec)

从以上查询结果中可以得出：

1）对某列使用算术比较操作符例如=、<>等时，忽略NULL值，注意不是‘NULL’字符串。

2）使用DISTINCT、GROUP BY或ORDER BY时，所有NULL值将被视为等同的。

3）对于聚合（累计）函数，如COUNT()、MIN()和SUM()，将忽略NULL值，但是，COUNT(*)除外，它计算行数不是单独的列值。