mysql中null值_mysql中NULL值

在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 =, . 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)