The simultaneous creation and last update timestamp fields in MySQL solution analysis

before writing this article, make clear my MySQL version.

Mysql> SELECT VERSION (); +------------+| VERSION ()  |+------------+| 5.5.29-log |+------------+1 row in Set (0.00 sec)

There is now a requirement that a table has a field created_at records the timestamp of the record creation, and another field Updated_at records the timestamp of the record being updated.
Let's try the following several statements.

The first one, the test passes.

CREATE TABLE temp (id INT) PRIMARY KEY auto_increment,name VARCHAR (Ten), updated_at timestamp NULL DEFAULT current_timest AMP on UPDATE current_timestamp);

the second one, the test does not pass. Reported error 1293 (HY000) errors. (Complete error message: Error 1293 (HY000): incorrect table definition; There can is only one TIMESTAMP column with Current_timestamp in DEFAULT or on UPDATE clause)

CREATE TABLE temp (id INT) PRIMARY KEY auto_increment,name VARCHAR (Ten), created_at timestamp NULL DEFAULT current_timest Amp,updated_at timestamp NULL DEFAULT current_timestamp on UPDATE current_timestamp);

MySQL 5.5.29 There is such a wonderful limit, do not understand why. Since there is such a limit, then only a detour, now try to give the following several solutions.

First , Created_at uses default Current_timestamp or default now () and UPDATED_AT uses triggers.
The specific solution is as follows:
The 1.TEMP table structure is as follows:

CREATE TABLE temp (id INT) PRIMARY KEY auto_increment,name VARCHAR (Ten), created_at timestamp NULL DEFAULT current_timest Amp,updated_at timestamp NULL);

2. Insert the test data:

Mysql> INSERT into temp (name,created_at,updated_at) VALUES (' Robin ', Now (), now ()); Query OK, 1 row affected (0.03 sec) mysql> INSERT into temp (name,created_at,updated_at) VALUES (' Wentasy ', now (), now ()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * from temp;+----+---------+---------------------+------------------ ---+| ID | Name    | created_at          | updated_at          |+----+---------+---------------------+---------------------+|  1 | Robin   | 2014-09-01 14:00:39 | 2014-09-01 14:00:39 | |  2 | Wentasy | 2014-09-01 14:01:11 | 2014-09-01 14:01:11 |+----+---------+---------------------+---------------------+2 rows in Set (0.00 sec)

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

Delimiter | DROP TRIGGER IF EXISTS tri_temp_updated_at; CREATE TRIGGER tri_temp_updated_at before UPDATE on tempfor each rowbeginset new.updated_at = Now (); End;|delimiter;

4. Test.

Mysql> UPDATE temp SET name= ' Robinwen ' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched:1  changed:1  warnings:0# can see that the first data has been recorded for update time mysql> SELECT * FR OM temp;+----+----------+---------------------+---------------------+| ID | Name     | created_at          | updated_at          |+----+----------+---------------------+---------------------+|  1 | Robinwen | 2014-09-01 14:00:39 | 2014-09-01 14:03:05 | |  2 | Wentasy  | 2014-09-01 14:01:11 | 2014-09-01 14:01:11 |+----+----------+---------------------+-------------------- -+2 rows in Set (0.00 sec)

second , Created_at uses the trigger, UPDATED_AT using default Current_timestamp on UPDATE current_timestamp or Default now () on UPDATE now ();

The specific solution is as follows:
The 1.TEMP table structure is as follows:

CREATE TABLE temp (id INT) PRIMARY KEY auto_increment,name VARCHAR (Ten), created_at timestamp null,updated_at timestamp N ULL DEFAULT current_timestamp on UPDATE current_timestamp);

2. Create a trigger on temp to create time for inserting data records;

Delimiter | DROP TRIGGER IF EXISTS tri_temp_created_at; CREATE TRIGGER tri_temp_created_at before INSERT on tempfor each rowbeginif new.created_at is Nullthenset new.created_at=n ow (); END IF; End;|delimiter;

3. Insert the test data:

