MySQL query to find tables modified in the last hour?

You can do this with the help of INFORMATION_SCHEMA.TABLES. Use date_sub() with intervals. The syntax is as follows -

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE DATE_SUB(NOW(), INTERVAL -1HOUR) < &lsquo;UPDATE_TIME&rsquo;;

Now you can check the above syntax. This is a query to find tables modified within the past hour -

mysql> select table_name from `INFORMATION_SCHEMA`.`TABLES`
-> WHERE
-> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;

Output

+---------------------+
| TABLE_NAME          |
+---------------------+
| innodb_table_stats  |
| innodb_index_stats  |
| employeeinformation |
+---------------------+
3 rows in set (0.37 sec)

The above query only selects table names. If you want information like table schema, table type, etc., you need to use the following query. The query displays the tables modified in the last hour -

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

The following is the output showing the table information modified in the last hour -

+---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | TABLE_TYPE  | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+
| def           | mysql        | innodb_table_stats  | BASE TABLE | InnoDB | 10      | Dynamic    | 449        | 145            | 65536       | 0               | 0            | 4194304   | NULL | 2018-09-22 20:37:26 | 2018-12-24 15:13:41 | NULL | utf8_bin | NULL |
row_format=DYNAMIC stats_persistent=0 | |
| def           | mysql        | innodb_index_stats  | BASE TABLE | InnoDB | 10      | Dynamic    | 1413       | 243            | 344064      | 0               | 0            | 4194304   | NULL | 2018-09-22 20:37:26 | 2018-12-24 15:13:41 | NULL | utf8_bin | NULL |
row_format=DYNAMIC stats_persistent=0 | |
| def           | test         | employeeinformation | BASE TABLE | InnoDB | 10      | Dynamic    | 6          | 2730           | 16384       | 0               | 0            | 0         | NULL | 2018-12-24 15:12:04 | 2018-12-24 15:14:00 | NULL | utf8mb4_0900_ai_ci | NULL | |
|
+---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+
3 rows in set (43.72 sec)

The above is the detailed content of MySQL query to find tables modified in the last hour?. For more information, please follow other related articles on the PHP Chinese website!