mysql Field 'xxx' doesn't have a default value

今天在对一个项目在进行数据库迁移后,进行测试时,出现了Field 'xxx' doesn't have a default value的错误,而这个错误在迁移前从未出现过.后来经过查询手册等资料,发现错误的原因是配置不同:出错的mysql服务器,在系统变量sql_mode里,多了个参数: STRICT_TRANS_TABLES,去掉后就不报错了.今天接触了很多知识点,我要一点点捋.

参考资料

sql_mode

strict SQL mode

首先了解一些mysql系统变量sql_mode.

根据sql_mode这个系统变量(system variable),Mysql服务器可以在不同的SQL模式下进行操作,并且对于不同客户端,可以分配给多种多样的SQL模式,这些模式可以影响mysql支持哪些语法,影响mysql执行的数据有效性检测.

有些mode很重要,比如我今天遇到的STRICT_TRANS_TABLES

STRICT_TRANS_TABLES就是对事务型存储引擎开启严格的SQL模式(strict sql mode),那什么叫严格SQL模式?

关于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, NULLis inserted if the value is missing.)

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings (see Section 13.7.5.41, “SHOW WARNINGS Syntax”). In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE.

说完了严格SQL模式,就略懂啦!报错的原因就是:第二台服务器开启了STRICT_TRANS_TABLES,当插入数据中有"value missing"的时,mysql服务器很严格,就会报错

从严格上讲,应该重新定义表结构,定义为非空的,如果允许有默认值,就定义.但是,本项目对于默认值没有太高的要求,于是,最高效的方法是该配置

Setting the SQL Mode

The default SQL mode is empty (no modes set).

To set the SQL mode at server startup:

use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas.

To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.(注意,在[mysqld]下面添加)

To change the SQL mode at runtime:

set the global or session sql_mode system variable using a SET statement:

SET GLOBAL sql_mode = 'modes';#全局生效用这个

SET SESSION sql_mode = 'modes';

Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time.