mysql 默认时间字段 1067,MySQL两列时间戳默认NOW值错误1067

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;

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'

I use MySQL 5.1.37 on Ubuntu

How can I fix it? Thanks.

解决方案

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

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.

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);

The output from this will display:

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.