# mysql error 1364 Field doesn't have a default values
Date: 2022-04-30


##### **问题描述**

前一阵在做一个系统迁移工作， 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 [**陳丁光**](https://www.facebook.com/groups/474930279315210/user/100010173011046/?__cft__[0]=AZVKE-m4hKqfgWChwlcsMe2mmfR32rYq_bh4k689mRkkEWAvNQhsvG5p3wKlIHlAQXCH3YoyhuORbAUxX77ZQHJ83XilKIL8V20cK4fSfk8ZDVCtZKBWcb618WE4JPWD-6UwyirEXXdFDzuhHp5WBGn9AV_uk0BdaMN6l_cr42t71m2SfHbgteGuHUGiMi2BR-4&__tn__=-UC%2CP-R)