MySQL 5.7 的 ERROR 1364 错误和SQL_MODE缺省值

最近迁移 MySQL 的数据库到新的环境，发现原有的程序工作不正常，就有了这一则小故事。

数据库的版本是 5.7.35：

MySQL [enmotech]> select version();
+-----------+
| version() |
+-----------+
| 5.7.35    |
+-----------+
1 row in set (0.17 sec)

在 insert 数据时遇到如下错误：

MySQL [enmotech]> INSERT INTO guestbook (icon,username,touser,sex,comefrom,email,qq,homepage,dateline,pagetext,useros,ipaddress) VALUES (‘icons/43.gif’,‘eygle’,’’,‘0’,’’,’’,’’,’’,‘7399713’,‘这是一个测试’,‘Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/195.20.48.169 Safari/537.36’,‘21.20.9.36’);

ERROR 1364 (HY000): Field 'replytext' doesn't have a default value

检查建表语句，我们发现 replytext 是 NOT NULL 但是没有缺省值，当以上 Insert 不包含该字段时，出现错误：

CREATE TABLE `guestbook` (
  `gbid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `icon` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `username` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `touser` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `sex` smallint(6) NOT NULL DEFAULT '0',
  `comefrom` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `qq` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `homepage` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `dateline` int(11) NOT NULL DEFAULT '0',
  `pagetext` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `reply` int(11) NOT NULL DEFAULT '0',
  `replyicon` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `replyname` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `replytext` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `useros` varchar(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ipaddress` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`gbid`)
) ENGINE=InnoDB AUTO_INCREMENT=46105 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

我们尝试修改该字段给定一个缺省空值，发现不允许：

MySQL [enmotech]> alter table guestbook modify column replytext mediumtext NOT NULL DEFAULT '';
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'replytext' can't have a default value

可以将该字段修改为允许为空：

MySQL [enmotech]> alter table guestbook modify column replytext mediumtext NULL;
Query OK, 0 rows affected (0.85 sec)
Records: 0  Duplicates: 0  Warnings: 0

那么为什么原环境是没有问题的呢？

注意，这是因为系统的参数设置，给出了一个 Warning 警告，但是没有抛出异常（以下来自源环境）：

MySQL [eygle_com]>  INSERT INTO guestbook (icon,username,touser,sex,comefrom,email,qq,homepage,dateline,pagetext,useros,ipaddress) VALUES ('icons/43.gif','eygle','','0','','','','','7399713','这是一个测试','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/195.20.48.169 Safari/537.36','21.20.9.36');
Query OK, 1 row affected, 1 warning (0.00 sec)

源环境没有设置约束：

MySQL [eygle_com]> select @@global.sql_mode;
+------------------------+
| @@global.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

而新的环境,自5.7开始，有了更多更严的参数设置：

MySQL [enmotech]> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+