Update mysql table with same values and still get a timestamp update

Question

So I have this stamp timestamp DEFAULT NOW() ON UPDATE NOW() row on my table, and I need it to update even when the update I'm executing is basically same data on all fields.

Is there any way of doing this within the declaration of the table, like some other option other than on update, or do I have to force a stamp = now() every time I update (and remove the on update of course since it will be useless).

I've seen this thread, but it only answers what is happening and why, not how to get around it other than forcing it indirectly

Answer 1

As @veeTrain said, it'd be easy if you added it to your update statement. This is just another way of doing it, you can also use unix_timestamp().

UPDATEtable_nameSETlast_logged_in= unix_timestamp() WHEREid= '$user_id'

I know my response is late, but I ran into a similar issue and figured I'd share my solution for those encountering this thread in the future.

Answer 2

You'd have to use a trigger to force it each time.

DELIMITER GO

CREATE TRIGGER `mydb`.`mytable_U` BEFORE UPDATE ON `mydb`.`mytable`  
FOR EACH ROW 
BEGIN  
    SET NEW.stamp = CURRENT_TIMESTAMP;   
END
GO

DELIMITER ;