MySQL 中的 NULL 和 空字符(”) 区别
MySQL 中的 空字符串'' 和 空值NULL ，二者之间有哪些区别与应用场景，一直没深入研究。
1）空字符串''是字段中存储空字符('')
2）空值NULL是在字段中存储NULL值
注意：空字符('')和含有空格的字符(' ')是不一样的，前者长度0，后者长度1
1、查询占用空间的大小
select length(NULL), length(''), Length(' '), length('123'), length(0), length(123), length(-123);
mysql> select length(NULL), length(''), Length(' '), length('123'), length(0), length(123), length(-123);
+--------------+------------+-------------+---------------+-----------+-------------+--------------+
| length(NULL) | length('') | Length(' ') | length('123') | length(0) | length(123) | length(-123) |
+--------------+------------+-------------+---------------+-----------+-------------+--------------+
| NULL | 0 | 1 | 3 | 1 | 3 | 4 |
+--------------+------------+-------------+---------------+-----------+-------------+--------------+
由此可见，得出以下结论：
1）空值(NULL)的长度是NULL，不确定占用了多少存储空间，但是占用存储空间的
2）空字符串('')的长度是0，是不占用空间的
3）含一个空格的字符串(' ')长度为1，是占用空间的
4）含三个字符的字符串('123')长度为3，是占用空间的
5）数值0长度为1，是占用空间的
6）数值123长度为3，是占用空间的
7）数值-123长度为4，是占用空间的，负号占了1个空间
通俗的讲：
1）空字符串（''）就像是一个真空状态的杯子，什么都没有。
2）空值（NULL）就像是一个装满空气的杯子，含有东西。
二者虽然看起来都是空的、透明的，但是有着本质的区别。
2、NULL 的注意事项
1）插入NULL不一定为NULL
在MySQL数据库中，NULL是一个比较特殊的字段，在不同的情形下，NULL往往代表不同的含义。
在普通的字符型字段中，插入NULL就是NULL。
如果将一个NULL的数据插入到TimeStamp类型的字段中，NULL就不一定为NULL。比如，如果往TimeStamp类型的列中插入NULL值，则其代表的就是系统的当前时间。
如果往auto_increment属性的列中插入NULL值的话，则系统会插入一个自增的正整数。
如果在字符型数据的列中插入NULL的数据，则其插入的就是一个 'NULL'
2）NULL与空字符串''是不一样的类型
空字符串''不占空间大小，长度为0
NULL是要占空间的，长度为NULL，不确定是多少
二者就好比一杯真空（空字符串''）和一杯空气（空值NULL）的区别。
NULL 的统计方法
count(col)的结果会排除col为NULL的行，而不会排除空字符串
count(*)会被优化，总是直接返回总行数的，推荐此用法
NULL 的查询方法
1）单纯查NULL值列，则使用 is NULL , is not NULL 去查
2）单纯查空值('')列，则使用 =''，并且=''与=' ' 结果一样。空值(’’)可以使用 = , != , < , > , <> 等算术运算符。
3、MySQL 实践
1）创建数据库表
create table mytest.test_null_zero ( `id` bigint(20) unsigned not null auto_increment , `col` varchar(50) not null, `col_null` varchar(50), `col_default_null` varchar(50) default null, `col_default_0char` varchar(50) default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
再添加几个字段
alter table mytest.test_null_zero add column `col_int` int after `col_default_0char`, add column `col_int_null` int default null, add column `col_int_zero` int default 0, add column `col_dtime` TIMESTAMP NULL, add column `col_dtime_now` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
小结：创建表，修改表，添加字段
2）查看表结构
利用 MySQL Workbench 工具查看
上图可见，总结如下：
1）`col` varchar(50) not null 默认值空白，即默认值不为 NULL
2）`col_null` varchar(50) 字符串型不声明默认值，则默认值为NULL
3）`col_default_null` varchar(50) default null 声明默认值为NULL
4）`col_default_0char` varchar(50) default '' ，声明默认值为空字符串 ''
5）add column `col_int` int after `col_default_0char` ，整型不声明默认值，则默认值为NULL
6）add column `col_dtime` TIMESTAMP NULL 说明 TIMESTAMP 可以添加两个，默认分别为 NULL 和 当前日期，但 TIMESTAMP 的默认值不能两个同时当前时间
小结：字符串型、整型等不声明默认值，默认则为 NULL
3）插入测试数据
根据最新的表字段结构，插入测试数据
insert into mytest.test_null_zero values (1, 'abc', 'abc', 'abc', 'abc', 100, 100, 100, '2020-05-20 12:25:48', '2020-05-20 12:25:48'), (2, '', ' ', null, null, 100.2, 100.5, 100.8, '2020-05-05 12:25:48', '2020-05-20'), (3, ' ', ' ', 0, null, 100.2, 100.5, 100.8, '2020-05-05', '2020-05-20'), (4, '123', null, 0, null, -1, 0, 1, now(), now()), (null, 'xyz', null, null, null, null, null, null, null, null), (null, 'none', null, null, null, -123, 123, 123456789, null, null);
查询结果如下：
select * from mytest.test_null_zero;
上图自增字段id，为什么不连续呢，如何知道当前id自增到了多少呢，查看表状态
show table status;
mysql> show table status; +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | student | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | NULL | 2020-06-24 16:16:10 | 2020-06-24 16:16:10 | NULL | utf8_general_ci | NULL | | | | test_field_type | InnoDB | 10 | Dynamic | 7 | 2340 | 16384 | 0 | 0 | 0 | NULL | 2020-06-28 20:41:32 | 2020-06-28 20:55:22 | NULL | utf8_general_ci | NULL | | | | test_null_zero | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | 73 | 2020-06-29 16:05:49 | 2020-06-29 16:09:38 | NULL | utf8_general_ci | NULL | | | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
上图，可见我们的测试表名 test_null_zero 对应的 Auto_increment 为73
73是什么含义呢？已经插入的最后一行id最大值，还是下次新插入一条的id值？
通过函数 last_insert_id() 获取刚刚自增过的值（已经自增存在，不是下次的自增值）
mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 72 | +------------------+
为了验证，我们最后用手动添加一条记录
insert into mytest.test_null_zero values (null, 'love', null, null, null, -100, 0, 100, null, null);
再次查询结果如下：
select * from mytest.test_null_zero;
上图可见，自增字段id 的数值73，值下次插入记录的id值！为什么呢？68+4=72，引申id不从0，而从1开始！
由上图，总结如下：
1）(2, '', ' ', null, null, 100.2, 100.5, 100.8, '2020-05-05 12:25:48', '2020-05-20') ，timestamp类型不设置时分秒，不报错，自动补全默认时分秒为 00:00:00
2）(4, '123', null, 0, null, -1, 0, 1, now(), now())，timestamp 类型可以插入 now() 函数值，即当前时间
3）(null, 'xyz', null, null, null, null, null, null, null, null) ，id自增、timestamp设定了默认值为 CURRENT_TIMESTAMP，则二者插入null值时，会自动分别插入id当前自增值、col_dtime_now当前系统时间值，其中 `col_dtime_now` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
4）NULL 和 空字符('') 的应用
为了简洁，下面的示例，我们只查五个字段
查询出全部内容
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero; +----+------+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+------+----------+------------------+-------------------+ | 1 | abc | abc | abc | abc | | 2 | | | NULL | NULL | | 3 | | | 0 | NULL | | 4 | 123 | NULL | 0 | NULL | | 67 | xyz | NULL | NULL | NULL | | 68 | none | NULL | NULL | NULL | | 73 | love | NULL | NULL | NULL | +----+------+----------+------------------+-------------------+ 7 rows in set (0.00 sec)
查询 is null 和 isnull( fieldname ) = 1
方式1：查询出 null 字段
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where col_null is null;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where col_null is null; +----+------+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+------+----------+------------------+-------------------+ | 4 | 123 | NULL | 0 | NULL | | 67 | xyz | NULL | NULL | NULL | | 68 | none | NULL | NULL | NULL | | 73 | love | NULL | NULL | NULL | +----+------+----------+------------------+-------------------+ 4 rows in set (0.00 sec)
方式2：isnull( fieldname ) = 1
isnull( fieldname) 函数是用来判断字段 fieldname 是否为空，1 - null ，0 - 非null，例如空串''和有数据都为0；
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where isnull(col_null) = 1;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where isnull(col_null) = 1; +----+------+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+------+----------+------------------+-------------------+ | 4 | 123 | NULL | 0 | NULL | | 67 | xyz | NULL | NULL | NULL | | 68 | none | NULL | NULL | NULL | | 73 | love | NULL | NULL | NULL | +----+------+----------+------------------+-------------------+ 4 rows in set (0.00 sec)
查询 not null
方式1：查询出 not null 字段
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where col_null is not null;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where col_null is not null; +----+-----+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+-----+----------+------------------+-------------------+ | 1 | abc | abc | abc | abc | | 2 | | | NULL | NULL | | 3 | | | 0 | NULL | +----+-----+----------+------------------+-------------------+ 3 rows in set (0.00 sec)
方式2：isnull( fieldname ) = 0
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where isnull(col_null) = 0;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where isnull(col_null) = 0; +----+-----+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+-----+----------+------------------+-------------------+ | 1 | abc | abc | abc | abc | | 2 | | | NULL | NULL | | 3 | | | 0 | NULL | +----+-----+----------+------------------+-------------------+ 3 rows in set (0.00 sec)
查询字符串长度
select * from mytest.test_null_zero;
上图，col字段的第二行为空字符串'', 第三行为空格' ' ； col_null字段的第二行为空格' '，第三行为空格 ' '
情景一）字符串长度 >= 1
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where length( col_null ) >= 1;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where length( col_null ) >= 1; +----+-----+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+-----+----------+------------------+-------------------+ | 1 | abc | abc | abc | abc | | 2 | | | NULL | NULL | | 3 | | | 0 | NULL | +----+-----+----------+------------------+-------------------+ 3 rows in set (0.00 sec)
说明：col_null 字段的 null 的长度为小于1
情景二）字符串长度 = 1
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where length( col ) >= 1;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where length( col ) >= 1; +----+------+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+------+----------+------------------+-------------------+ | 1 | abc | abc | abc | abc | | 3 | | | 0 | NULL | | 4 | 123 | NULL | 0 | NULL | | 67 | xyz | NULL | NULL | NULL | | 68 | none | NULL | NULL | NULL | | 73 | love | NULL | NULL | NULL | +----+------+----------+------------------+-------------------+ 6 rows in set (0.00 sec)
说明：col字段的第二行为空字符串''，其长度为0，故排除了
情景三）去除空白后的字符串长度 = 1
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where length( trim(col) ) >= 1;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero where length( trim(col) ) >= 1; +----+------+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+------+----------+------------------+-------------------+ | 1 | abc | abc | abc | abc | | 4 | 123 | NULL | 0 | NULL | | 67 | xyz | NULL | NULL | NULL | | 68 | none | NULL | NULL | NULL | | 73 | love | NULL | NULL | NULL | +----+------+----------+------------------+-------------------+ 5 rows in set (0.00 sec)
说明：col字段的第二行空字符串''，第三行空格' '，在用 trim(col) 去除空白后均为0，故排除了
情景四）统计表的行数
mysql> select count(1) from mytest.test_null_zero; +----------+ | count(1) | +----------+ | 7 | +----------+ mysql> select count(*) from mytest.test_null_zero; +----------+ | count(*) | +----------+ | 7 | +----------+ mysql> select count(col) from mytest.test_null_zero; +------------+ | count(col) | +------------+ | 7 | +------------+ mysql> select count(col_null) from mytest.test_null_zero; +-----------------+ | count(col_null) | +-----------------+ | 3 | +-----------------+
说明：count(col_null) 统计行数，发现 null 并不参与行数计算
情景五）null 计算
select id, col, col_null, col_default_null+1, col_default_0char from mytest.test_null_zero;
mysql> select id, col, col_null, col_default_null+1, col_default_0char from mytest.test_null_zero; +----+------+----------+--------------------+-------------------+ | id | col | col_null | col_default_null+1 | col_default_0char | +----+------+----------+--------------------+-------------------+ | 1 | abc | abc | 1 | abc | | 2 | | | NULL | NULL | | 3 | | | 1 | NULL | | 4 | 123 | NULL | 1 | NULL | | 67 | xyz | NULL | NULL | NULL | | 68 | none | NULL | NULL | NULL | | 73 | love | NULL | NULL | NULL | +----+------+----------+--------------------+-------------------+ 7 rows in set, 1 warning (0.00 sec)
说明：
null 不参与计算，仍为null，空值（null）是不能参与任何计算，因为空值参与任何计算都为空
字符串 + 1 默认把字符串看做0，例如：第一行 'abc'+1=1，第三、四行 '0' + 1 = 1
所以，当程序业务中存在计算的时候，如果null非要参与计算，需使用ifnull函数，将null转换为''才能正常计算。
情景六）null 求和
select sum(col_int) from mytest.test_null_zero;
mysql> select sum(col_int) from mytest.test_null_zero; +--------------+ | sum(col_int) | +--------------+ | 76 | +--------------+ 1 row in set (0.00 sec)
说明：100 + 100 + 100 -1 -123 -100 = 76，空值null 都不参与计算！
情景七）修改 null、''、' ' 为指定值
先查出表的所有行内容
select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero;
mysql> select id, col, col_null, col_default_null, col_default_0char from mytest.test_null_zero; +----+------+----------+------------------+-------------------+ | id | col | col_null | col_default_null | col_default_0char | +----+------+----------+------------------+-------------------+ | 1 | abc | abc | abc | abc | | 2 | | | NULL | NULL | | 3 | | | 0 | NULL | | 4 | 123 | NULL | 0 | NULL | | 67 | xyz | NULL | NULL | NULL | | 68 | none | NULL | NULL | NULL | | 73 | love | NULL | NULL | NULL | +----+------+----------+------------------+-------------------+ 7 rows in set (0.00 sec)
然后修改 null、0 的显示值
select id, col, col_null,
case
when ( isnull(col_default_null) = 1 ) || ( length( trim(col_default_null) ) = 0 ) then 'none'
when col_default_null = '0' then 'zero'
else col_default_null
end
as col_default_null, col_default_0char
from mytest.test_null_zero;
执行出结果如下：
mysql> select id, col, col_null,
-> case
-> when ( isnull(col_default_null) = 1 ) || ( length( trim(col_default_null) ) = 0 ) then 'none'
-> when col_default_null = '0' then 'zero'
-> else col_default_null
-> end
-> as col_default_null, col_default_0char
-> from mytest.test_null_zero;
+----+------+----------+------------------+-------------------+
| id | col | col_null | col_default_null | col_default_0char |
+----+------+----------+------------------+-------------------+
| 1 | abc | abc | abc | abc |
| 2 | | | none | NULL |
| 3 | | | zero | NULL |
| 4 | 123 | NULL | zero | NULL |
| 67 | xyz | NULL | none | NULL |
| 68 | none | NULL | none | NULL |
| 73 | love | NULL | none | NULL |
+----+------+----------+------------------+-------------------+
7 rows in set (0.00 sec)
说明：
1）null/''/' ' 全部转化为 'none'
2）'0' 全部转化成了 'zero'
3）其他的原样返回（else col_default_null）
小结：
由上实践可见，当统计数量的时候，空值（null）并不会被当成有效值去统计。
同理，sum()求和的时候，null也不会被统计进来，这样就能理解，为什么null计算的时候结果为空，而sum()求和的时候结果正常了。
以上对比，算得上是程序中经常会遇到但又不容易注意的问题。
网络上，针对空值（null）和空字符串（''）两个值，有一种说法会比较好理解：
1）在存电话号码字段中，空值（null）代表你没有电话，而空字符串(”)则代表，你有电话但是没有填写。或者理解，填写了又删除掉。
2）null是尚未定义的值，表示未知。而空字符串'' 确定为一个空字符串。所以未知的值，无法进行各种比较（大于，小于，等于），也不能用于计算（加减乘除）
我的理解是，不考虑是否占用内存。null代表什么都没有，''代表的就是一个空白的字符串。
所以，null什么都没有就不能用于比较、统计、计算。无论怎么理解，都需要记住这两个值的区别。
4、总结
1、空值''不占空间，NULL值占空间。当字段不为NULL时，也可以插入空值。
2、当使用 is not NULL 或者 is NULL 时，只能查出字段中不为NULL或者为 NULL 的，不能查出空值''。
3、判断NULL 用 is NULL 或者 is not NULL，SQL 语句函数中可以使用IFNULL()函数来进行处理，判断空字符用 ='' 或者 <>'' 来进行处理。
4、在进行count()统计某列的记录数的时候，如果采用的NULL值，会别系统自动忽略掉，但是空值('')是会进行统计到其中的。
5、实际场景是使用NULL值，还是空值('')，根据实际业务来进行区分。推荐空值（''）
6、数据库表字段设计时，最好都给出一个默认值，例如：字符串默认为空值''，int 默认为0
参考推荐：
MySQL 中 distinct 和 group by 性能比较
MySQL中distinct和group by过滤删除重复行
MySQL 中 insert ignore into, replace into 用法总结
Python连接MySQL、MongoDB、Redis、memcache
版权所有: 本文系米扑博客原创、转载、摘录，或修订后发表，最后更新于 2024-05-14 14:36:15
侵权处理: 本个人博客，不盈利，若侵犯了您的作品权，请联系博主删除，莫恶意，索钱财，感谢！