MySQL 如何将字段的默认值设置为“0000-00-00 00:00:00”？

Question

提问by laukok

How can I set the default value of a field as '0000-00-00 00:00:00'? If we can't use '0000-00-00 00:00:00' as the default? What is the basicvalid tiemdate?

如何将字段的默认值设置为“0000-00-00 00:00:00”？如果我们不能使用 '0000-00-00 00:00:00' 作为默认值？什么是基本有效期限？

For instance, this is the SQL for creating my article table,

例如，这是用于创建我的文章表的 SQL，

-- -----------------------------------------------------
-- Table `article`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `article` ;

CREATE TABLE IF NOT EXISTS `article` (
  `article_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `date_from` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured from a datetime.',
  `date_to` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured to a datetime.',
  `backdated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The manual datetime that is modified or input by the user.',
  `created_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The permanent datetime when the article is created.',
  `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime when the article is updated on.',
  PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
  UNIQUE INDEX `url_UNIQUE` (`url` ASC))
ENGINE = MyISAM
AUTO_INCREMENT = 66
COMMENT = 'Entity that holds the article with one-to-one properties.';

I get this error when I run this query,

运行此查询时出现此错误，

#1067 - Invalid default value for 'date_from'

Answer 1

回答by VMai

Cause of the error: the SQL mode

错误原因：SQL模式

You can set the default value of a DATE, DATETIMEor TIMESTAMPfield to the special "zero" value of '0000-00-00' as dummy date if the sql mode permits it. For MySQL versions lower than 5.7.4 this is ruled by the NO_ZERO_DATE mode, see this excerpt of the documentation:

如果 sql 模式允许，您可以将 a DATE, DATETIMEorTIMESTAMP字段的默认值设置为“0000-00-00”的特殊“零”值作为虚拟日期。对于低于 5.7.4 的 MySQL 版本，这是由 NO_ZERO_DATE 模式决定的，请参阅文档摘录：

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.

MySQL 允许您将“0000-00-00”的“零”值存储为“虚拟日期”。这在某些情况下比使用 NULL 值更方便，并且使用更少的数据和索引空间。要禁止“0000-00-00”，请启用 NO_ZERO_DATE SQL 模式。

Additionally strict mode has to be enabled for disallowing "zero" values:

此外，必须启用严格模式以禁止“零”值：

If this mode and strict mode are enabled, '0000-00-00' is not permitted
and inserts produce an error, unless IGNORE is given as well.

如果启用此模式和严格模式，则不允许使用 '0000-00-00'
并且插入会产生错误，除非也给出 IGNORE。

As of MySQL 5.7.4this depends only on the strict mode:

从MySQL 5.7.4 开始，这仅取决于严格模式：

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.

严格模式会影响服务器是否允许 '0000-00-00' 作为有效日期：
如果未启用严格模式，则允许使用 '0000-00-00' 并且插入不会产生警告。
如果启用了严格模式，则不允许使用 '0000-00-00' 并且插入会产生错误，除非也给出了 IGNORE。对于 INSERT IGNORE 和 UPDATE IGNORE，允许使用 '0000-00-00' 并且插入会产生警告。

Check version and SQL mode

检查版本和SQL模式

So you should to check your MySQL version and the SQL modeof your MySQL server with

所以你应该检查你的 MySQL 版本和你的 MySQL 服务器的SQL 模式

SELECT version();
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Enable the INSERT

启用插入

You can set the sql_mode for your session with SET sql_mode = '<desired mode>'

您可以为您的会话设置 sql_mode SET sql_mode = '<desired mode>'

SET sql_mode = 'STRICT_TRANS_TABLES';

Valid range for DATETIME

DATETIME 的有效范围

The supported range for DATETIMEis

支持的范围DATETIME是

[1000-01-01 00:00:00] to ['9999-12-31 23:59:59'],

so the minimal valid DATETIME value is '1000-01-01 00:00:00'.
I wouldn't recommend to use this value though.

所以最小的有效 DATETIME 值为“1000-01-01 00:00:00”。
我不建议使用这个值。

Additional Note

附加说明

Since MySQL 5.6.5 all TIMESTAMPand DATETIMEcolumns can have the magic behavior (initializing and/or updating), not only TIMESTAMPand only one column at most, see Automatic Initialization and Updating for TIMESTAMP and DATETIME:

由于 MySQL 5.6.5 allTIMESTAMP和DATETIMEcolumns 可以具有魔法行为（初始化和/或更新），不仅TIMESTAMP而且最多只有一列，请参阅TIMESTAMP 和 DATETIME 的自动初始化和更新：

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.

从 MySQL 5.6.5 开始，TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前日期和时间（即当前时间戳）。在 5.6.5 之前，这仅适用于 TIMESTAMP，并且每个表最多有一个 TIMESTAMP 列。下面的注释首先描述了 MySQL 5.6.5 及更高版本的自动初始化和更新，然后是 5.6.5 之前版本的差异。

You could change your CREATE TABLE statement in the case of MySQL 5.6.5 or newer to:

对于 MySQL 5.6.5 或更新版本，您可以将 CREATE TABLE 语句更改为：

