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;
从查询结果中我们可以看出空值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(字段名,"")将其转换为空字符串。
本文地址:https://blog.csdn.net/weixin_41685388/article/details/110155680
推荐阅读
-
确保所有的非空(Non-NULL)值都是唯一的_MySQL
-
php中0,null,empty,空,false,字符串关系的详细介绍
-
Thinkphp中如何表达MYSQL中的某字段不为空is not null
-
MySQL null与not null和null与空值''''的区别详解
-
MySQL的空值和NULL区别
-
PostgreSQL空值null参与运算的处理方法_PHP教程
-
SQLserver查询数据类型为ntext是空或NULL值的方法
-
C#三种判断数据库中取出的字段值是否为空(NULL) 的方法
-
SQLserver查询数据类型为ntext是空或NULL值的方法
-
C#三种判断数据库中取出的字段值是否为空(NULL) 的方法