avisheks avisheks -4 years ago 63
MySQL Question

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

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download