mysql中NULL值

lucky-yhc 2018-06-19 原文

在SQL中，NULL并不能采用 != 与数值进行比较，若要进行比较，我们只能采用 IS NULL 或 IS NOT NULL。

原因：

NULL is used as a placeholder for unknown or inapplicable values, it is treated differently from other values.
It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.

因为只是一个占位符，用于不可知的值。

例子：

drop table test_null;
CREATE TABLE `test_null` (
  `p_name` varchar(128) DEFAULT NULL,
  `p_index` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
 
insert into test_null(p_index) values( 2 );
insert into test_null(p_name, p_index) values( 'test', 2 );
 
mysql> select * from test_null where p_name =null;
Empty set (0.00 sec)
 
mysql> select * from test_null where p_name !=null;
Empty set (0.00 sec)
 
mysql> select * from test_null where p_name <>null;
Empty set (0.01 sec)
 
mysql> select * from test_null where p_name is null;
+--------+---------+
| p_name | p_index |
+--------+---------+
| NULL   |       2 |
+--------+---------+
1 row in set (0.00 sec)