mysql 5.7 对于NULL值的判断 is null 与 is not null 有什么区别?请大神赐教
小草随风 2017-09-07 03:32:26 表结构如下:
-------------------------------------------
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`sex` tinyint(1) NOT NULL,
`name` char(10) NOT NULL,
`age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-----------------------------------------------------------------------
添加如下数据:
-------------------------------------------------------------------------
INSERT INTO `test` (`id`, `sex`, `name`, `age`) VALUES
(1, 1, 'a1', 12),
(2, 0, 'b1', 13),
(3, 1, 'a1', 12),
(4, 0, 'b1', 13),
(5, 1, 'a2', 15),
(6, 0, 'b2', 16),
(7, 0, 'b3', 18),
(8, 1, 'a3', 19),
(9, 0, 'b4', 21);
------------------------------------------------------------
分别执行如下sql分组语句
--------------------------------------------------
1、
SELECT sex,
if(sex is null,'',group_concat(name)) as name1
FROM `test` group by sex with ROLLUP
--------------------------------------------------
sex name1
0 b1,b1,b2,b3,b4
1 a1,a1,a2,a3
NULL b1,b1,b2,b3,b4,a1,a1,a2,a3
--------------------------------------------
2、
SELECT sex,
if(sex is not null,group_concat(name),'') as name1
FROM `test` group by sex with ROLLUP
--------------------------------------------------
sex name1
0 b1,b1,b2,b3,b4
1 a1,a1,a2,a3
NULL
--------------------------------------------------
问题:为什么 is not null 判断出了sex不为NULL 的情况 ,而 is null 不能成功判断???