查找最近一小时被修改的表的查询

Question

我想查找在MySQL数据库中最近一小时内被修改过的表。我该怎么做？

- agente_secreto

---

你的表格中是否有一个时间戳列来记录数据的修改时间？ - TomTom

@dotancohen 你是否正在使用innodb_file_per_table？ - RolandoMySQLDBA

你好，Rolando！这个项目很重要的是MySQL 5.5版本，但我希望有一个版本无关的解决方案，例如Ian提供的答案。那可能是一个好的解决方案。此外，似乎MySQL 5.7将启用InnoDB的UPDATE_TIME，但该值不会在mysqld重新启动后持续存在。 - dotancohen

@RolandoMySQLDBA：您能否发布一个带有dba.SE链接的答案，因为那似乎是最好的答案，我想接受它。谢谢！ - dotancohen

@dotancohen 我在12分钟前添加了我的答案，并进行了轻微的更新。 - RolandoMySQLDBA

Answer 1

MySQL 5.x可以通过INFORMATION_SCHEMA数据库实现此功能。该数据库包含有关表、视图、列等信息。

SELECT * 
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE 
    DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`

返回在过去一小时内已更新（UPDATE_TIME）的所有表。您还可以按数据库名称（TABLE_SCHEMA列）进行筛选。

示例查询：

SELECT 
    CONCAT(`TABLE_SCHEMA`, '.', `TABLE_NAME`) AS `Table`, 
    UPDATE_TIME AS `Updated`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
    DATE_SUB(NOW(), INTERVAL 3 DAY) < `UPDATE_TIME`
    AND `TABLE_SCHEMA` != 'INFORMATION_SCHEMA'
    AND `TABLE_TYPE` = 'BASE TABLE';

- rjh

---

请注意，这种方法仅适用于MyISAM表，而不适用于InnoDB表。 - Ike Walker

对于InnoDB，我很难找到替代方案。也许可以在插入和删除操作上使用触发器。 - rjh

正如Ike Walker上面提到的，它不适用于InnoDB。information_schema.TABLES中的UPDATE_TIME列从未更新。因此，所提到的查询将失败。 - RRM

对于mysql 5.7.15版本，该请求在InnoDB表上运行良好。 - Zur13

是的，我确认@Zur13的信息，我的MySQL服务器在5.7.19版本中有不同数据库中的myISAM和InnoDB表，并且对于所有表都可以正常工作。 - Meloman

我正在寻找一种Postgres友好的方法，但这并不起作用。请指导我正确的问答。 - computingfreak

Answer 2

对于每个您想要检测更改的表，您需要有一个保存上次更改时间戳的列。

对于表中的每个插入或更新，您需要使用当前日期和时间更新该列。

或者，您可以设置一个触发器，在每次插入或修改时自动更新该列。这样，您就不必修改所有查询。

一旦这样做了，要查找最近一小时内表中行是否已被修改，请执行查询

select count(*) from mytable where datemod>subtime(now(),'1:0:0')

对于您想要检查的每个表格都重复此操作。

- David V.

Answer 3

InnoDB目前仍缺乏本地机制来检索此信息。在MySQL相关功能请求中，有人建议对要监视的每个表设置AFTER [all events]触发器。 触发器将发出以下语句：

INSERT INTO last_update VALUE ('current_table_name', NOW())
ON DUPLICATE KEY UPDATE update_time = NOW();

在这样的表格中：

CREATE TABLE last_update (
    table_name VARCHAR(64) PRIMARY KEY,
    update_time DATETIME
) ENGINE = MyISAM; -- no need for transactions here

如果您可以接受数据略微不准确（范围在一秒内），并且可以读取MySQL数据文件，则可以切换到设置为inndb_files_per_table = ON（无论如何都建议这样做），并检查基础数据文件的最后修改时间。

在大多数默认安装中，这些文件位于/var/lib/mysql/[database_name]/*.ibd下。

请注意，如果您决定采用此方法，您需要重新创建现有表格以应用新的设置。

- RandomSeed

---

你可能想使用TIMESTAMP数据类型而不是DATETIME。将其定义为“update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP”，如果省略，则在插入数据时会自动设置它。 - DustWolf

Answer 4

大约1.5年前，我在DBA StackExchange上回答过类似的问题：如何快速检查InnoDB表是否已更改。

基于那篇旧文章的答案，我建议采取以下措施：

将写入刷新到磁盘

这是一次性设置。您需要将innodb_max_dirty_pages_pct设置为0。

首先，将以下内容添加到/etc/my.cnf文件中：

[mysqld]
innodb_max_dirty_pages_pct=0

然后，运行以下命令以避免重新启动mysql：

mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

获取InnoDB表的.ibd文件的时间戳

ls命令有一个选项可以以秒为单位检索UNIX时间戳。对于InnoDB表mydb.mytable

$ cd /var/lib/mysql/mydb
$ ls -l --time-style="+%s" mytable.ibd | awk '{print $6}'

您可以计算UNIX_TIMESTAMP(NOW()) - (.ibd文件的时间戳)，并查看它是否小于或等于3600秒。

试一试吧！！！

- RolandoMySQLDBA

Answer 5

SELECT *
FROM  information_schema.tables
WHERE UPDATE_TIME >= SYSDATE() - INTERVAL 1 DAY  && TABLE_TYPE != 'SYSTEM VIEW'

SELECT * 
FROM information_schema.tables
WHERE UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) && TABLE_TYPE != 'SYSTEM VIEW'

- user1140237