(GO) MySQL build table set two default Current_timestamp tips

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 TABLETbl_fund_frozen_unfrozen_record (IDBIGINT  not NULLauto_increment,trade_flow_idVARCHAR( -) not NULL, Account_noVARCHAR( +), initiatorVARCHAR( -), Create_dateTIMESTAMP default Current_timestamp, Operate_typeVARCHAR( +) not NULL, Frozen_amountDECIMAL( -,2)DEFAULT 0.0  not NULL, Unfrozen_amountDECIMAL( -,2)DEFAULT 0.0, CredentialVARCHAR( +) not NULL, Modify_dateTIMESTAMP default Current_timestamp, Unfrozen_dateTIMESTAMP, Serial_numBIGINT  not NULL, VERSIONBIGINT, DecriptionVARCHAR( $),CONSTRAINTP_key_1PRIMARY KEY(ID)) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

Use the function to resolve the problem:

Specify the timestamp type in the following ways:

Create Table  integernotnullprimarykey timestamp  Default'0000-00-00 00:00:00'timestampDefault  onupdate now ());

Perform the following tests to verify that the feature is in effect:

mysql> INSERT INTO test_table (stamp_created, stamp_updated) VALUES (null, NULL);
Query OK, 1 row affected (0.06 sec)

Mysql> SELECT * from T5;
+----+---------------------+---------------------+
| ID | stamp_created | stamp_updated |
+----+---------------------+---------------------+
| 2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in Set (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)

Article Source:

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

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

(GO) MySQL build table set two default Current_timestamp tips