在MySQL/MariaDB中设置一列，以使用unix_timestamp()更新INT(11)列

Question

是否可以在MySQL中设置一列，以便在update上使用当前Unix时间戳值自动更新INT(11(列？我什么都试过了，但似乎没能奏效。我可以使用unix_timestamp()将其作为插入的默认值。

Answer 1

否。

只有timestamp和datetime数据类型支持on update子句。

不过，在MySQL的最新版本中，您可以将自动更新的timestamp列与计算列结合起来，计算列将其转换为unix时间戳，这样就不需要触发器：

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))
);

请注意，到目前为止，我们还不能在计算列中使用unix_timestamp()；MySQL似乎认为这个函数的结果是不确定的(当函数有一个固定的参数时，这是不正确的(，但我们可以使用timestampdiff()来解决这个问题。

Answer 2

您可以使用DATETIME或TIMESTAMP列来完成此操作：

CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
);

然后您可以查询它以获得如下的UNIX时间戳：

SELECT UNIX_TIMESTAMP(timestamp) AS ts FROM mytable;

MySQL 5.7已经生成了列，但它不适用于不确定的函数：

mysql> ALTER TABLE mytable ADD COLUMN ts INT UNSIGNED AS (UNIX_TIMESTAMP(timestamp));
ERROR 3102 (HY000): Expression of generated column 'ts' contains a disallowed function.

你可以定义一个触发器：

mysql> ALTER TABLE mytable ADD COLUMN ts INT UNSIGNED;
mysql> CREATE TRIGGER ts BEFORE UPDATE ON mytable FOR EACH ROW SET NEW.ts = UNIX_TIMESTAMP(NEW.timestamp);
mysql> select * from mytable;
+----+---------------------+------+
| id | timestamp           | ts   |
+----+---------------------+------+
|  2 | 2020-07-23 23:09:57 | NULL |
+----+---------------------+------+
mysql> update mytable set id = 3;
mysql> select * from mytable;
+----+---------------------+------------+
| id | timestamp           | ts         |
+----+---------------------+------------+
|  3 | 2020-07-23 23:14:05 | 1595546045 |
+----+---------------------+------------+

您也应该在插入之前创建一个匹配的触发器。