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 数据类型

MySQL 查询语句取整数或小数

MySQL 正则表达式查询

MySQL 时间函数加减计算

MySQL 日期和时间函数详解

MySQL 连接查询join详解

MySQL 联合查询union详解

MySQL 函数 group_concat

MySQL 中 case when 语句用法

MySQL 中 distinct 和 group by 性能比较

MySQL中distinct和group by过滤删除重复行

MySQL 数据库 User表权限以及用户授权详解

MySQL 字段中区分字符串大小写的解决方法

MySQL 字段的添加前缀、去掉前缀等字符串操作

MySQL中大于小于,IN,OR,BETWEEN性能比较

PHP MySQL中 uft-8中文编码乱码的解决办法

MySQL 中 insert ignore into, replace into 用法总结

Python创建MySQL数据库/表/存储过程

MySQL 存储引擎 TokuDB

单机开启多个 MySQL 实例

MySQL 内存调优

MySQL 执行 SQL 及慢查询监控

MySQL: 一个参数引起的宕机血案

淘宝分享:跳出MySQL的10个大坑

MySQL 删除数据后物理空间未释放

MySQL 删除100G大表中的绝大部分数据

Python连接MySQL、MongoDB、Redis、memcache