更新应用程序或数据库中的时间戳列? [英] Update timestamp column in Application or Database?

问题描述

我在数据库中的每个表上都有 date_created 和 date_modified 列.

I have date_created and date_modified columns on each table in database.

使用 MySQL 触发器在数据库中设置时间戳列与使用 PHP 在应用程序层设置它们的优点/缺点是什么?

What are advantages/disadvantages setting timestamp columns at database using MySQL triggers vs setting them at application layer using PHP?

在多台服务器上部署时，哪个更易于维护且时间同步更好?

Which is easier to maintain and have better time synchronization when deployed across on many servers?

由于 Johan 建议在数据库中设置时间戳很好，这些是正确的实现吗?另外，在每个表上都有 date_created 和 date_modified 是一种好习惯吗?还是建议将它们添加到某些表中?

since Johan suggested setting timestamps in database is good, are these correct implementations? additionally, is it good practice to have date_created and date_modified on every table? or do you recommend adding them on certain tables?

date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
date_modified TIMESTAMP NOT NULL DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

推荐答案

数据库中的时间戳.

如果您在主从设置中进行复制，则会正确复制数据库时间戳(使用原始时间戳).

If you replicate in a master-slave setup, database timestamps are correctly replicated (using the original timestamp).

您没有 (!) 在触发器中设置时间戳.

You do not (!) set timestamps in triggers.

相反，您在表定义中指定它们，MySQL 将在插入和/或更新时自动设置正确的时间戳.
您只需在创建(或更改)表时设置它们一次，仅此而已.

Instead you specify them in the table definition, MySQL will automatically set the correct timestamp on insert and/or update.
You only have to set them once, when you create (or alter) the table and that's it.

再简单不过了.

这篇关于更新应用程序或数据库中的时间戳列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！