MySQL Build table set two default Current_timestamp tips

reprint: http://blog.163.com/user_zhaopeng/blog/static/166022708201252323942430/

Business Scenario:

For example, the user table, we need to build a field is the creation time, a field is the update time.

The workaround can be to specify the insertion time, or the default time for the database to be used.

This error can occur if you set two default Current_timestamp in MySQL.

ERROR 1293 (HY000): incorrect table definition; There can is only one TIMESTAMP column with the Current_timestamp in DEFAULT or on UPDATE clause.

Wrong build Table statement:

CREATE TABLE ' db1 '. ' Sms_queue ' ( ' Id ' INTEGER UNSIGNED not NULL auto_increment, ' Message ' VARCHAR not NULL DEFAULT ' Unknown message Error ', ' CurrentState ' VARCHAR (Ten) not NULL DEFAULT ' None ', ' Phone ' VARCHAR DEFAULT NULL, ' Created ' TIMESTAMP not NULL DEFAULT Current_timestamp, ' LastUpdated ' TIMESTAMP not NULL on UPDATE Current_timestamp, ' Triesleft ' tinyint not NULL DEFAULT 3, PRIMARY KEY (' Id ') ) ENGINE = InnoDB;

Workaround, you can use triggers or something else, here or in the way the database is used.

To build a table statement:

CREATE TABLE Test_table ( ID integer NOT NULL auto_increment primary key, stamp_created timestamp default ' 0000-00-00 00:00:00 ', stamp_updated timestamp default now in Update Now () );

Test:

Mysql> Insert IntoTest_table(stamp_created,stamp_updated) Values(Null, Null); QueryOk, 1Row affected(0.06Sec) Mysql> Select * FromT5; +----+---------------------+---------------------+ |Id|stamp_created|stamp_updated| +----+---------------------+---------------------+ | 2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 | +----+---------------------+---------------------+ 2 rows in ( 0.00 Sec) mysql> update test_table set ID = 3 where id = 2; Query OK, 1 row affected (0.05 sec) Rows matched:1 changed:1 warnings:0 mysql> select * FROM test_table;+----+------ ---------------+---------------------+| ID | stamp_created | stamp_updated | +----+---------------------+---------------------+ | 3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | +----+---------------------+---------------------+ 2 rows in Set (0.00 sec)

The solution is seen in StackOverflow, the original URL:

http://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0

MySQL Build table set two default Current_timestamp tips