資料結構
網路
RDBMS
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化學
生物
數學
英語
經濟學
心理學
社會研究
時尚研究
法律研究MySQL 查詢如何查詢過去一小時內修改的表？
藉助 INFORMATION_SCHEMA.TABLES 可以實現此操作。將 date_sub() 與間隔一起使用。語法如下所示：-
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE DATE_SUB(NOW(), INTERVAL -1HOUR) < ‘UPDATE_TIME’;
現在可以檢查上面的語法。以下查詢用於查詢在過去一小時中修改的表格：-
mysql> select table_name from `INFORMATION_SCHEMA`.`TABLES` -> WHERE -> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;
輸出
+---------------------+ | TABLE_NAME | +---------------------+ | innodb_table_stats | | innodb_index_stats | | employeeinformation | +---------------------+ 3 rows in set (0.37 sec)
上面的查詢只選擇表名。如果需要表架構、表型別等資訊，您需要使用以下查詢。該查詢顯示過去一小時中修改的表格：-
mysql> SELECT * -> FROM `INFORMATION_SCHEMA`.`TABLES` -> WHERE -> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;
以下是輸出，顯示過去一小時中修改的表資訊：-
+---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+ | 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)
更新於：2019 年 7 月 30 日
373 次瀏覽
廣告