我新建了一张表:
CREATE TABLE TEST
( POL_NO VARCHAR(20) DEFAULT NULL
,EFF_DT DATE DEFAULT NULL
,PLAN_CODE VARCHAR(5) DEFAULT NULL
,KEY INDEX_TEST_PLAN_CODE (PLAN_CODE)
) default charset=gb2312 auto_increment=1;然后用LOAD DATA INFILE往TEST表中插入数据,对应PLAN_CODE字段的数据为空。表数据示例如下:
+------------------+------------+-----------+| POL_NO | EFF_DT | PLAN_CODE |+------------------+------------+-----------+| 9999999999999999 | 2002-09-05 | || 000001007427088 | 2001-12-25 | |+------------------+------------+-----------+但是我执行语句SELECT * FROM TEST WHERE PLAN_CODE IS NULL时,返回的却是0条记录,而SELECT * FROM TEST where PLAN_CODE = '' 却能返回2条记录。同时,我用select ASCII(PLAN_CODE) from TEST看过,值均为0,应该都是NULL没错。按我的理解,判断字段是否为空应该用IS NULL来判断,为什么我的例子里却无法匹配到记录呢?谢谢!
CREATE TABLE TEST
( POL_NO VARCHAR(20) DEFAULT NULL
,EFF_DT DATE DEFAULT NULL
,PLAN_CODE VARCHAR(5) DEFAULT NULL
,KEY INDEX_TEST_PLAN_CODE (PLAN_CODE)
) default charset=gb2312 auto_increment=1;然后用LOAD DATA INFILE往TEST表中插入数据,对应PLAN_CODE字段的数据为空。表数据示例如下:
+------------------+------------+-----------+| POL_NO | EFF_DT | PLAN_CODE |+------------------+------------+-----------+| 9999999999999999 | 2002-09-05 | || 000001007427088 | 2001-12-25 | |+------------------+------------+-----------+但是我执行语句SELECT * FROM TEST WHERE PLAN_CODE IS NULL时,返回的却是0条记录,而SELECT * FROM TEST where PLAN_CODE = '' 却能返回2条记录。同时,我用select ASCII(PLAN_CODE) from TEST看过,值均为0,应该都是NULL没错。按我的理解,判断字段是否为空应该用IS NULL来判断,为什么我的例子里却无法匹配到记录呢?谢谢!
-----
mysql> select * from test20141231;
+--------+------------+-----------+
| POL_NO | EFF_DT | PLAN_CODE |
+--------+------------+-----------+
| 123 | 2014-12-31 | NULL |
| 456 | 2014-12-22 | NULL |
| 789 | 2014-12-15 | |
+--------+------------+-----------+
3 rows in set
+--------+------------+-----------+
| POL_NO | EFF_DT | PLAN_CODE |
+--------+------------+-----------+
| 123 | 2014-12-31 | NULL |
| 456 | 2014-12-22 | NULL |
+--------+------------+-----------+
2 rows in setmysql> select * from test20141231 where PLAN_CODE='';
+--------+------------+-----------+
| POL_NO | EFF_DT | PLAN_CODE |
+--------+------------+-----------+
| 789 | 2014-12-15 | |
+--------+------------+-----------+
1 row in setmysql>
空才是0,null还是输出null
mysql> select * from test20141231;
+--------+------------+-----------+
| POL_NO | EFF_DT | PLAN_CODE |
+--------+------------+-----------+
| 123 | 2014-12-31 | NULL |
| 456 | 2014-12-22 | NULL |
| 789 | 2014-12-15 | |
+--------+------------+-----------+
3 rows in setmysql> select ASCII(PLAN_CODE) from test20141231;
+------------------+
| ASCII(PLAN_CODE) |
+------------------+
| NULL |
| NULL |
| 0 |
+------------------+
3 rows in set