关于null值的一个小问题

AsiaYe

发布于 2019-11-06 16:50:47

5440

发布于 2019-11-06 16:50:47

文章被收录于专栏：DBA随笔 DBA随笔DBA随笔

线上操作的一个小问题

今天在处理业务的时候，碰到了一个小问题，这里简单记录下。

一个业务方给了一个SQL，要让在线上执行，具体的SQL抽象完成之后是这样的：

alter table tbl_name add col_name default null comment '表的注释';

可以看到，是一个给表增加字段的操作，然后我按照SQL中的内容执行完毕，过了一会儿，说是需要修改一下这个字段，把这个字段修改成not null类型的，不能写成default null，给出的SQL如下：

alter table tbl_name modify col_name not null comment '表的注释';

执行这个SQL的时候，碰到了一个错误：

ERROR 1138 (22004): Invalid use of NULL value

为了好模拟，我把这个场景在测试环境上重现了一遍：

mysql--dba_admin@127.0.0.1:yeyz 21:57:40>>select * from tbl_test;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 21:58:14>>alter table tbl_test add name varchar(10) default null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql--dba_admin@127.0.0.1:yeyz 21:58:19>>select * from tbl_test;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 21:58:33>>alter table tbl_test modify name varchar(10) not null;
ERROR 1138 (22004): Invalid use of NULL value

报出这个错误，第一反应是由于name的值是NULL导致的，我尝试修改了一下name字段的属性，发现问题仍然存在：

mysql--dba_admin@127.0.0.1:yeyz 21:58:54>>show create table tbl_test;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                    |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_test | CREATE TABLE `tbl_test` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 21:59:13>>alter table tbl_test alter name drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql--dba_admin@127.0.0.1:yeyz 21:59:30>>show create table tbl_test;
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                       |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| tbl_test | CREATE TABLE `tbl_test` (
  `id` int(11) NOT NULL,
  `name` varchar(10),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 21:59:34>>alter table tbl_test modify name varchar(10) not null;
ERROR 1138 (22004): Invalid use of NULL value

于是更加确认是由于name字段的NULL值导致的。又重新做了一次实验，如下：

mysql--dba_admin@127.0.0.1:yeyz 21:59:55>>truncate table tbl_test;
Query OK, 0 rows affected (0.01 sec)

mysql--dba_admin@127.0.0.1:yeyz 22:01:02>>insert into tbl_test values (1,'NULL');
Query OK, 1 row affected (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 22:01:08>>alter table tbl_test modify name varchar(10) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

这次把表里面的数据都清除掉，然后重新插入一条数据，其中name字段的值是'NULL'字符串，然后再次修改表结构，发现表结构修改成功。这就证明，确实是由于数据中存在null值，而修改的语句又是要把这个字段改为not null选项，所以发生了冲突，mysql针对这种冲突就会报一个'null使用不合法'的错误。这个问题比较好模拟，在线上操作的时候，由于这个字段是刚刚添加的，字段中还没有具体的值，都被设置为default null了，所以我直接drop掉这个字段了，重新alter table add了一下，就通过了，这里，想要提出的问题是：

第1.如果这个字段中已经有了一部分null值，然后我们应该如何去把这个字段的类型改为not null？(总不能像我一样直接truncate表吧)

第2.当我们看到一个字段的值是null的时候，我们应该如何判断它的内容是'NULL'字符串，还是真的是null值？

关于第一个问题，可能我们需要利用一个update操作，利用is null作为where匹配条件去先把这些null值改为空值，然后再进行alter操作。如下：

mysql--dba_admin@127.0.0.1:yeyz 22:26:53>>select *from tbl_test;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 22:27:14>>alter table tbl_test modify name varchar(10) not null;
ERROR 1138 (22004): Invalid use of NULL value

mysql--dba_admin@127.0.0.1:yeyz 22:28:36>>update tbl_test set name='' where name is null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql--dba_admin@127.0.0.1:yeyz 22:28:47>>alter table tbl_test modify name varchar(10) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

关于第二个问题，我们可能需要使用is null作为where条件去表中过滤。类似下面这样：

mysql--dba_admin@127.0.0.1:yeyz 22:26:53>>select *from tbl_test;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
|  2 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 22:27:02>>select *from tbl_test where name is null;
+----+------+
| id | name |
+----+------+
|  2 | NULL |
+----+------+
1 row in set (0.00 sec)

说到了null值，我们在来多说一点。

null值与空值

mysql中null值与空值是两个不同的概念，来看官方的解释：

首先，我们要搞清楚“空值” 和 “NULL” 的概念：

1、空值是不占用空间的

2、mysql中的NULL其实是占用空间的，下面是来自于MYSQL官方的解释：

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

也就是说，null值是需要占用空间的。首先我们通过实验来证明二者确实是不同的：

mysql 22:28:51>>create table test (aa varchar(10) not null,bb varchar(10) null);
Query OK, 0 rows affected (0.01 sec)

mysql 22:32:40>>INSERT INTO `test` VALUES (null,1); 
ERROR 1048 (23000): Column 'aa' cannot be null
mysql 22:32:54>>INSERT INTO `test` VALUES ('',1); 
Query OK, 1 row affected (0.00 sec)

从上面的例子不难看出，当我们设置aa字段是not null的时候，第一条记录为null的值无法插入，而第二条记录为''的记录可以插入，这就说明null值和空值是不一样的。

此时我们给表中重新插入两条记录，可以看到：

mysql--dba_admin@127.0.0.1:yeyz 22:33:06>>INSERT INTO `test` VALUES ('', NULL);  
Query OK, 1 row affected (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 22:35:19>>INSERT INTO `test` VALUES ('1', '2');  
Query OK, 1 row affected (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 22:35:22>>select * from test;
+----+------+
| aa | bb   |
+----+------+
|    | 1    |
|    | NULL |
| 1  | 2    |
+----+------+
3 rows in set (0.00 sec)

可以看到数据现在有三条了，如果我们要查找其中aa"不为空"的记录，需要使用哪种方法呢？

mysql--dba_admin@127.0.0.1:yeyz 22:35:34>>SELECT * FROM `test` WHERE aa IS NOT NULL 
    -> ;
+----+------+
| aa | bb   |
+----+------+
|    | 1    |
|    | NULL |
| 1  | 2    |
+----+------+
3 rows in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 22:36:10>>SELECT * FROM `test` WHERE aa <> ''; 
+----+------+
| aa | bb   |
+----+------+
| 1  | 2    |
+----+------+
1 row in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyz 22:38:12>>SELECT * FROM `test` WHERE aa != null; 
Empty set (0.00 sec)

这里我们可以看到：

第一种方法返回了三条值，条件是is not null，这个条件的意思是说不为null值得记录有三条；

第二种方法返回了一条记录，这个条件是<> ''也就是不为空值的记录;

需要注意的是第三条，null" 表示什么也不是，用“=、>、< ...” 所有的判断，结果都是false，"！= null"的判断会永远返回0行，但没有语法错误。所以一般不要使用。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-03-12，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server