Analysis on solutions for creating and last updating timestamp fields in MySQL

Analysis on solutions for creating and last updating timestamp fields in MySQL

Before writing this article, clarify my MySQL version.

Mysql> select version ();
+ ------------ +
| VERSION () |
+ ------------ +
| 5.5.29-log |
+ ------------ +
1 row in set (0.00 sec)

This is the requirement. One table has a created_at field to create the timestamp of this record, and the other field updated_at record to update the timestamp of this record.

Let's try the following statements.

First, the test passes.

Create table temp
(
Id INT (11) primary key AUTO_INCREMENT,
Name VARCHAR (10 ),
Updated_at timestamp null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
);

Second, the test fails. ERROR 1293 (HY000) is reported. (Complete ERROR message: ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or on update clause)

Create table temp
(
Id INT (11) primary key AUTO_INCREMENT,
Name VARCHAR (10 ),
Created_at timestamp null default CURRENT_TIMESTAMP,
Updated_at timestamp null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
);

MySQL 5.5.29 has such a wonderful restriction and does not understand why. Since there is such a restriction, there is only a detour, And now try to provide the following solutions.

First, created_at uses DEFAULT CURRENT_TIMESTAMP or DEFAULT now (), and updated_at uses the trigger.

The solution is as follows:

1. The temp table structure is as follows:

Create table temp
(
Id INT (11) primary key AUTO_INCREMENT,
Name VARCHAR (10 ),
Created_at timestamp null default CURRENT_TIMESTAMP,
Updated_at timestamp NULL
);

2. Insert test data:

• Mysql> INSERTINTOtemp (name, created_at, updated_at) VALUES ('Robin ', now (), now ());
• Query OK, 1 row affected (0.03 sec)
• Mysql> INSERTINTOtemp (name, created_at, updated_at) VALUES ('wentasy', now (), now ());
• Query OK, 1 row affected (0.01 sec)
• Mysql> SELECT * FROMtemp;
• + ---- + --------- + --------------------- +
• | Id | name | created_at | updated_at |
• + ---- + --------- + --------------------- +
• | 1 | robin | 14:00:39 | 14:00:39 |
• | 2 | wentasy | 14:01:11 | 14:01:11 |
• + ---- + --------- + --------------------- +
• 2 rowsinset (0.00 sec)

3. Create a trigger on temp to record the update time during update;

Delimiter |
Drop trigger if exists tri_temp_updated_at;
Create trigger tri_temp_updated_at before update on temp
FOR EACH ROW
BEGIN
Set new. updated_at = now ();
END;
|
Delimiter;

4. Test.

• Mysql> UPDATEtempSETname = 'paiwen' WHERE id = 1;
• Query OK, 1 row affected (0.01 sec)
• Rows matched: 1 Changed: 1 Warnings: 0
• # You can see that the first data update time has been recorded.
• Mysql> SELECT * FROMtemp;
• + ---- + ---------- + --------------------- +
• | Id | name | created_at | updated_at |
• + ---- + ---------- + --------------------- +
• | 1 | robinwen | 14:00:39 | 14:03:05 |
• | 2 | wentasy | 14:01:11 | 14:01:11 |
• + ---- + ---------- + --------------------- +
• 2 rowsinset (0.00 sec)

For more details, please continue to read the highlights on the next page:

1 2 Next Page