如何判断 MySQL 表的上次更新时间？

How can I tell when a MySQL table was last updated?

提问by RaGE

In the footer of my page, I would like to add something like "last updated the xx/xx/200x" with this date being the last time a certain mySQL table has been updated.

在我的页面的页脚中，我想添加诸如“上次更新 xx/xx/200x”之类的内容，此日期是某个 mySQL 表的最后一次更新时间。

What is the best way to do that? Is there a function to retrieve the last updated date? Should I access to the database every time I need this value?

最好的方法是什么？是否有检索上次更新日期的功能？每次需要这个值时都应该访问数据库吗？

回答by Alnitak

In later versions of MySQL you can use the information_schemadatabase to tell you when another table was updated:

在更高版本的 MySQL 中，您可以使用information_schema数据库来告诉您另一个表何时更新：

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

This does of course mean opening a connection to the database.

这当然意味着打开与数据库的连接。

---

---

An alternative option would be to "touch" a particular file whenever the MySQL table is updated:

另一种选择是在更新 MySQL 表时“触摸”特定文件：

On database updates:

关于数据库更新：

• Open your timestamp file in O_RDRWmode
• closeit again

• 以O_RDRW模式打开时间戳文件
• close再来一次

or alternatively

或者

• use touch(), the PHP equivalent of the utimes()function, to change the file timestamp.

• 使用函数touch()的 PHP 等效项utimes()来更改文件时间戳。

On page display:

页面显示：

• use stat()to read back the file modification time.

• 用于stat()回读文件修改时间。

回答by Radu Maris

I don't have information_schema database, using mysql version 4.1.16, so in this case you can query this:

我没有 information_schema 数据库，使用 mysql 4.1.16 版，所以在这种情况下你可以查询：

SHOW TABLE STATUS FROM your_database LIKE 'your_table';

It will return these columns:

它将返回这些列：

| Name      | Engine | Version | Row_format | Rows | Avg_row_length 
| Data_length | Max_data_length | Index_length | Data_free | Auto_increment 
| Create_time         | Update_time         | Check_time | Collation         
| Checksum | Create_options | Comment |

As you can see there is a column called: "Update_time" that shows you the last update time for your_table.

如您所见，有一列名为“ Update_time”的列显示了your_table的上次更新时间。

回答by Bill Karwin

I'm surprised no one has suggested tracking last update time per row:

我很惊讶没有人建议跟踪每行的上次更新时间：

mysql> CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

mysql> INSERT INTO foo VALUES (1, NOW() - INTERVAL 3 DAY), (2, NOW());

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | NULL | 2013-08-18 03:26:28 |
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

mysql> UPDATE foo SET x = 1234 WHERE id = 1;

This updates the timestamp even though we didn't mention it in the UPDATE.

即使我们没有在 UPDATE 中提到它，这也会更新时间戳。

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | 1235 | 2013-08-21 03:30:20 | <-- this row has been updated
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

Now you can query for the MAX():

现在您可以查询 MAX()：

mysql> SELECT MAX(updated_at) FROM foo;
+---------------------+
| MAX(updated_at)     |
+---------------------+
| 2013-08-21 03:30:20 |
+---------------------+

Admittedly, this requires more storage (4 bytes per row for TIMESTAMP).
But this works for InnoDB tablesbefore 5.7.15 version of MySQL, which INFORMATION_SCHEMA.TABLES.UPDATE_TIMEdoesn't.

诚然，这需要更多的存储空间（TIMESTAMP 每行 4 个字节）。
但这适用于MySQL 5.7.15 版本之前的InnoDB 表，而后者INFORMATION_SCHEMA.TABLES.UPDATE_TIME则不然。

回答by user2654744

The simplest thing would be to check the timestamp of the table files on the disk. For example, You can check under your data directory

最简单的方法是检查磁盘上表文件的时间戳。例如，您可以在您的数据目录下检查

cd /var/lib/mysql/<mydatabase>
ls -lhtr *.ibd

This should give you the list of all tables with the table when it was last modified the oldest time, first.

这应该首先为您提供最后一次修改表的所有表的列表。

回答by Francois Bourgeois

For a list of recent table changes use this:

有关最近表更改的列表，请使用：

SELECT UPDATE_TIME, TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY UPDATE_TIME DESC, TABLE_SCHEMA, TABLE_NAME

回答by Mikhail

