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

Question

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

22

如何将字段的默认值设置为“0000-00-00 00:00:00”？如果不能使用“0000-00-00 00:00:00”作为默认值，那么什么是基本有效的日期时间？

例如，这是创建文章表的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.';

当我运行这个查询时，我收到了这个错误：

#1067 - Invalid default value for 'date_from'

- Run

2

请看这里：https://dev59.com/53fZa4cB1Zd3GeqPVLr2 - Krupal Shah

4

为什么要存储无效日期？如果想标记“缺少数据”，请使用NULL。避免使用“魔术数字”。 - user330315

无法在MySQL 5.5中重现。（您的版本是？）主键声明将不起作用；列“parent_id”和“template_id”不存在。但是，在修复后，您的CREATE TABLE语句对我有效。 - Mike Sherrill 'Cat Recall'

6个回答

7

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

SELECT @@SESSION.sql_mode session

首先，将值保存到您的剪贴板中，然后

SET SESSION sql_mode = '';

在此之后，您可以创建或修改表并设置默认值为“0000-00-00 00:00:00”。

- rebduvid

2

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

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

所以我们可以更改 MySQL 配置来解决这个问题：

显示当前 MySQL 版本和 sql_mode：

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

将输出：

因此，我们需要删除NO_ZERO_IN_DATE和NO_ZERO_DATE。

修改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

...

保存并重启 MySQL 服务器

/etc/init.d/mysql restart

如此简单！

- 君主不是你

2

SQL Server中的日期只能在1753年1月1日至9999年12月31日期间。

您需要决定如何处理缺省或丢失的日期时间作为组织或团体（NULL或这些极端值之一）。通常，我们的组织选择不使用日期，而不是1753或9999日期。当没有日期时，NULL比任何日期更为合适，尽管在1750年代或9999年代似乎荒谬。将缺失的日期保留为NULL也可以避免报告拉取虚假日期。 http://msdn.microsoft.com/en-us/library/ms187819.aspx

- Patrick Tucci

1

OP标记了问题的sql-server标签，但是不恰当地这样做了，而且问题根本不涉及Microsoft SQL Server。 - user743382

1

明白了。感谢您的评论。如果您觉得我的回答与讨论无关，请删除它。 - Patrick Tucci

1

从以下代码替换：

    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 '设置文章的新闻或特色日期时间。',
      `date_to` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '设置文章的新闻或特色截止日期时间。',
      `backdated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '由用户修改或输入的手动日期时间。',
      `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建文章时的永久日期时间。',
      `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新文章时的日期时间。',
      PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
      UNIQUE INDEX `url_UNIQUE` (`url` ASC))
    ENGINE = MyISAM
    AUTO_INCREMENT = 66
    COMMENT = '保存具有一对一属性的文章的实体。';

至

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 '设置文章的新闻或特色日期时间。',
  `date_to` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT '设置文章的新闻或特色截止日期时间。',
  `backdated_on` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00' COMMENT '由用户修改或输入的手动日期时间。',
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '文章创建的永久日期时间。',
  `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '文章更新的日期时间。',
  PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
  UNIQUE INDEX `url_UNIQUE` (`url` ASC))
ENGINE = MyISAM

这对我很有用，也将有助于php 7和mysql 5.7。

- JayminLimbachiya

0

你也可以像这样替换模式，它会生效。

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

- sree

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- VMai · Accepted Answer

错误原因：SQL模式

如果SQL模式允许，您可以将DATE、DATETIME或TIMESTAMP字段的默认值设置为特殊的“零”值'0000-00-00'，作为虚拟日期。对于MySQL版本低于5.7.4，这取决于NO_ZERO_DATE模式，参见文档中的说明：

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

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

如果启用了此模式和严格模式，则不允许使用'0000-00-00'，除非还提供了IGNORE。
插入会产生错误。

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

严格模式影响服务器是否允许'0000-00-00'作为有效日期：

如果未启用严格模式，则允许'0000-00-00'，并且插入不会产生警告。

如果启用了严格模式，则不允许使用'0000-00-00'，除非还提供了IGNORE。对于INSERT IGNORE和UPDATE IGNORE，允许使用'0000-00-00'，并产生警告。

检查版本和SQL模式

所以您应该使用以下命令检查您的MySQL版本和MySQL服务器的SQL模式:

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

启用 INSERT 功能

您可以使用 SET sql_mode = '<desired mode>' 命令为会话设置 sql_mode。

SET sql_mode = 'STRICT_TRANS_TABLES';

DATETIME合法范围

DATETIME的支持范围为：

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

因此，最小有效的DATETIME值为'1000-01-01 00:00:00'。
虽然如此，我不建议使用这个值。

附加说明

自MySQL 5.6.5起，所有TIMESTAMP和DATETIME列都可以具有魔术行为（初始化和/或更新），而不仅仅是TIMESTAMP列，并且每个表格只能有一个列，参见自动初始化和更新TIMESTAMP和DATETIME：

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

如果使用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.';