MYSQL的null(空值)，还有空字符串的坑

原文来源：数据运营与数据分析

1.先建立数据

# 创建表demo02
CREATE TABLE demo02 
(
		id int PRIMARY KEY AUTO_INCREMENT COMMENT "编号",
		name CHAR(20) NOT NULL COMMENT "名字",
		sex CHAR(5) DEFAULT "未知" COMMENT "性别",
		age int(10) COMMENT "年龄",
		qualifications CHAR(40) COMMENT "学历",
		interests CHAR(40) COMMENT "爱好"
) ENGINE= INNODB character set utf8mb4;

# 插入数据
INSERT INTO demo02
		(id,name,sex,age,qualifications,interests)
VALUES
		(1,"xr","男",26,"本科","篮球"),
		(2,"hw","男",27,"硕士","游泳"),
		(3,"wp","男",26,"本科","游戏"),
		(4,"ljl","男",26,"本科","桌球"),
		(5,"cl","女",26,"硕士","小说"),
		(6,"hl","女",25,"专科","小吃"),
		(7,"cb","女",25,"本科",null),
		(8,"cy","男",26,"专科","调酒"),
		(9,"cj","女",24,"博士","  "),
		(10,"fj","男",27,"硕士","宅男"),
		(11,"jl","未知",27,"本科","")
;
# 查看数据
SELECT 
	*
FROM 
	demo02;

从查询结果中我们可以看出空值null和空字符串的显示是不相同的

2.简单对比一下

• 做count聚合的时候空字符串算在其中，而null则不计算；
• 做模糊查找的[not] like的时候null会被过滤掉，空字符串不会；
• 计算字符串长度的时候null等于null，空字符串等于0；
• 条件中使用<> “” 不但会剔除空字符串，同样null也会剔除；
• 条件中使用 = “” 只会出现有空字符串的内容，但null的会被剔除；

# 做count聚合的时候空字符串算在其中，而null则不计算
SELECT
	count( id ) AS 总行数,
	count(interests) AS 剔除null的行数
FROM demo02;

SELECT 
	name,
	interests
FROM demo02
WHERE interests NOT LIKE "%小%";

SELECT 
	name,
	interests
FROM demo02
WHERE interests LIKE "%";

SELECT 
	name,
	LENGTH(interests) as 字符串长度
FROM demo02;

SELECT 
	name,
	interests
FROM demo02
WHERE interests = "";

# 用<> "" 同样会过来掉null
SELECT 
	name,
	interests
FROM demo02
WHERE interests <> "";

3.判断NULL

用IS NULL 或者is not null，SQL语句函数中可以使用ifnull()函数来进行处理

SELECT 
	name,
	interests
FROM demo02
WHERE interests IS NULL;

SELECT 
	name,
	interests
FROM demo02
WHERE interests IS NOT NULL;

# 统计 空值null和空字符串的数量
SELECT 
	count(ifnull(interests,"")) as 数量
FROM demo02
WHERE ifnull(interests,"")="";

4.判断空字符串

= 或者<> 来进行处理空字符串

SELECT 
	name,
	interests
FROM demo02
WHERE interests = "";

# 用<> "" 同样会过来掉null
SELECT 
	name,
	interests
FROM demo02
WHERE interests <> "";

5.综合案例

# 统计interests字段中不包含“小”字的数量
SELECT 
	count(ifnull(interests,"")) as 数量
FROM demo02
WHERE 
ifnull(interests,"") NOT LIKE "%小%" 
;

# 输出interests字段中不包含"小"字的结果
SELECT 
	*
FROM demo02
WHERE 
	ifnull(interests,"") NOT LIKE "%小%" ;
# 或者
SELECT 
	*
FROM demo02
WHERE 
	interests NOT LIKE "%小%" 
or interests is null;

6.总结

• 做count聚合的时候空字符串算在其中，而null则不计算；
• 做模糊查找的[not] like的时候null会被过滤掉，空字符串不会；
• 计算字符串长度的时候null等于null，空字符串等于0；
• 条件中使用<> “” 不但会剔除空字符串，同样null也会剔除；
• 条件中使用 = “” 只会出现有空字符串的内容，但null的会被剔除；
• 判断NULL。用IS NULL 或者is not null，SQL语句函数中可以使用ifnull()函数来进行处理；
• 判断空字符串。= 或者<> 来进行处理空字符串
• 做条件筛选和count聚合时一定要查验是否有null,然后用ifnull(字段名,"")将其转换为空字符串。