Mysql> INSERT into temp (name,created_at,updated_at) VALUES (' Robin ', Now (), now ()); Query OK, 1 row affected (0.01 sec) mysql> INSERT into temp (name,created_at,updated_at) VALUES (' Wentasy ', now (), now ()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * from temp;+----+---------+---------------------+------------------ ---+| ID | Name    | created_at          | updated_at          |+----+---------+---------------------+---------------------+|  1 | Robin   | 2014-09-01 14:08:36 | 2014-09-01 14:08:36 | |  2 | Wentasy | 2014-09-01 14:08:44 | 2014-09-01 14:08:44 |+----+---------+---------------------+---------------------+2 rows in Set (0.00 sec)

4. Test.

Mysql> UPDATE temp SET name= ' Robinwen ' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched:1  changed:1  warnings:0# can see that the first data has been recorded for update time mysql> SELECT * FR OM temp;+----+----------+---------------------+---------------------+| ID | Name     | created_at          | updated_at          |+----+----------+---------------------+---------------------+|  1 | Robinwen | 2014-09-01 14:08:36 | 2014-09-01 14:09:09 | |  2 | Wentasy  | 2014-09-01 14:08:44 | 2014-09-01 14:08:44 |+----+----------+---------------------+-------------------- -+2 rows in Set (0.00 sec)

Third , created_at specifies TIMESTAMP default ' 0000-00-00 00:00:00 ', updated_at specifies default current_timestamp on UPDATE Current_timestamp or timestamp DEFAULT now () in UPDATE now ();
The specific solution is as follows:
The 1.TEMP table structure is as follows:

CREATE TABLE Temp (id INT (one) PRIMARY KEY auto_increment,name VARCHAR (), created_at timestamp NULL DEFAULT ' 0000-00-00 00: 00:00 ', updated_at timestamp NULL DEFAULT current_timestamp on UPDATE current_timestamp);

2. Insert the test data:

Mysql> INSERT into temp (name,created_at,updated_at) VALUES (' Robin ', Now (), now ()); Query OK, 1 row affected (0.01 sec) mysql> INSERT into temp (name,created_at,updated_at) VALUES (' Wentasy ', now (), now ()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * from temp;+----+---------+---------------------+------------------ ---+| ID | Name    | created_at          | updated_at          |+----+---------+---------------------+---------------------+|  1 | Robin   | 2014-09-01 14:10:43 | 2014-09-01 14:10:43 | |  2 | Wentasy | 2014-09-01 14:10:57 | 2014-09-01 14:10:57 |+----+---------+---------------------+---------------------+2 rows in Set (0.00 sec)

3. Test.

Mysql> UPDATE temp SET name= ' Robinwen ' WHERE id=1; Query OK, 1 row affected (0.01 sec) Rows matched:1  changed:1  warnings:0# can see that the first data has been recorded for update time mysql> SELECT * FR OM temp;+----+----------+---------------------+---------------------+| ID | Name     | created_at          | updated_at          |+----+----------+---------------------+---------------------+|  1 | Robinwen | 2014-09-01 14:10:43 | 2014-09-01 14:11:24 | |  2 | Wentasy  | 2014-09-01 14:10:57 | 2014-09-01 14:10:57 |+----+----------+---------------------+-------------------- -+2 rows in Set (0.00 sec)

fourth , to replace the MySQL version, MySQL 5.6 has removed this limitation.

We can look at the MySQL 5.5 and 5.6 help documentation for an explanation of this problem.

From the MySQL 5.5 documentation:

One TIMESTAMP column in a table can has the current TIMESTAMP as the default value for initializing the column, as the AU To-update value, or both. It is not a possible to having the current timestamp being the default value for one column and the Auto-update value for another Column.

Changes in MySQL 5.6.5:

Previously, at most one TIMESTAMP column per table could is automatically initialized or updated to the current date and T Ime. This restriction has been lifted. Any TIMESTAMP column definition can has any combination of the DEFAULT current_timestamp and on UPDATE current_timestamp Clau Ses. In addition, these clauses now can is used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

We determine the version under MySQL.

Mysql> SELECT VERSION (); +---------------------------------------+| VERSION ()                             |+---------------------------------------+| 5.6.20-enterprise-commercial-advanced |+-------------- -------------------------+1 Row in Set (0.00 sec)

we can see that there are no errors when we execute the SQL statement that the first Test does not pass under MySQL 5.6.

CREATE TABLE temp (id INT) PRIMARY KEY auto_increment,name VARCHAR (Ten), created_at timestamp NULL DEFAULT current_timest Amp,updated_at timestamp NULL DEFAULT current_timestamp on UPDATE current_timestamp); Query OK, 0 rows affected (0.28 sec)

We then insert the test statement and test it.

Mysql> INSERT into temp (name) VALUES (' Robin '); Query OK, 1 row affected (0.07 sec) mysql> INSERT into temp (name) VALUES (' Wentasy '); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from temp;+----+---------+---------------------+------------------ ---+| ID | name | Created_at |  Updated_at |+----+---------+---------------------+---------------------+| 1 | Robin | 2014-09-01 15:05:57 |  2014-09-01 15:05:57 | | 2 | Wentasy | 2014-09-01 15:06:02 | 2014-09-01 15:06:02 |+----+---------+---------------------+---------------------+2 rows in Set (0.01 sec) mysql> UPDATE temp SET name= ' Robinwen ' WHERE id=1; Query OK, 1 row affected (0.02 sec) Rows matched:1 changed:1 warnings:0# can see that the first data has been recorded for update time mysql> SELECT * from tem p;+----+----------+---------------------+---------------------+| ID | name | Created_at |  Updated_at |+----+----------+---------------------+---------------------+| 1 | Robinwen | 2014-09-01 15:05:57 | 2014-09-01 15:06:45 | | 2 | Wentasy | 2014-09-01 15:06:02 | 2014-09-01 15:06:02 |+----+----------+---------------------+---------------------+2 rows in Set (0.00 sec)

Summary
The method described in this article boils down to two, one is to specify default values and update actions, and the second is to use triggers to insert default values and update time. In the face of the fact that cannot be changed, only compromise or sacrifice more to compensate. It is also worth noting that there are many problems to think of different solutions, as far as possible to list all possible or feasible solutions, so that you learn more, and can exercise the breadth of thinking, sunline a variety of programs can compensate for a particular scenario in a specific environment is not feasible.

Good luck!

Robin

September 1, 2014

The simultaneous creation and last update timestamp fields in MySQL solution analysis