mysql - 创建Mysql数据库表时出错

Question

下面是我用来创建表的查询:

CREATE TABLE `users_authentication` (
`id` int(11) NOT NULL,
`users_id` int(11) NOT NULL,
`token` varchar(255) NOT NULL,
`expired_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

但它显示了如下错误消息:

1067 - Invalid default value for 'expired_at'

Answer 1

使用TIMESTAMP而不是DATETIME。在某些版本的 MySQL 中，无法将 CURRENT_TIMESTAMP 与 DATETIME 一起使用。

因此，对于 5.6.5 之前的 MySQL 版本，您可以为此替换脚本:

CREATE TABLE `users_authentication` (
`id` int(11) NOT NULL,
`users_id` int(11) NOT NULL,
`token` varchar(255) NOT NULL,
`expired_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL,
`updated_at` TIMESTAMP NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

查看此处:How do you set a default value for a MySQL Datetime column?

MySQL 文档:

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initialized 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.

MySQL Doc