问题描述
我正在使用一个数据库,不幸的是,该数据库有很多未使用的表,并且我正在尝试对其进行清理.我正在尝试找到一种方法,使我可以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查找未使用的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!