MySQL案例：not null和default的一个小问题
原创
MySQL案例：not null和default的一个小问题
原创
前言
前几天，有位童鞋咨询我一个问题，如果表新增一个not null字段、不指定default，那么这个操作能成功吗？当时我不假思索地回答会直接报错，结果当然就是被啪啪打脸。后续回顾这个问题，做出此错误回答的原因有那么几个：一是not null和不指定default两者本身是冲突的；二是Oracle相关经验告诉我会报错；三是MySQL开发规范一般要求指定default；接下来我们详细记录一下这个案例。
Oracle是如何处理的？
首先，我们看一下Oracle是如何处理这种情况的。
（1）查看表结构
SQL> select dbms_metadata.get_ddl('TABLE','SBTEST','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','SBTEST','TEST')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."SBTEST"
( "ID" NUMBER NOT NULL ENABLE,
"K" NUMBER NOT NULL ENABLE,
"C" CHAR(120) NOT NULL ENABLE,
"PAD" CHAR(60) NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"（2）当表数据为空时，新增一个not null字段、不指定default，执行成功；其实这也比较好理解，因为没有数据，not null约束校验通过，字段新增成功，not null只会约束后续数据修改
SQL> select count(*) from sbtest;
COUNT(*)
----------
0
SQL> alter table sbtest add a number not null;
Table altered.（3）当表数据不为空时，新增一个not null字段、不指定default，报错ORA-01758；报错信息也非常清晰，因为有数据，not null约束校验不通过，字段无法新增成功
SQL> select count(*) from sbtest;
COUNT(*)
----------
10
SQL> alter table sbtest add a number not null;
alter table sbtest add a number not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column从上面的测试可以看出来，Oracle对于新增not null字段、不指定default的处理方式，还是非常严谨、符合标准的。
MySQL是如何处理的？
看完了Oracle，我们再来看一下MySQL是如何处理的。
（1）查看表结构
*************************** 1. row ***************************
Table: sbtest
Create Table: CREATE TABLE `sbtest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 row in set (0.00 sec)（2）当表数据为空时，新增一个not null字段、不指定default，和Oracle一样执行成功
mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> alter table sbtest add a int not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0（3）当表数据不为空时，新增一个not null字段、不指定default，也执行成功；那么这两个冲突的操作，为什么能够成功呢？这里可以有两个猜测：一是忽略了not null约束，二是强制指定了default
mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> alter table sbtest add a int not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0（4）然后，我们再查一下详细数据，可以发现，MySQL指定了0作为默认值，这也证实了上述猜测
mysql> select * from sbtest;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+---+
| id | k | c | pad | a |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+---+
| 1 | 3718516 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 | 0 |
| 2 | 5934152 | 05879259692-02024081996-76022818191-82933803603-81845875017-31928300264-16934042125-67052432228-92123768050-95121478647 | 79362588344-09017007031-35411714211-52054317597-26283585383 | 0 |
| 3 | 3082941 | 16663631067-14838697989-64358398406-16917073223-47713406217-78812818873-46576803224-61955865221-97956448087-49259465660 | 52468633478-82076178857-68496668227-22002343387-57481185690 | 0 |
| 4 | 3371969 | 88814841367-98767826715-60494483894-21079440118-10718401291-33837777683-38627243809-45118876335-22718290515-23426562159 | 79384524915-41356971023-74955511882-92798558059-93482034638 | 0 |
| 5 | 4988307 | 87263726817-94340135738-17557808545-78801760644-04111877554-34439976372-12880746592-22897263227-59023974220-20489960934 | 34521232779-81152534024-26776427142-43102257779-39982840334 | 0 |
| 6 | 6601147 | 60685942156-14883401017-75912296178-34847270244-98656403671-62539148212-66176221264-72102213443-13108032859-29810653635 | 95461233997-54685588475-76951480327-08622771838-61221370037 | 0 |
| 7 | 5030295 | 37161750159-64469011488-79357028225-41966759109-28901419994-00098250192-00948263005-05722282729-31248925570-37623248811 | 17963809886-58076981282-57193684531-83042641085-66229847928 | 0 |
| 8 | 6107250 | 56275464643-39136268261-86841165941-68712814563-89105006669-69794071600-71049942131-52283289695-61545685528-68757241042 | 45213425692-80254356530-25945739949-45569971743-21089826446 | 0 |
| 9 | 4986827 | 47508604409-87100663829-59520169019-60773852233-25192900648-41585484092-99809863381-30396901315-54193919798-54584773977 | 13622125480-90103407092-22928184340-50945574854-18069333369 | 0 |
| 10 | 5046588 | 66735372336-31067191528-40688810467-42388152620-36963372403-70446940149-52664058292-51291459264-39838509024-39953981870 | 93993879411-89576069455-53989496194-63022727287-89271530611 | 0 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+---+
10 rows in set (0.00 sec)（5）接下来，我们测试一下其他常见数据类型，汇总结果如下表
数据类型 | 默认值 |
|---|---|
TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT/FLOAT/DOUBLE/DECIMAL | 0 |
CHAR/VARCHAR/TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB/TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT | '' |
DATE | 0000-00-00 |
TIMESTAMP | 当前时间 |
从上面的测试可以看出来，MySQL对于新增not null字段、不指定default的处理方式，是不够严谨的；数据库自己指定一个default，是有可能会造成一些脏数据的，毕竟类似0、当前时间这些数值，本身可能是具有业务意义的；因此，这也是为什么MySQL开发规范一般要求指定default
总结
对于表新增not null字段、不指定default的处理方式，Oracle和MySQL是不一样的，具体总体如下：
（1）对于Oracle而言，如果表为空，可以执行成功；如果表不为空，会报错ORA-01758
（2）对于MySQL而言，不管表是否为空，均可以执行成功；如果表不为空，会根据数据类型，为现存数据指定一个default
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
