1. 测试NULL值,使用IS NULL和IS NOT NULL操作
mysql> select 1 is null, 1 is not null; +-----------+---------------+ | 1 is null | 1 is not null | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ 1 row in set (0.00 sec) mysql>
也可以用算术比较操作,如=, <, 或<>
mysql> select 1 = null, 1 <> null, 1 < null, 1 > null; +----------+-----------+----------+----------+ | 1 = null | 1 <> null | 1 < null | 1 > null | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 1 row in set (0.01 sec) mysql>
因为任何与NULL的算术比较,其结果都是NULL,所以从这样的比较中得不到任何有意义的结果。
两个NULL值被认为是相等的在使用GROUP BY时。
当使用ORDER BY时,NULL总是出现在第一位当执行ORDER BY ... ASC,而出现在最后一位当执行ORDER BY ... DESC。
0和空字符串与NULL并不是有相同意义的
mysql> select 0 is null, 0 is not null, '' is null, '' is not null; +-----------+---------------+------------+----------------+ | 0 is null | 0 is not null | '' is null | '' is not null | +-----------+---------------+------------+----------------+ | 0 | 1 | 0 | 1 | +-----------+---------------+------------+----------------+ 1 row in set (0.00 sec) mysql>
所以完全可以在定义为NOT NULL的列插入0或空字符串