
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
MySQL Query to find tables modified in the last hour?
You can achieve this with the help of INFORMATION_SCHEMA.TABLES. Use the date_sub() with interval. The syntax is as follows −
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE DATE_SUB(NOW(), INTERVAL -1HOUR) < ‘UPDATE_TIME’;
Now you can check the above syntax. Here is the query to find the tables modified in the last 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 selects only table name. If you want information like table schema, table type etc, you need to use the following query. The query displays the table 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 displaying 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)
- Related Articles
- How to find tables modified in the last hour in Android sqlite?
- MySQL query to find the number of rows in the last query
- MySQL query to count rows in multiple tables
- Fetching rows added in last hour with MySQL?
- A single MySQL query to find the highest and lowest among two tables?
- How to get file last modified time in Java?
- Write a MySQL query equivalent to “SHOW TABLES” in sorted order?
- MongoDB query to find last object in collection?
- What is the MySQL query to display the number of tables in a database?
- MySQL query to search within the last 5 characters in a column?
- How to count rows from two tables in a single MySQL query?
- A single query to get the sum of count from different tables in MySQL?
- MySQL query to extract last word from a field?
- MySQL query to sort by certain last string character?
- MySQL query to get the last created table name (most recent)?

Advertisements