CREATE TABLE IF NOT EXISTS `article` (
  `article_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `date_from` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured from a datetime.',
  `date_to` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured to a datetime.',
  `backdated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The manual datetime that is modified or input by the user.',
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'The permanent datetime when the article is created.',
  `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime when the article is updated on.',
  PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
  UNIQUE INDEX `url_UNIQUE` (`url` ASC))
ENGINE = MyISAM
AUTO_INCREMENT = 66
COMMENT = 'Entity that holds the article with one-to-one properties.';

Answer 2

回答by rebduvid

There is a solution. I'm not sure about the security of this so I recommend to

有一个解决方案。我不确定这个的安全性，所以我建议

SELECT @@SESSION.sql_mode session

first. Save the value in your clipboard, then

第一的。将值保存在剪贴板中，然后

SET SESSION sql_mode = '';

After that you can create or alter the table with default values like '0000-00-00 00:00:00'

之后，您可以使用默认值（如“0000-00-00 00:00:00”）创建或更改表

Answer 3

回答by Patrick Tucci

Dates in SQL Server can only be between January 1, 1753 and December 31, 9999.

SQL Server 中的日期只能介于 1753 年 1 月 1 日和 9999 年 12 月 31 日之间。

You have to decide how to handle default or missing datetimes as an organization or group (NULL, or one of these extremes). Typically our organization chooses to go with no date instead of the 1753 or the 9999 date. When there is no date, NULL is more appropriate than having any date at all, as ludicrous as something in the 1750's or 9999's may seem. Leaving missing dates as NULL will also avoid reports pulling bogus dates.

您必须决定如何作为一个组织或组（NULL 或这些极端之一）处理默认或缺失的日期时间。通常，我们的组织选择不使用日期而不是 1753 或 9999 日期。当没有日期时，NULL 比任何日期都更合适，就像 1750 年代或 9999 年代的东西一样荒谬。将缺少的日期保留为 NULL 也将避免报告提取虚假日期。

http://msdn.microsoft.com/en-us/library/ms187819.aspx

Answer 4

回答by u1241648

because default valid datetime value after mysql version 5.6.5 as

因为 mysql 版本 5.6.5 之后的默认有效日期时间值为

1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

so we can change mysql config to resolve the problem:

所以我们可以更改mysql配置来解决问题：

show current mysql version and sql_mode:

显示当前的 mysql 版本和 sql_mode：

select version() as version, @@sql_mode as sql_mode;

will output:

将输出：

so, we need remove NO_ZERO_IN_DATE and NO_ZERO_DATE.

所以，我们需要删除 NO_ZERO_IN_DATE 和 NO_ZERO_DATE。

modify mysql config file

修改mysql配置文件

vi /etc/mysql/my.cnf

[mysqld]

...

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

...

save and restart mysql server

保存并重启mysql服务器

/etc/init.d/mysql restart

so easy!

太简单！

Answer 5

回答by sree

You can also replace the modes like this, it works.

您也可以像这样替换模式，它有效。

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_AUTO_CREATE_USER',''));

Answer 6

回答by Jaymin

Replace code from :

替换代码：

    CREATE TABLE IF NOT EXISTS `article` (
      `article_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `url` VARCHAR(255) NOT NULL,
      `title` VARCHAR(255) NOT NULL,
      `date_from` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured from a datetime.',
      `date_to` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured to a datetime.',
      `backdated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The manual datetime that is modified or input by the user.',
      `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'The permanent datetime when the article is created.',
      `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime when the article is updated on.',
      PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
      UNIQUE INDEX `url_UNIQUE` (`url` ASC))
    ENGINE = MyISAM
    AUTO_INCREMENT = 66
    COMMENT = 'Entity that holds the article with one-to-one properties.';

to

到

    CREATE TABLE IF NOT EXISTS `article` (
      `article_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `url` VARCHAR(255) NOT NULL,
      `title` VARCHAR(255) NOT NULL,
      `date_from` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT 'Set the article as new or featured from a datetime.',
      `date_to` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT 'Set the article as new or featured to a datetime.',
      `backdated_on` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT 'The manual datetime that is modified or input by the user.',
      `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'The permanent datetime when the article is created.',
      `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime when the article is updated on.',
      PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
      UNIQUE INDEX `url_UNIQUE` (`url` ASC))
    ENGINE = MyISAM

Its working for me and it will helpful for php 7 and mysql 5.7

它对我有用，对 php 7 和 mysql 5.7 很有帮助

MySQL 如何将字段的默认值设置为“0000-00-00 00:00:00”？

提问by laukok

回答by VMai

回答by rebduvid

回答by Patrick Tucci

回答by u1241648

回答by sree

回答by Jaymin

相关推荐

最近更新

标签

MySQL 如何将字段的默认值设置为“0000-00-00 00:00:00”？

提问by laukok

回答by VMai

回答by rebduvid

回答by Patrick Tucci

回答by u1241648

回答by sree

回答by Jaymin

相关推荐

MySQL CREATE TABLE LIKE A1 as A2

MySQL Flyway迁移，无法从DataSource获取Jdbc连接

MySQL 结果为逗号分隔列表

具有多个值的 MYSQL CASE THEN 语句

相关推荐

最近更新

标签