mysql

 

-- https://my.oschina.net/u/3160411/blog/1594052

创建表tb_test

CREATE TABLE `tb_test` (
  `name` varchar(255) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 插入几条数据

  INSERT INTO tb_test VALUES
    ('null',NULL),
    ('empty',''),
    ('0','0'),
    ('1','1'),
    ('2','2');


 问题:查询出value字段为null,空字符串,字符串'0'和特定值'1'

--MySQL数据库版本
SELECT version()   5.6.21-log
SELECT @@version   5.6.21-log

--
SELECT * FROM tb_test WHERE `value`=1 or ISNULL(`value` or `value`=null);  --null,empty,0,1
SELECT * FROM tb_test WHERE `value`='1' or ISNULL(`value` or `value`=null);--null,empty,0,1
SELECT * FROM tb_test WHERE `value`=1;--1
SELECT * FROM tb_test WHERE `value`='1';--1
SELECT * FROM tb_test WHERE ISNULL(`value` or `value`=null);   --null,empty,0
SELECT * FROM tb_test WHERE ISNULL(`value`); --null
SELECT * FROM tb_test WHERE ISNULL(`value`=null); --null,empty,0,1,2
SELECT * FROM tb_test WHERE `value`=NULL; --这种方式获取到一条记录,所有字段都为null
SELECT '0'=NULL; --null
SELECT '1'=NULL; --null
SELECT ''=NULL; --null
SELECT 0=NULL; --null
SELECT 1=NULL; --null
SELECT NULL=NULL; --null
SELECT (NULL or NULL=NULL); --null
SELECT ('' or '' =NULL); --null
SELECT ('0' or '0'=NULL); --null
SELECT ('1' or '1'=NULL); --1
SELECT ('2' or '2'=NULL); --1
总结:= ,!=不能用来判断一个字段是否为空,应该用ISNULL()

任何值使用=与NULL比较返回的都是NULL

null、空字符串、字符串0、数字0与null进行or运算返回null

上述问题使用符合表达式ISNULL(`value` or `value`=null)可以分解为

value='0' or value='' or ISNULL(value)

示例: u.age = '' or ISNULL(TRIM(u.age))

 

原文地址:https://www.cnblogs.com/hahajava/p/13475542.html