如何将字段的默认值设置为“0000-00-00 00:00:00”？ [英] How can I set the default value of a field as '0000-00-00 00:00:00'?

问题描述

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

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

   -  -------------------------------- --------------------- 
  - 表`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'将文章设置为新的或从datetime的特色。'，
`date_to` DATETIME NOT NULL DEFAULT'0000-00-00 00:00:00'COMMENT'将文章设置为新的或特征为datetime。'，
`backdated_on` DATETIME NOT NULL DEFAULT'0000 -00-00 00:00:00'COMMENT'由用户修改或输入的手动日期时间'，
`created_on` DATETIME NOT NULL DEFAULT'0000-00-00 00:00:00'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 =拥有一对一属性的文章的实体; 
  

当我运行此查询时，我收到此错误，

$ b $ $ {pre> ＃1067 - 'date_from'的默认值为


解决方案

错误原因：SQL模式

您可以设置默认值 DATE ， DATETIME 或 TIMESTAMP 字段到特殊的零 0000-00-00的值作为虚拟日期，如果sql模式允许的话。对于低于5.7.4的MySQL版本，由NO_ZERO_DATE模式排除，请参阅文档：

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

另外，必须启用严格模式不允许零值：

如果启用此模式和严格模式，则不允许使用0000-00-00 >
和insert会产生错误，除非给出了IGNORE。

自 Mysql 5.7.4 这仅取决于严格模式：

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

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

如果启用严格模式，则不允许0000-00-00，并且插入
会产生错误，除非给出了IGNORE。对于INSERT IGNORE
和更新IGNORE，允许'0000-00-00'，插入产生
警告。

检查版本和SQL模式

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

 code> SELECT version（）; 
 SELECT @@ GLOBAL.sql_mode global，@@ SESSION.sql_mode session 
  

启用INSERT

您可以使用 SET sql_mode ='< desired mode>'

  SET sql_mode ='STRICT_TRANS_TABLES'; 
  

DATETIME的有效范围

DATETIME 的支持范围是

  [1000 -01-01 00:00:00]至['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'将文章设置为新的或从datetime的特色。'，
`date_to` DATETIME NOT NULL DEFAULT'0000-00-00 00:00:00'COMMENT'将文章设置为新的或特征为datetime。'，
`backdated_on` DATETIME NOT NULL DEFAULT'0000-00-00 00:00 ：00'COMMENT'由用户修改或输入的手动日期时间'，
`created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'永久数据创建文章时，ime。'，
`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 =保存文章的实体一对一属性。 
  

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 basic valid tiemdate?

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

-- -----------------------------------------------------
-- 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' 

解决方案

Cause of the error: the SQL mode

You can set the default value of a DATE, DATETIME or TIMESTAMP field 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:

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.

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.

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

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.

Check version and SQL mode

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

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>'

SET sql_mode = 'STRICT_TRANS_TABLES';   

Valid range for DATETIME

The supported range for DATETIME is

[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.

Additional Note

Since MySQL 5.6.5 all TIMESTAMP and DATETIME columns can have the magic behavior (initializing and/or updating), not only TIMESTAMP and only one column at most, see Automatic Initialization and Updating for TIMESTAMP and 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.

You could change your CREATE TABLE statement in the case of MySQL 5.6.5 or newer 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 '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.';

这篇关于如何将字段的默认值设置为“0000-00-00 00:00:00”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！