MySQL CURRENT_TIMESTAMP作为默认值 [英] MySQL CURRENT_TIMESTAMP as DEFAULT

问题描述

在创建表时，出现以下错误:

While creating a table I am getting the following error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

问题是我实际上没有两列TIMESTAMP，默认情况下为CURRENT_TIMESTAMP，我都没有使用ON UPDATE子句.

The problem is that I don't actually have two columns TIMESTAMP with CURRENT_TIMESTAMP as default, neither I am using ON UPDATE clause.

我要执行的DDL查询是

The DDL query I'm trying to execute is

CREATE TABLE user(
    /* Basic Information */
    id INT NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(255) NOT NULL,
    surname VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(255) NOT NULL UNIQUE,
    password CHAR(40) NOT NULL,

    /* System status information */
    active BOOL NOT NULL DEFAULT FALSE,
    validated BOOL NOT NULL DEFAULT FALSE,
    date_validated TIMESTAMP,
    date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    /* Index */
    PRIMARY KEY (id)
) Engine=InnoDB;

是什么原因引起的问题?

What's causing the issue?

推荐答案

您可以在一个表中使用两个时间戳.默认情况下，请先使用DEFAULT字段，然后再使用其余时间戳字段.

You can use two timestamp in one table. For default, use DEFAULT field first and then the rest timestamp fields.

下面的查询应该可以使用.

Below query should work.

CREATE TABLE myTable 
(
 id INT,
 date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 date_validated TIMESTAMP
);

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