MySQL:TIMESTAMP的无效默认值 [英] MySQL: Invalid default value for TIMESTAMP

问题描述

我得到了错误

ERROR 1067 (42000) at line 5459: Invalid default value for 'start_time'

运行以下查询时

DROP TABLE IF EXISTS `slow_log`;
CREATE TABLE IF NOT EXISTS `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

我正在使用MySQL 5.7.18

I am using MySQL 5.7.18

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for osx10.10 (x86_64) using  EditLine wrapper

根据 MySQL 5.7文档，以下语法是

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

上面的SQL语法有什么问题?

What is wrong with the SQL syntax above?

推荐答案

有趣的是，这两种方法都有效:

Interestingly, both these work:

`start_time` timestamp(6), 

并且:

`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

您可以使用后者-将精度说明符放在定义之外.

You can use the latter -- leave the precision specifier out of the definition.

但是正确的方法是:

`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

如文档中所述:

如果 TIMESTAMP 或 DATETIME 列定义包含显式小数秒精度值在任何地方，必须相同在整个列定义中使用.这是允许的:

If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition. This is permitted:

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

这是不允许的:

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

这篇关于MySQL:TIMESTAMP的无效默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！