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

关于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.(良好的习惯)

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.

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

标签：字段   wro   out   服务器   global   存储引擎   session   http   存储