MySQL 查询以查找最近一小时内修改的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2492040/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to find tables modified in the last hour
提问by agente_secreto
I want to find out which tables have been modified in the last hour in a MySQL database. How can I do this?
我想找出 MySQL 数据库中最近一个小时内修改了哪些表。我怎样才能做到这一点？
回答by rjh
MySQL 5.x can do this via the INFORMATION_SCHEMA database. This database contains information about tables, views, columns, etc.
MySQL 5.x 可以通过 INFORMATION_SCHEMA 数据库做到这一点。该数据库包含有关表、视图、列等的信息。
SELECT *
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`
Returns all tables that have been updated (UPDATE_TIME) in the last hour. You can also filter by database name (TABLE_SCHEMA column).
返回过去一小时内已更新 (UPDATE_TIME) 的所有表。您还可以按数据库名称（TABLE_SCHEMA 列）进行过滤。
An example query:
一个示例查询：
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';
回答by David V.
For each table you want to detect change, you need to have a column that holds the last change's timestamp.
对于要检测更改的每个表，您需要有一列保存上次更改的时间戳。
For every insert or update in the table, you need to update that column with the current date and time.
对于表中的每个插入或更新，您需要使用当前日期和时间更新该列。
Alternatively, you can set up a triggerwhich updates the column automatically on each insert or modify. That way you don't have to modify all of your query.
或者，您可以设置一个触发器，在每次插入或修改时自动更新列。这样您就不必修改所有查询。
Once this works, to find out if rows from a table have been modified in the last hour, perform the query
一旦成功，要找出表中的行是否在过去一小时内被修改，请执行查询
select count(*) from mytable where datemod>subtime(now(),'1:0:0')
Repeat for every table you want to check.
对要检查的每个表重复此操作。
回答by RandomSeed
InnoDB still currently lacks a native mechanism to retreive this information. In the related feature request at MySQL, someone advises to set AFTER [all events]triggers on each table to be monitored. The trigger would issue a statement such as
InnoDB 目前仍然缺乏一个本地机制来检索这些信息。在MySQL的相关功能请求中，有人建议AFTER [all events]在每个要监控的表上设置触发器。触发器会发出一个语句，例如
INSERT INTO last_update VALUE ('current_table_name', NOW())
ON DUPLICATE KEY UPDATE update_time = NOW();
in a table like this:
在这样的表中：
CREATE TABLE last_update (
table_name VARCHAR(64) PRIMARY KEY,
update_time DATETIME
) ENGINE = MyISAM; -- no need for transactions here
Alternatively, if a slight inaccuracy in this data (in the range of one second) is acceptable, and if you have read access to the MySQL data files, you could switch to a setting where inndb_files_per_table = ON(recommended in any case) and check the last modification time of the underlying data files.
或者，如果此数据中的轻微不准确（在一秒范围内）是可以接受的，并且如果您对 MySQL 数据文件具有读取权限，则可以切换到一个设置inndb_files_per_table = ON（在任何情况下都推荐）并检查最后一次修改底层数据文件的时间。
These files are found under /var/lib/mysql/[database_name]/*.ibdin most default installations.
这些文件/var/lib/mysql/[database_name]/*.ibd位于大多数默认安装下。
Please note, if you decide to take this route, you need to recreate existing tablesfor the new setting to apply.
请注意，如果您决定采用此路线，则需要重新创建现有表以应用新设置。
回答by RolandoMySQLDBA
I have answered a question like this in the DBA StackExchange about 1.5 years ago: Fastest way to check if InnoDB table has changed.
大约 1.5 年前，我在 DBA StackExchange 中回答了这样一个问题：Fastest way to check if InnoDB table has changed。
Based on that old answer, I recommend the following
基于那个旧答案，我推荐以下内容
Flushing Writes to Disk
刷新磁盘写入
This is a one-time setup. You need to set innodb_max_dirty_pages_pctto 0.
这是一次性设置。您需要将innodb_max_dirty_pages_pct设置为 0。
First, add this to /etc/my.cnf
首先，将此添加到 /etc/my.cnf
[mysqld]
innodb_max_dirty_pages_pct=0
Then, run this to avoid having to restart mysql:
然后，运行它以避免重新启动mysql：
mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;
Get Timestamp of InnoDB table's .ibd file
获取 InnoDB 表的时间戳 .ibd file
lshas the option to retrieve the UNIX timestamp in Seconds. For an InnoDB table mydb.mytable
ls可以选择以秒为单位检索 UNIX 时间戳。对于 InnoDB 表mydb.mytable
$ cd /var/lib/mysql/mydb
$ ls -l --time-style="+%s" mytable.ibd | awk '{print }'
You can then compute UNIX_TIMESTAMP(NOW())- (timestamp of the .ibd file) and see if it is 3600 or less.
然后您可以计算UNIX_TIMESTAMP(NOW())- ( timestamp of the .ibd file) 并查看它是否为 3600 或更少。
Give it a Try !!!
试一试 ！！！
回答by user1140237
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'
