MySQL两列时间戳默认NOW值错误1067 [英] MySQL two column timestamp default NOW value ERROR 1067

问题描述

我有表如下图所示。为了解决一个默认的现在列限制MySQL我使用提示如下所示此处

I have table as shown below. In order to workaround one default now column restriction of MySQL I used the tip as shown here

CREATE  TABLE IF NOT EXISTS mytable (
  id INT NOT NULL AUTO_INCREMENT ,
  create_date TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' ,
  update_date TIMESTAMP NULL DEFAULT NOW() ON UPDATE NOW() ,
  PRIMARY KEY (`parti_id`) )
ENGINE = InnoDB;

我的sql_mode不包括 NO_ZERO_DATE 这里我的输出：

My sql_mode does not include NO_ZERO_DATE as pointed here my output :

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            | 
+------------+
1 row in set (0.00 sec)

它仍然给出错误，如下所示：

It is still giving the error as shown below:

ERROR 1067 (42000) at line xx in file: '/myschema.sql': Invalid default value for 'create_date'

5.1.37在Ubuntu上

I use MySQL 5.1.37 on Ubuntu

如何解决？

推荐答案

您只能有一个时间戳记列默认为 CURRENT_TIMESTAMP 或 NOW（）。这是MySQL中的一个众所周知的错误。

You can only have one timestamp column that defaults to CURRENT_TIMESTAMP or NOW() per table. This is a well known bug in MySQL.

为了克服这个问题，使你创建的列的默认值为有效的时间戳值，然后在CRUD应用程序代码中插入时间戳。
为您更新的列默认值使用 NOW（）或 CURRENT_TIMESTAMP 。

To overcome this, make your default for the created column a valid timestamp value, then insert the timestamp in your CRUD application code. Use NOW() or CURRENT_TIMESTAMP for your updated column default.

参考资料：
http：/ /dev.mysql.com/doc/refman/5.1/en/timestamp.html

要进一步说明MySQL在这方面的缺点，请考虑以下代码：

To further illustrate MySQL's shortcoming in this area, consider the following code:

CREATE TABLE testing_timestamps (
  id INT NOT NULL AUTO_INCREMENT,
  pk_id INT NOT NULL,
  col1 TIMESTAMP DEFAULT 0,
  col2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(id)
);

delimiter $$
CREATE TRIGGER testing_timestamps_trigger
  AFTER INSERT ON testing_timestamps
  FOR EACH ROW 
  BEGIN
    UPDATE testing_timestamps SET col1 = NOW() WHERE id = MAX(id);
  END;
$$
delimiter ;

INSERT INTO testing_timestamps (id) VALUES (0);

此输出将显示：

mysql> INSERT INTO testing_timestamps (id) VALUES (0);
ERROR 1442 (HY000): Can't update table 'testing_timestamps' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

这是一个坏消息，因为在这个实例中使用触发器将是一个很好的工作。

This is a bummer because using a trigger in this instance would be a good work around.

这篇关于MySQL两列时间戳默认NOW值错误1067的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！