Mysql版本:MySQL Server 6.0
现象描述 :使用is null条件判断时null值字段会交替显示null或0,使用isnull()函数作为条件判断时始终显示null。
实例引用 :
mysql> select * from producttype where parentid is null;
+--------+----------+----------+---------+----------+
| typeid | name | note | visible | parentid |
+--------+----------+----------+---------+----------+
| 0 | typename | typenote | | NULL |
| 1 | typename | typenote | | NULL |
| 2 | typename | typenote | | NULL |
| 3 | typename | typenote | | NULL |
| 4 | typename | typenote | | NULL |
+--------+----------+----------+---------+----------+
5 rows in set (0.00 sec)
mysql> select * from producttype where parentid is null;
+--------+----------+----------+---------+----------+
| typeid | name | note | visible | parentid |
+--------+----------+----------+---------+----------+
| 0 | typename | typenote | | 0 |
| 1 | typename | typenote | | 0 |
| 2 | typename | typenote | | 0 |
| 3 | typename | typenote | | 0 |
| 4 | typename | typenote | | 0 |
+--------+----------+----------+---------+----------+
5 rows in set (0.00 sec)
mysql> select * from producttype where isnull(parentid)=true;
+--------+----------+----------+---------+----------+
| typeid | name | note | visible | parentid |
+--------+----------+----------+---------+----------+
| 0 | typename | typenote | | NULL |
| 1 | typename | typenote | | NULL |
| 2 | typename | typenote | | NULL |
| 3 | typename | typenote | | NULL |
| 4 | typename | typenote | | NULL |
+--------+----------+----------+---------+----------+
5 rows in set (0.01 sec)
mysql> select * from producttype where isnull(parentid)=true;
+--------+----------+----------+---------+----------+
| typeid | name | note | visible | parentid |
+--------+----------+----------+---------+----------+
| 0 | typename | typenote | | NULL |
| 1 | typename | typenote | | NULL |
| 2 | typename | typenote | | NULL |
| 3 | typename | typenote | | NULL |
| 4 | typename | typenote | | NULL |
+--------+----------+----------+---------+----------+
5 rows in set (0.00 sec)
请高手解释为何会出现这种情况,这种情况影响到jpal查询结果,导致我程序的bug。
先谢谢各位!
2010年2月27日 16:04