行创建和最后修改的时间戳 [英] Timestamp for row creation and last modification

问题描述

我需要跟踪将行插入数据库的时间以及上次修改的时间.

I need to keep track of the time a row was inserted into the database, and the time it was last modified.

我尝试创建两个单独的列，并使用CURRENT_TIMESTAMP:

I tried to create two separate columns, and use CURRENT_TIMESTAMP:

create table def (
  id int, 
  creation timestamp 
    default CURRENT_TIMESTAMP, 
  modification timestamp 
    on update CURRENT_TIMESTAMP
);

但是，这产生了一个错误:

However, this produced an error:

错误1293(HY000):错误的表定义；在DEFAULT或ON UPDATE子句中只能有一个带有CURRENT_TIMESTAMP的TIMESTAMP列

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

什么是最好的方法?

我正在考虑存储过程，但正在寻找标准的解决方案.我还关心访问权限-尽可能少的程序/事物应该能够触摸时间戳.

I'm thinking stored procedure, but looking for a standard solution. I'm also concerned with access privileges -- as few programs/things should be able to touch the timestamps as possible.

尽管我更喜欢MySQL的答案，但也赞赏其他RDBMS的解​​决方案！

Although I would prefer MySQL answers, solutions for other RDBMS's are also appreciated!

推荐答案

是的，这对MySQL来说是一个la脚的限制.如果正在浏览应用程序，则可以为created_at列添加time()调用，并让Updated_at列使用CURRENT_TIMESTAMP.

Ya this is a lame limitation on MySQL. If you are going through an application you can add a time() call for the created_at column, and let the updated_at column use the CURRENT_TIMESTAMP.

$sql = "INSERT INTO my_table SET name = 'Mike', created_at = " . time();

我选择在created_at列上执行此操作，因为它可能不会像update_at列那样频繁地被触摸.

I'd opt to do this on the created_at column as it probably won't be touched as often as the updated_at column.

-编辑-

更好的是，使用MySQL内置的now()函数.这样，您只需要关心mysql服务器的时区，而不必关心应用程序服务器和mysql服务器的时区.

Better yet, use MySQL's built in now() function. This way you only need to be concerned with the timezone of the mysql server, and not the timezones of the app server AND the mysql server.

$sql = "INSERT INTO my_table SET name = 'Mike', created_at = NOW()";

这篇关于行创建和最后修改的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！