問題描述

我運行的是 MySql Server 5.7.11 和這句話:

I'm running MySql Server 5.7.11 and this sentence:

updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

不起作用.給出錯誤:

ERROR 1067 (42000): Invalid default value for 'updated'

但是以下:

updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00'

剛剛好.

DATE 的情況相同.

The same case for DATE.

作為旁注，它在MySQL 文檔:

DATE 類型用于具有日期部分但沒有時間部分的值.MySQL 以 'YYYY-MM-DD' 格式檢索和顯示 DATE 值.支持的范圍是1000-01-01"到9999-12-31".

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

即使他們也說:

無效的 DATE、DATETIME 或 TIMESTAMP 值將轉(zhuǎn)換為適當(dāng)類型的零"值('0000-00-00' 或 '0000-00-00 00:00:00').

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the "zero" value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

還考慮到 MySQL 文檔中的第二個引用，誰能告訴我為什么會出現(xiàn)該錯誤?

Having also into account the second quote from MySQL documentation, could anyone let me know why it is giving that error?

推薦答案

該錯誤是由于 sql 模式，根據(jù)最新的 MYSQL 5.7 文檔可以是嚴(yán)格模式

The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation

MySQL 文檔 5.7 說:

嚴(yán)格模式會影響服務(wù)器是否允許0000-00-00"作為有效日期:如果未啟用嚴(yán)格模式，則允許使用 '0000-00-00' 并且插入不會產(chǎn)生警告.如果啟用了嚴(yán)格模式，則不允許使用 '0000-00-00' 并且插入會產(chǎn)生錯誤，除非也給出了 IGNORE.對于 INSERT IGNORE 和 UPDATE IGNORE，允許使用0000-00-00"并且插入會產(chǎn)生警告.

Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

檢查MYSQL模式

SELECT @@GLOBAL.sql_mode 全局，@@SESSION.sql_mode 會話

禁用 STRICT_TRANS_TABLES 模式

但是要允許格式 0000-00-00 00:00:00您必須在 mysql 配置文件中或通過命令禁用 STRICT_TRANS_TABLES 模式

However to allow the format 0000-00-00 00:00:00you have to disable STRICT_TRANS_TABLES mode in mysql config file or by command

通過命令

SET sql_mode = '';

或

SET GLOBAL sql_mode = '';

使用關(guān)鍵字 GLOBAL 需要超級特權(quán)，它會影響從那時起所有客戶端連接的操作

Using the keyword GLOBAL requires super previliges and it affects the operations all clients connect from that time on

如果以上不起作用，請轉(zhuǎn)到 /etc/mysql/my.cnf(根據(jù) ubuntu)并注釋掉 STRICT_TRANS_TABLES

if above is not working than go to /etc/mysql/my.cnf (as per ubuntu) and comment out STRICT_TRANS_TABLES

此外，如果您想在服務(wù)器啟動時永久設(shè)置 sql 模式，請在 Linux 或 MacOS 上的 my.cnf 中包含 SET sql_mode=''.對于 Windows，這必須在 my.ini 文件中完成.

Also, if you want to permanently set the sql mode at server startup then include SET sql_mode='' in my.cnf on Linux or MacOS. For windows this has to be done in my.ini file.

注意

然而，在 MYSQL 5.6 中默認(rèn)沒有啟用嚴(yán)格模式.因此它不會按照 MYSQL 6 產(chǎn)生錯誤文檔其中說

However strict mode is not enabled by default in MYSQL 5.6. Hence it does not produce the error as per MYSQL 6 documentation which says

MySQL 允許您將0000-00-00"的零"值存儲為虛擬日期".這在某些情況下比使用 NULL 值更方便，并且使用更少的數(shù)據(jù)和索引空間.要禁止0000-00-00"，請啟用 NO_ZERO_DATE SQL 模式.

MySQL permits you to store a "zero" value of '0000-00-00' as a "dummy date." This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE SQL mode.

更新

關(guān)于@Dylan-Su 所說的錯誤問題:

Regarding the bug matter as said by @Dylan-Su:

我不認(rèn)為這是 MYSQL 隨著時間的推移演變的方式的錯誤，因為根據(jù)產(chǎn)品的進(jìn)一步改進(jìn)，某些事情發(fā)生了變化.

I don't think this is the bug it the way MYSQL is evolved over the time due to which some things are changed based on further improvement of the product.

但是我有另一個關(guān)于 NOW() 函數(shù)的相關(guān)錯誤報告

However I have another related bug report regarding the NOW() function

日期時間字段不接受默認(rèn)的 NOW()

另一個有用的說明 [參見 TIMESTAMP 和 DATETIME 的自動初始化和更新]

從 MySQL 5.6.5 開始，TIMESTAMP 和 DATETIME 列可以自動初始化并更新為當(dāng)前日期和時間(即當(dāng)前時間戳).在 5.6.5 之前，這僅適用于 TIMESTAMP，并且每個表最多有一個 TIMESTAMP 列.下面的注釋首先描述了 MySQL 5.6.5 及更高版本的自動初始化和更新，然后是 5.6.5 之前版本的差異.

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for versions preceding 5.6.5.

關(guān)于 NO_ZERO_DATE 的更新

自 MySQL 5.7.4 起，此模式已棄用.對于以前的版本，您必須注釋掉配置文件中的相應(yīng)行.請參閱 關(guān)于 NO_ZERO_DATE 的 MySQL 5.7 文檔

As of MySQL as of 5.7.4 this mode is deprecated. For previous version you must comment out the respective line in the config file. Refer MySQL 5.7 documentation on NO_ZERO_DATE

這篇關(guān)于為 DATE 或 DATETIME 設(shè)置默認(rèn)值時 MySQL 中的錯誤的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！