blocks|key|1685587|text|最简单的方法是检查磁盘上表文件的时间戳。例如，您可以在数据目录下进行检查|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1685588|cd+/var/lib/mysql/<mydatabase>
ls+-lhtr+*.ibd|code-block|syntax|javascript|1685589|这将为您提供该表最后一次修改时的所有表的列表，这是最早的。|1685590|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The simplest thing would be to check the timestamp of the table files on the disk. For example, You can check under your data directory 

<pre><code>cd /var/lib/mysql/&lt;mydatabase&gt;
ls -lhtr *.ibd
</code></pre>

This should give you the list of all tables with the table when it was last modified the oldest time, first.

blocks|key|1685286|text|有关最近表更改的列表，请使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1685287|SELECT+UPDATE_TIME,+TABLE_SCHEMA,+TABLE_NAME
FROM+information_schema.tables
ORDER+BY+UPDATE_TIME+DESC,+TABLE_SCHEMA,+TABLE_NAME|code-block|syntax|javascript|1685288|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For a list of recent table changes use this:
<pre class="lang-sql prettyprint-override"><code>SELECT UPDATE_TIME, TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY UPDATE_TIME DESC, TABLE_SCHEMA, TABLE_NAME
</code></pre>

blocks|key|1470030|text|我将创建一个触发器来捕获所有更新/插入/删除，并在定制表中写入时间戳，类似于tablename+%7C+timestamp|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470031|因为我不喜欢直接读取db+server的内部系统表的想法。|1470032|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I would create a trigger that catches all updates/inserts/deletes and write timestamp in custom table, something like
tablename | timestamp 

Just because I don't like the idea to read internal system tables of db server directly

blocks|key|1685657|text|操作系统级别分析：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1685658|查找DB在磁盘上的存储位置：|1685659|grep+datadir+/etc/my.cnf
datadir=/var/lib/mysql|code-block|syntax|javascript|1685660|检查最近的修改|1685661|cd+/var/lib/mysql/{db_name}
ls+-lrt|1685662|应该适用于所有数据库类型。|1685663|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

OS level analysis:

Find where the DB is stored on disk:

<pre><code>grep datadir /etc/my.cnf
datadir=/var/lib/mysql
</code></pre>

Check for most recent modifications

<pre><code>cd /var/lib/mysql/{db_name}
ls -lrt
</code></pre>

Should work on all database types.

blocks|key|1470588|text|a)它将向您显示所有表和上次更新日期|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470589|SHOW+TABLE+STATUS+FROM+db_name;|code-block|syntax|javascript|1470590|然后，您可以进一步询问具体的表：|1470591|SHOW+TABLE+STATUS+FROM+db_name+like+'table_name';|1470592|b)与上面的示例一样，您不能对'Update_time‘使用排序，但使用SELECT可以：|1470593|SELECT+*+FROM+information_schema.tables+WHERE+TABLE_SCHEMA='db_name'+ORDER+BY+UPDATE_TIME+DESC;|1470594|要进一步询问特定表：|1470595|SELECT+*+FROM+information_schema.tables+WHERE+TABLE_SCHEMA='db_name'+AND+table_name='table_name'+ORDER+BY+UPDATE_TIME+DESC';|1470596|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

a) It will show you all tables and there last update dates

<pre><code>SHOW TABLE STATUS FROM db_name;
</code></pre>

then, you can further ask for specific table:

<pre><code>SHOW TABLE STATUS FROM db_name like 'table_name';
</code></pre>

b) As in above examples you cannot use sorting on 'Update_time' but using SELECT you can:

<pre><code>SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA='db_name' ORDER BY UPDATE_TIME DESC;
</code></pre>

to further ask about particular table:

<pre><code>SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA='db_name' AND table_name='table_name' ORDER BY UPDATE_TIME DESC';
</code></pre>

blocks|key|1470127|text|只需从文件系统中抓取修改后的文件日期即可。用我的语言来说就是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470128|+tbl_updated+=+file.update_time(
++++++++"C:\ProgramData\MySQL\MySQL+Server+5.5\data\mydb\person.frm")|code-block|syntax|javascript|1470129|输出：|1470130|1/25/2013+06:04:10+AM|1470131|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Just grab the file date modified from file system. In my language that is:

<pre><code> tbl_updated = file.update_time(
 "C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\person.frm")
</code></pre>

Output:

<pre><code>1/25/2013 06:04:10 AM
</code></pre>

blocks|key|1685411|text|如果您运行的是Linux，那么可以使用inotify来查看表或数据库目录。inotify可以从PHP、node.js、perl和大多数其他语言中获得。当然，您必须安装inotify或让您的ISP安装它。很多ISP不会这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1685412|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you are running Linux you can use inotify to look at the table or the database directory. inotify is available from PHP, node.js, perl and I suspect most other languages. Of course you must have installed inotify or had your ISP install it. A lot of ISP will not.

