mysql error 1364 Field doesn’t have a default values

问题描述

前一阵在做一个系统迁移工作， mysql数据库版本进行了升级，

数据库mariadb版本原有版本 5.5.56，升级后版本10.3.9.

$ mysql -V

or

$ mysql
> select version();

升级后原有功能报1364错误， 原因由于数据库中的SQL mode 默认定义调整。

可以通过以下命令进行看出

$mysql -u -e "select @@sql_mode"

mysql 5.7版本以后默认打开了打开了STRICT_TRANS_TABLES。

可以通过配置文件修改，设置为””则为取消

[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

或者使用sql, 多个模式间的用逗号分割不能有空格。

>SET session sql_mode = 'modes...';

立即生效设置当前session sql_mode

sql_mode 

mysql 文档关于STRICT_TRANS_TABLES描述。

Strict SQL Mode
Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

严格模式控制MySQL如何处理数据如插入或更新中的无效值。例如，它可能具有错误的列数据类型，或者可能超出范围。当要插入的新行不包含一个非NULL列的值时，其定义中没有明确的默认子句。（对于null列，如果丢失了值，则将插入空。）严格模式还会影响诸如创建表之类的DDL语句.

处理过程

下面整理一下处理过程

MariaDB > desc table_xxx;
+-------------+---------------------+------+-----+---------------------+-------------------------------+
| Field       | Type                | Null | Key | Default             | Extra                         |
+-------------+---------------------+------+-----+---------------------+-------------------------------+
| id          | int(10)             | NO   | PRI | NULL                | auto_increment                |
| colume_a    | varchar(1000)       | NO   |     | NULL                |                               |
+-------------+---------------------+------+-----+---------------------+-------------------------------+


MariaDB > select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB > insert into table_xxx( colume_a ) value('50000');
ERROR 1364 (HY000): Field 'data_type' doesn't have a default value
MariaDB > set Global sql_mode='';
Query OK, 0 rows affected (0.000 sec)

MariaDB > insert into table_xxx( colume_a ) value('50000');
ERROR 1364 (HY000): Field 'colume_a ' doesn't have a default value
MariaDB > select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB > set session sql_mode='';
Query OK, 0 rows affected (0.000 sec)

MariaDB > select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.000 sec)

MariaDB > insert into table_xxx( colume_a ) value('50000');
Query OK, 1 row affected, 1 warning (0.003 sec)

MariaDB > select * from table_xxx where id='50000';
+------------+------------+
| id         |  column_a  |
+------------+------------+
| 50000      |            |
+------------+------------+

参考及引用

https://stackoverflow.com/questions/15438840/mysql-error-1364-field-doesnt-have-a-default-values

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

图片from 陳丁光