MySQL查找未使用的表 [英] MySQL find unused tables

问题描述

我正在使用一个数据库，不幸的是，该数据库有很多未使用的表，并且我正在尝试对其进行清理.我正在尝试找到一种方法，使我可以100％确信不再使用特定表.

I am working with a database which unfortunately has a lot of unused tables and I am trying to clean it up. I'm trying to find a way where I can be 100% confident that a particular table is no longer being used.

经过一番谷歌搜索后，我仍然找不到找到它的好方法.我只能使用以下命令告诉对表的最后一次写入(INSERT，UPDATE等):

After some googling, I still cannot find a good way to do so. I am only able to tell the last writes to a table (INSERT, UPDATE, etc) using:

SHOW TABLE STATUS或在mysql数据目录上运行ls -lt(可以通过运行SHOW VARIABLES LIKE'datadir';找到

SHOW TABLE STATUS or running ls -lt on the mysql datadir (can be found by by running SHOW VARIABLES LIKE 'datadir';)

您还有其他建议吗?

谢谢.

推荐答案

尝试使用INFORMATION_SCHEMA.TABLES

Try using the INFORMATION_SCHEMA.TABLES

有一列称为UPDATE_TIME

There is a column called UPDATE_TIME

在该字段中检查日期

如果为NULL，则表创建以来从未更新过

If it is NULL, the table has never been updated since the table's creation

示例:最近10天未更新的表的列表

Example : A list of tables not updated in the last 10 days

select table_schema,table_name,create_time,update_time from information_schema.tables where table_schema not in ('information_schema','mysql') and engine is not null and ((update_time < (now() - interval 10 day)) or update_time is null);

试试看！

这篇关于MySQL查找未使用的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！