Analysis on the solution of creating and last updating timestamp fields in MySQL, mysql Field

Analysis on the solution of creating and last updating timestamp fields in MySQL, mysql Field
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 use DEFAULT CURRENT_TIMESTAMP or DEFAULT now (), updated_at use 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> 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 during update;

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 = '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:
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,updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

2. Create a trigger on temp to create the time when data records are inserted;

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=now();END IF;END;|delimiter ;

3. Insert 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 = '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 '2014-00-00 00:00:00 ', updated_at specifies DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP or timestamp DEFAULT now () on update now ();
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 '0000-00-00 00:00:00',updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

2. Insert 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 = '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)

We run the SQL statement that failed the test in MySQL 5.6, and we can see that there are no errors.

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)

Then we insert the test statement and perform the test.

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
The methods described in this article are essentially two. One is to specify the default value and update action for the table creation statement, and the other is to use the trigger to insert the default value and update time. In the face of the fact that it cannot be changed, you can only take a compromise or sacrifice more to make up for it. Another thing worth noting is that when you encounter problems, think about different solutions and list all possible or feasible solutions as much as possible so that you can learn more and exercise the breadth of thinking, three or more solutions can make up for the shortcomings of a solution that is not feasible in a specific environment.

Good Luck!

Robin

September 1, 2014

Mysql Field update Problems

One solution is to use the mysql case when condition to determine. Assume that your integer field name is field_1 and the table name is table_x. The following statement can solve your problem.
Update table_x set field_1 = (case when field_1 = 0 then 0 else field_1-1 end) where field_x = 'xxxx ';
That is what you want. Note that do not miss the where condition during execution.
However, I personally do not agree with this usage. First, it is unclear that the business logic is encapsulated in SQL. Changes may affect other functions when the business changes, and second, it reduces the performance of mysql, if the data volume is large, the impact may be obvious.
Use it according to your situation.

I want to create a trigger in MySQL and use update to update different fields in the same table.

...
Why do you need to set a trigger for a table?
Cannot you set or update FeedBack_State when inserting or updating this record?
Why?
How can this problem be solved?