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

標籤：欄位   wro   out   伺服器   global   儲存引擎   session   http   儲存   

今天在對一個項目在進行資料庫遷移後,進行測試時,出現了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.(良好的習慣)

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