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)

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

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

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

CREATE TABLE temp(id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(10),created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,updated_at timestamp NULL);

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; + ---- + --------- + response + ------------------- + | id | name | created_at | updated_at | + ---- + --------- + response + ------------------- + | 1 | robin | 14:00:39 | 14:00:39 | | wentasy | 14:01:11 | 14:01:11 | + ---- + --------- + ------------------------- + ----------------------- + 2 rows in set (0.00 sec)

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 ;

Mysql> UPDATE temp SET name = 'wen wen' WHERE id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 # You can see that the update time of the first data has been recorded. mysql> SELECT * FROM temp; + ---- + ---------- + response + ------------------- + | id | name | created_at | updated_at | + ---- + ---------- + response + | 1 | robinwen | 14:00:39 | 14:03:05 | 2 | wentasy | 14:01:11 | 14:01:11 | + ---- + ---------- + ------------------------- + ----------------------- + 2 rows in set (0.00 sec)

Second, Created_at uses the trigger, and updated_at uses DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP or DEFAULT now () on update now ();

The solution is as follows:

CREATE TABLE temp(id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(10),created_at timestamp NULL,updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Delimiter | drop trigger if exists tri_temp_created_at; create trigger tri_temp_created_at before insert on temp for each row begin if new. created_at is null then set new. created_at = now (); end if; END; | delimiter;

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)

Mysql> UPDATE temp SET name = 'wen wen' WHERE id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 # You can see that the update time of the first data has been recorded. mysql> SELECT * FROM temp; + ---- + ---------- + response + ------------------- + | id | name | created_at | updated_at | + ---- + ---------- + response + | 1 | robinwen | 14:08:36 | 14:09:09 | 2 | wentasy | 14:08:44 | 14:08:44 | + ---- + ---------- + ------------------------- + ----------------------- + 2 rows in set (0.00 sec)

Third, created_at specifies timestamp DEFAULT '2017-00-00 00:00:00 ', updated_at specifies DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP or timestamp DEFAULT now () on update now (); create table temp (id INT (11) primary key AUTO_INCREMENT, name VARCHAR (10), created_at timestamp null default '2017-00-00 00:00:00 ', updated_at timestamp null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP );

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)

Mysql> UPDATE temp SET name = 'wen wen' WHERE id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 # You can see that the update time of the first data has been recorded. mysql> SELECT * FROM temp; + ---- + ---------- + response + ------------------- + | id | name | created_at | updated_at | + ---- + ---------- + response + | 1 | robinwen | 14:10:43 | 14:11:24 | 2 | wentasy | 14:10:57 | 14:10:57 | + ---- + ---------- + ------------------------- + ----------------------- + 2 rows in set (0.00 sec)

FourthTo replace the MySQL version. MySQL 5.6 has removed this restriction.

For more information, see the MySQL 5.5 and 5.6 help documents.

From the MySQL 5.5 documentation:

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. it is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Changes in MySQL 5.6.5:

Previusly, at most one TIMESTAMP column per table cocould be automatically initialized or updated to the current date and time. this restriction has been lifted. any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP clses. in addition, these clses now can be used with DATETIME column definitions. for more information, see initiic Initialization and Updating for TIMESTAMP and DATETIME.

Let's determine the MySQL version.

mysql> SELECT VERSION();+---------------------------------------+| VERSION()                             |+---------------------------------------+| 5.6.20-enterprise-commercial-advanced |+---------------------------------------+1 row in set (0.00 sec)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 ); Query OK, 0 rows affected (0.28 sec)

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; + ---- + --------- + response + ------------------- + | id | name | created_at | updated_at | + ---- + --------- + response + ------------------- + | 1 | robin | 15:05:57 | 15:05:57 | | wentasy | 15:06:02 | 15:06:02 | + ---- + --------- + ------------------------- + ----------------------- + 2 rows in set (0.01 sec) mysql> UPDATE temp SET name = 'wen wen' WHERE id = 1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 # You can see that the update time of the first data has been recorded. mysql> SELECT * FROM temp; + ---- + ---------- + response + ------------------- + | id | name | created_at | updated_at | + ---- + ---------- + response + | 1 | robinwen | 15:05:57 | 15:06:45 | 2 | wentasy | 15:06:02 | 15:06:02 | + ---- + ---------- + ------------------------- + ----------------------- + 2 rows in set (0.00 sec)

Summary

Good Luck!

Robin

September 1, 2014