I would create a trigger that catches all updates/inserts/deletes and write timestamp in custom table, something like tablename | timestamp

我会创建一个触发器来捕获所有更新/插入/删除并在自定义表中写入时间戳，例如 tablename | 时间戳

Just because I don't like the idea to read internal system tables of db server directly

只是因为我不喜欢直接读取数据库服务器内部系统表的想法

回答by Soul Reaver

Although there is an accepted answer I don't feel that it is the right one. It is the simplest way to achieve what is needed, but even if already enabled in InnoDB (actually docs tell you that you still should get NULL ...), if you read MySQL docs, even in current version (8.0) using UPDATE_TIME is not the right option, because:

尽管有一个公认的答案，但我不认为这是正确的答案。这是实现所需内容的最简单方法，但即使已经在 InnoDB 中启用（实际上文档告诉您仍然应该获取 NULL ...），如果您阅读 MySQL文档，即使在使用 UPDATE_TIME 的当前版本 (8.0) 中也是如此不是正确的选择，因为：

Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

当服务器重新启动或表从 InnoDB 数据字典缓存中被驱逐时，时间戳不会持久化。

If I understand correctly (can't verify it on a server right now), timestamp gets reset after server restart.

如果我理解正确（现在无法在服务器上验证它），服务器重启后时间戳会重置。

As for real (and, well, costly) solutions, you have Bill Karwin's solutionwith CURRENT_TIMESTAMP and I'd like to propose a different one, that is based on triggers (I'm using that one).

至于真正的（而且，成本高昂）的解决方案，您有 Bill Karwin 的CURRENT_TIMESTAMP解决方案，我想提出一个不同的解决方案，它基于触发器（我正在使用那个）。

You start by creating a separate table (or maybe you have some other table that can be used for this purpose) which will work like a storage for global variables (here timestamps). You need to store two fields - table name (or whatever value you'd like to keep here as table id) and timestamp. After you have it, you should initialize it with this table id + starting date (NOW() is a good choice :) ).

您首先创建一个单独的表（或者您可能有其他一些可用于此目的的表），它将像存储全局变量（这里是时间戳）一样工作。您需要存储两个字段 - 表名（或您想在此处保留为表 ID 的任何值）和时间戳。拥有后，您应该使用此表 ID + 开始日期对其进行初始化（NOW() 是一个不错的选择 :)）。

Now, you move to tables you want to observe and add triggers AFTER INSERT/UPDATE/DELETE with this or similar procedure:

现在，您移动到要观察的表并使用以下或类似过程在 INSERT/UPDATE/DELETE 之后添加触发器：

CREATE PROCEDURE `timestamp_update` ()
BEGIN
    UPDATE `SCHEMA_NAME`.`TIMESTAMPS_TABLE_NAME`
    SET `timestamp_column`=DATE_FORMAT(NOW(), '%Y-%m-%d %T')
    WHERE `table_name_column`='TABLE_NAME';
END

回答by John McLean

OS level analysis:

操作系统层面分析：

Find where the DB is stored on disk:

查找数据库在磁盘上的存储位置：

grep datadir /etc/my.cnf
datadir=/var/lib/mysql

Check for most recent modifications

检查最近的修改

cd /var/lib/mysql/{db_name}
ls -lrt

Should work on all database types.

应该适用于所有数据库类型。

回答by saeed

i made a column by name : update-at in phpMyAdmin and got the current time from Date() method in my code (nodejs) . with every change in table this column hold the time of changes.

我按名称创建了一个列：phpMyAdmin 中的 update-at 并从我的代码 (nodejs) 中的 Date() 方法获取当前时间。表中的每一次更改，此列都包含更改的时间。

回答by justnajm

a) It will show you all tables and there last update dates

a) 它将显示所有表格和最后更新日期

SHOW TABLE STATUS FROM db_name;

then, you can further ask for specific table:

然后，您可以进一步询问特定表：

SHOW TABLE STATUS FROM db_name like 'table_name';

b) As in above examples you cannot use sorting on 'Update_time' but using SELECT you can:

b) 在上面的例子中，你不能对 'Update_time' 使用排序，但使用 SELECT 你可以：

SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA='db_name' ORDER BY UPDATE_TIME DESC;

to further ask about particular table:

进一步询问特定表：

SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA='db_name' AND table_name='table_name' ORDER BY UPDATE_TIME DESC';