MySQL `timestamp` to be updated even with same column value

Question

I am using timestamp columns to one of my table, and using the auto update functionality. Here is my table schema:

mysql> desc user_rides;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| user_id    | int(11)      | NO   | MUL | NULL              |                             
| ride_cnt   | int(11)      | YES  |     | NULL              |                             |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.02 sec)

What I'm expecting is that,

• created_at column to be initialize with the time, the row gets created and
• updated_at column to be same as created_at and also updated when any of the columns(basically ride_cnt) get updated.

This works great.

But what I am also expecting is that the updated_at to be updated even if ride_cnt has the same value. So that I can keep a track of when was the last time the row's value fetched and can be ignored for further run.

For example:

The rows with ride_cnt = 0 to be updated with the latest time we ran the update. So that the rows can be ignored for quite sometime to be reinitialize.

Is there any way we can achieve this without passing in timestamp manually?

Edit:

Here what's happening,

mysql> insert into user_ride set user_id=7445, user_ride=0;
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_ride set user_id=7009, user_ride=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_ride;
+----+---------+-----------+---------------------+---------------------+
| id | user_id | user_ride | created_at          | updated_at          |
+----+---------+-----------+---------------------+---------------------+
|  1 |    7445 |         0 | 2017-06-13 10:44:05 | 2017-06-13 10:44:05 |
|  2 |    7009 |         2 | 2017-06-13 10:44:18 | 2017-06-13 10:44:18 |
+----+---------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> update user_ride set user_ride=0 where id=1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from user_ride;
+----+---------+-----------+---------------------+---------------------+
| id | user_id | user_ride | created_at          | updated_at          |
+----+---------+-----------+---------------------+---------------------+
|  1 |    7445 |         0 | 2017-06-13 10:44:05 | 2017-06-13 10:44:05 |
|  2 |    7009 |         2 | 2017-06-13 10:44:18 | 2017-06-13 10:44:18 |
+----+---------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> update user_ride set user_ride=1 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_ride;
+----+---------+-----------+---------------------+---------------------+
| id | user_id | user_ride | created_at          | updated_at          |
+----+---------+-----------+---------------------+---------------------+
|  1 |    7445 |         1 | 2017-06-13 10:44:05 | 2017-06-13 10:45:26 |
|  2 |    7009 |         2 | 2017-06-13 10:44:18 | 2017-06-13 10:44:18 |
+----+---------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)

Answer 1

Add a trigger.

CREATE TRIGGER user_ride_bu
BEFORE UPDATE ON user_ride
FOR EACH ROW
SET NEW.updated_at = NOW();

(For those wondering where the DELIMITER directives are, they aren't needed when a trigger has a single, simple statement).

Answer 2

I wish to call attention to your first update in the scenario you described above:

mysql> update user_ride set user_ride=0 where id=1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

We can see that there was a matching row, but no update actually took place. The reason for this is the update would have resulted in no data actually changing. Hence, the ON UPDATE clause of the updated_at timestamp never kicked in. I can offer two workarounds for your problem. The first, probably the most performant, would be to just manually set the updated_at column to the current timestamp during the update. Hence, from the example above you would use this instead:

update user_ride set user_ride = 0, updated_at = CURRENT_TIMESTAMP where id=1;

This should trigger an actual update of the row, because the timestamp has changed since it was last updated.

Another workaround would be to find a way to ensure that each update will always change some of the data in the given record. Then, the ON UPDATE clause would always be applied.

This may seem like a limitation, but I guess MySQL does not consider a change as having happened to a record if the underlying data itself did not change.

This question is sort of a duplicate of this one:

How to Force UPDATE of MySQL Record When Nothing Is Changing

However, since SO's coverage of this problem is so thin, I thought this answer might be useful to anyone else encountering the same problems.