获取上次更改 MySQL 数据库的日期/时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13403016/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting the date/time of the last change to a MySQL database
提问by Richard Morgan
I'm working with MySQL and I would like to get the date/time of the very last change to any table within a database. Each of my tables has an auto updating '*_modified' timestamp so I could use this (* is a prefix).
我正在使用 MySQL，我想获取数据库中任何表的最后一次更改的日期/时间。我的每个表都有一个自动更新的 '*_modified' 时间戳，所以我可以使用它（* 是一个前缀）。
The purpose of this is to work out if the data has changed at all since the date/time of the last database backup.
这样做的目的是确定自上次数据库备份的日期/时间以来数据是否发生了变化。
I was wondering is there an simpler way to do this using DESCRIBE or SHOW? I've searched and experimented, but found nothing yet.
我想知道有没有更简单的方法可以使用 DESCRIBE 或 SHOW 来做到这一点？我已经搜索并尝试过，但还没有找到。
Thanks for the help.
谢谢您的帮助。
回答by Zak
SELECT update_time
FROM information_schema.tables
WHERE table_schema = 'dbName'
AND table_name = 'tableName'
回答by Giovanni Di Maggio
I found this metod, from http://mysqladministrators.blogspot.it/2012/02/get-database-size.html
我发现这个方法，来自http://mysqladministrators.blogspot.it/2012/02/get-database-size.html
I'm not sure if it can help you, since i'm not so prepared in MySql
我不确定它是否可以帮助您，因为我在 MySql 中还没有准备好
Get the database size, free space and last update
获取数据库大小、可用空间和上次更新
To get the current database size just by querying into your query browser or CLI from the INFORMATION_SCHEMA database in table TABLES.
仅通过从表 TABLES 中的 INFORMATION_SCHEMA 数据库查询查询浏览器或 CLI 来获取当前数据库大小。
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
Get the database free space
获取数据库可用空间
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;
Get the database last update ordered by update time then by create time.
获取按更新时间和创建时间排序的数据库上次更新。
SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA
FROM `TABLES`
GROUP BY TABLE_SCHEMA
ORDER BY 1, 2;
回答by Oliver Williams
The accepted answer is great, but I think a better format in case it's not immediately apparent to a rookie would be:
接受的答案很好，但我认为更好的格式是：
SELECT table_name, update_time
FROM information_schema.tables
WHERE table_schema = 'myDBName'
order by update_time DESC
Since, we don't always know what table(s) have been touched, and this has the bonus of giving a clue as to what the activity was by showing the most-recently-updated tables.
因为，我们并不总是知道哪些表被触摸了，这有一个好处是通过显示最近更新的表来提供有关活动是什么的线索。
回答by Dhanu K
Simply, to get all tables under one database use this query
简单地说，要获取一个数据库下的所有表，请使用此查询
SELECT TABLE_NAME,CREATE_TIME,UPDATE_TIME
FROM information_schema.tables
WHERE table_schema = 'database_name'
That's all
就这样
