欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-02 20:19:26
1建立数据。2简单对比null和空字符串。3判断NULL。4判断空字符串。5综合案例。6总结。...

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

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;

MYSQL的null(空值),还有空字符串的坑
从查询结果中我们可以看出空值null和空字符串的显示是不相同的

2.简单对比一下

  • 做count聚合的时候空字符串算在其中,而null则不计算;
  • 做模糊查找的[not] like的时候null会被过滤掉,空字符串不会;
  • 计算字符串长度的时候null等于null,空字符串等于0;
  • 条件中使用<> “” 不但会剔除空字符串,同样null也会剔除;
  • 条件中使用 = “” 只会出现有空字符串的内容,但null的会被剔除;
# 做count聚合的时候空字符串算在其中,而null则不计算
SELECT
	count( id ) AS 总行数,
	count(interests) AS 剔除null的行数
FROM demo02;

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

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

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

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

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

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

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

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

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

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

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

3.判断NULL

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

SELECT 
	name,
	interests
FROM demo02
WHERE interests IS NULL;

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

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

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

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

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

4.判断空字符串

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

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

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

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

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

5.综合案例

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

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

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

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

6.总结

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

本文地址:https://blog.csdn.net/weixin_41685388/article/details/110155680

相关标签: sql mysql