blocks|key|1685509|text|不确定这是否会引起任何兴趣。在mysql和客户端之间使用mysqlproxy，并使用lua脚本根据感兴趣的表更改更新memcached中的键值UPDATE，DELETE，INSERT是我最近做的解决方案。如果包装器支持php中的钩子或触发器，这可能会更容易。到目前为止，没有一个包装器可以做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1685510|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Not sure if this would be of any interest. Using mysqlproxy in between mysql and clients, and making use of a lua script to update a key value in memcached according to interesting table changes UPDATE,DELETE,INSERT was the solution which I did quite recently. If the wrapper supported hooks or triggers in php, this could have been eaiser. None of the wrappers as of now does this.

blocks|key|1470492|text|我在phpMyAdmin中创建了一个名为update-at的列，并在我的代码(nodejs)中获取了Date()方法的当前时间。对于表中的每一次更改，此列保存更改的时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470493|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

i made a column by name : update-at in phpMyAdmin and got the current time from Date() method in my code (nodejs) . with every change in table this column hold the time of changes.

blocks|key|1685928|text|与其他条件相同，但为了节省时间，我使用了一些条件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1685929|SELECT
++UPDATE_TIME,
++TABLE_SCHEMA,
++TABLE_NAME
FROM
++information_schema.tables
WHERE
++1+=+1
++AND+UPDATE_TIME+>+'2021-11-09+00:00:00'
++AND+TABLE_SCHEMA+=+'db_name_here'
++AND+TABLE_NAME+not+in+('table_name_here',)
ORDER+BY
++UPDATE_TIME+DESC,
++TABLE_SCHEMA,
++TABLE_NAME;|code-block|syntax|javascript|1685930|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Same as others, but with some conditions i've used, to save time:
<pre><code>SELECT
 UPDATE_TIME,
 TABLE_SCHEMA,
 TABLE_NAME
FROM
 information_schema.tables
WHERE
 1 = 1
 AND UPDATE_TIME &gt; '2021-11-09 00:00:00'
 AND TABLE_SCHEMA = 'db_name_here'
 AND TABLE_NAME not in ('table_name_here',)
ORDER BY
 UPDATE_TIME DESC,
 TABLE_SCHEMA,
 TABLE_NAME;
</code></pre>

blocks|key|1470000|text|这就是我所做的，我希望它能有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1470001|<?php
++++mysql_connect("localhost",+"USER",+"PASSWORD")+or+die(mysql_error());
++++mysql_select_db("information_schema")+or+die(mysql_error());
++++$query1+=+"SELECT+`UPDATE_TIME`+FROM+`TABLES`+WHERE
++++++++`TABLE_SCHEMA`+LIKE+'DataBaseName'+AND+`TABLE_NAME`+LIKE+'TableName'";
++++$result1+=+mysql_query($query1)+or+die(mysql_error());
++++while($row+=+mysql_fetch_array($result1))+{
++++++++echo+"1r+tr.:+".$row['UPDATE_TIME'];
++++}
?>|code-block|syntax|javascript|1470002|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is what I did, I hope it helps.

<pre><code>&lt;?php
 mysql_connect("localhost", "USER", "PASSWORD") or die(mysql_error());
 mysql_select_db("information_schema") or die(mysql_error());
 $query1 = "SELECT `UPDATE_TIME` FROM `TABLES` WHERE
 `TABLE_SCHEMA` LIKE 'DataBaseName' AND `TABLE_NAME` LIKE 'TableName'";
 $result1 = mysql_query($query1) or die(mysql_error());
 while($row = mysql_fetch_array($result1)) {
 echo "&lt;strong&gt;1r tr.: &lt;/strong&gt;".$row['UPDATE_TIME'];
 }
?&gt;
</code></pre>

blocks|key|1685196|text|当全局变量不可用时，将查询缓存到全局变量中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1685197|创建一个网页，强制在更新时重新加载缓存。|1685198|将对重载页面的调用添加到部署脚本中。|1685199|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Cache the query in a global variable when it is not available.

Create a webpage to force the cache to be reloaded when you update it.

Add a call to the reloading page into your deployment scripts.

In the footer of my page, I would like to add something like "last updated the xx/xx/200x" with this date being the last time a certain mySQL table has been updated.

What is the best way to do that? Is there a function to retrieve the last updated date? Should I access to the database every time I need this value?

How can I tell when a MySQL table was last updated?

在我的页面的页脚，我想添加一些类似于“上次更新xx/xx/200x”的内容，这个日期是某个mySQL表的最后一次更新。做到这一点的最好方法是什么？有没有检索上次更新日期的函数？我应该在每次需要这个值时都访问数据库吗？

问如何判断MySQL表的最后更新时间？
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何判断MySQL表的最后更新时间？EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何判断MySQL表的最后更新时间？
EN