Setting up a column in MySQL/MariaDB to update an INT(11) column with the unix_timestamp()

Question

Is it possible to set up a column in MySQL to automatically update an INT(11) column with the current Unix timestamp value on an UPDATE? I tried everything and can't seem to get it working. I can make it work as a default value on an insert using unix_timestamp().

Answer 1

No.

Only the timestamp and datetime datatypes support the on update clause.

In recent versions of MySQL though, you can combine an auto-updated timestamp column with a computed column that turns it to a unix timestamp - which avoids the need for a trigger:

create table mytable (
    id int primary key,

    -- auto-updated timestamp colum
    update_ts timestamp on update current_timestamp,

    -- unix timestamp computed from the timestamp
    unix_update_ts int as (timestampdiff(second, '1970-01-01', update_ts))
);

Note that we cannot use unix_timestamp() in a computed column as of now; MySQL seems to assume that the results of this function are not deterministic (which is not correct when the function is given a fixed argument) - but we can work around this with timestampdiff().