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.
在我的页面页脚，我想添加一些类似“上次更新xx/xx/200x”的内容，这个日期是最近一次更新某个mySQL表。
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?
最好的方法是什么?是否有一个函数来检索最近更新的日期?我是否应该在每次需要这个值时访问数据库?
13 个解决方案
#1
226
In later versions of MySQL you can use the information_schema database to tell you when another table was updated:
在以后的MySQL版本中，您可以使用information_schema数据库来告诉您何时更新了另一个表:
SELECT UPDATE_TIME
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'dbname'
AND TABLE_NAME = 'tabname'
This does of course mean opening a connection to the database.
这当然意味着打开与数据库的连接。
An alternative option would be to "touch" a particular file whenever the MySQL table is updated:
另一种选择是在更新MySQL表时“触摸”特定文件:
On database updates:
数据库更新:
- Open your timestamp file in
O_RDRWmode - 以O_RDRW模式打开时间戳文件
-
closeit again - 再次关闭它
or alternatively
或者
- use
touch(), the PHP equivalent of theutimes()function, to change the file timestamp. - 使用相当于utimes()函数的PHP touch()来更改文件的时间戳。
On page display:
页面显示:
- use
stat()to read back the file modification time. - 使用stat()读取文件修改时间。
#2
50
I don't have information_schema database, using mysql version 4.1.16, so in this case you can query this:
我没有information_schema数据库，使用mysql version 4.1.16，所以在这种情况下，您可以查询:
SHOW TABLE STATUS FROM your_database LIKE 'your_table';
It will return these columns:
它将返回这些列:
| Name | Engine | Version | Row_format | Rows | Avg_row_length
| Data_length | Max_data_length | Index_length | Data_free | Auto_increment
| Create_time | Update_time | Check_time | Collation
| Checksum | Create_options | Comment |
As you can see there is a column called: "Update_time" that shows you the last update time for your_table.
正如您所看到的，有一个名为“Update_time”的列，它向您显示了your_table的最后一个更新时间。
#3
43
I'm surprised no one has suggested tracking last update time per row:
我很惊讶没有人建议跟踪每一行的更新时间:
mysql> CREATE TABLE foo (
id INT PRIMARY KEY
x INT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
KEY (updated_at)
);
mysql> INSERT INTO foo VALUES (1, NOW() - INTERVAL 3 DAY), (2, NOW());
mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x | updated_at |
+----+------+---------------------+
| 1 | NULL | 2013-08-18 03:26:28 |
| 2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+
mysql> UPDATE foo SET x = 1234 WHERE id = 1;
This updates the timestamp even though we didn't mention it in the UPDATE.
这更新了时间戳，尽管我们在更新中没有提到它。
mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x | updated_at |
+----+------+---------------------+
| 1 | 1235 | 2013-08-21 03:30:20 | <-- this row has been updated
| 2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+
Now you can query for the MAX():
现在您可以查询MAX():
mysql> SELECT MAX(updated_at) FROM foo;
+---------------------+
| MAX(updated_at) |
+---------------------+
| 2013-08-21 03:30:20 |
+---------------------+
Admittedly, this requires more storage (4 bytes per row for TIMESTAMP).
But this works for InnoDB tables before 5.7.15 version of MySQL, which INFORMATION_SCHEMA.TABLES.UPDATE_TIME doesn't.
诚然，这需要更多的存储空间(时间戳每一行4字节)。但在5.7.15版本的MySQL (INFORMATION_SCHEMA.TABLES)之前，这对InnoDB表有效。UPDATE_TIME没有。
#4
5
I would create a trigger that catches all updates/inserts/deletes and write timestamp in custom table, something like tablename | timestamp
我将创建一个触发器，它捕获所有更新/插入/删除，并在自定义表中写入时间戳，类似于tablename |时间戳
Just because I don't like the idea to read internal system tables of db server directly
只是因为我不喜欢直接读取db服务器的内部系统表。
#5
4
For a list of recent table changes use this:
对于最近的表更改列表，请使用以下内容:
SELECT UPDATE_TIME, TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY UPDATE_TIME DESC, TABLE_SCHEMA, TABLE_NAME
#6
3
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
最简单的方法是检查磁盘上表文件的时间戳。例如，可以在数据目录下进行检查
cd /var/lib/mysql/<mydatabase>
ls -lhtr *.ibd
This should give you the list of all tables with the table when it was last modified the oldest time, first.
这将首先给出最后一次修改该表时所有表的列表。
#7
0
Just grab the file date modified from file system. In my language that is:
只需从文件系统中获取修改后的文件日期。我的语言是:
tbl_updated = file.update_time(
"C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\person.frm")
Output:
输出:
1/25/2013 06:04:10 AM
#8
0
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.
如果您正在运行Linux，您可以使用inotify来查看表或数据库目录。inotify可从PHP node获得。js、perl和我怀疑大多数其他语言。当然，您必须安装了inotify或让ISP安装了它。很多ISP不会。
#9
0
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.
我不确定这是否有什么意义。在mysql和客户端之间使用mysqlproxy，使用lua脚本根据有趣的表更改更新、删除、插入更新memcached内的键值，这是我最近做的解决方案。如果包装器支持php中的钩子或触发器，那么这可以是eaiser。到目前为止，所有的包装器都没有这样做。
#10
0
OS level analysis:
操作系统级别的分析:
Find where the DB is stored on disk:
查找数据库存储在磁盘上的位置:
grep datadir /etc/my.cnf
datadir=/var/lib/mysql
Check for most recent modifications
检查最近的修改。
cd /var/lib/mysql/{db_name}
ls -lrt
Should work on all database types.
应该适用于所有数据库类型。
#11
0
Although there is an accepted answer I don't feel that it is the right one. It is the simplest way to achieve what is needed, but even if already enabled in InnoDB (actually docs tell you that you still should get NULL ...), if you read MySQL docs, even in current version (8.0) using UPDATE_TIME is not the right option, because:
虽然有一个公认的答案，但我不认为它是正确的。这是实现所需的最简单的方法，但是即使在InnoDB中已经启用(实际上文档告诉您仍然应该得到NULL…)，如果您阅读MySQL文档，即使在当前版本(8.0)中使用UPDATE_TIME也不是正确的选项，因为:
Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.
当服务器重新启动或表被从InnoDB数据字典缓存中删除时，不会持久化时间戳。
If I understand correctly (can't verify it on a server right now), timestamp gets reset after server restart.
如果我理解正确(现在无法在服务器上验证它)，那么时间戳将在服务器重启后重置。
As for real (and, well, costly) solutions, you have Bill Karwin's solution with CURRENT_TIMESTAMP and I'd like to propose a different one, that is based on triggers (I'm using that one).
至于真正(而且代价高昂)的解决方案，您有Bill Karwin的具有CURRENT_TIMESTAMP的解决方案，我想提出另一个基于触发器的解决方案(我正在使用那个)。
You start by creating a separate table (or maybe you have some other table that can be used for this purpose) which will work like a storage for global variables (here timestamps). You need to store two fields - table name (or whatever value you'd like to keep here as table id) and timestamp. After you have it, you should initialize it with this table id + starting date (NOW() is a good choice :) ).
您首先要创建一个单独的表(或者您还有其他可以用于此目的的表)，它将像全局变量的存储(这里是时间戳)一样工作。您需要存储两个字段——表名(或您希望在这里作为表id保留的任何值)和时间戳。有了它之后，应该用这个表id +开始日期(现在()是一个不错的选择)来初始化它。
Now, you move to tables you want to observe and add triggers AFTER INSERT/UPDATE/DELETE with this or similar procedure:
现在，您移动到您想要观察的表和添加触发器后，插入/更新/删除使用以下或类似的过程:
CREATE PROCEDURE `timestamp_update` ()
BEGIN
UPDATE `SCHEMA_NAME`.`TIMESTAMPS_TABLE_NAME`
SET `timestamp_column`=DATE_FORMAT(NOW(), '%Y-%m-%d %T')
WHERE `table_name_column`='TABLE_NAME';
END
#12
-1
This is what I did, I hope it helps.
这就是我所做的，我希望它能有所帮助。
<?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 "<strong>1r tr.: </strong>".$row['UPDATE_TIME'];
}
?>
#13
-8
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.
将一个调用添加到重新加载页面到部署脚本中。
#1
226
In later versions of MySQL you can use the information_schema database to tell you when another table was updated:
在以后的MySQL版本中，您可以使用information_schema数据库来告诉您何时更新了另一个表:
SELECT UPDATE_TIME
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'dbname'
AND TABLE_NAME = 'tabname'
This does of course mean opening a connection to the database.
这当然意味着打开与数据库的连接。
An alternative option would be to "touch" a particular file whenever the MySQL table is updated:
另一种选择是在更新MySQL表时“触摸”特定文件:
On database updates:
数据库更新:
- Open your timestamp file in
O_RDRWmode - 以O_RDRW模式打开时间戳文件
-
closeit again - 再次关闭它
or alternatively
或者
- use
touch(), the PHP equivalent of theutimes()function, to change the file timestamp. - 使用相当于utimes()函数的PHP touch()来更改文件的时间戳。
On page display:
页面显示:
- use
stat()to read back the file modification time. - 使用stat()读取文件修改时间。
#2
50
I don't have information_schema database, using mysql version 4.1.16, so in this case you can query this:
我没有information_schema数据库，使用mysql version 4.1.16，所以在这种情况下，您可以查询:
SHOW TABLE STATUS FROM your_database LIKE 'your_table';
It will return these columns:
它将返回这些列:
| Name | Engine | Version | Row_format | Rows | Avg_row_length
| Data_length | Max_data_length | Index_length | Data_free | Auto_increment
| Create_time | Update_time | Check_time | Collation
| Checksum | Create_options | Comment |
As you can see there is a column called: "Update_time" that shows you the last update time for your_table.
正如您所看到的，有一个名为“Update_time”的列，它向您显示了your_table的最后一个更新时间。
#3
43
I'm surprised no one has suggested tracking last update time per row:
我很惊讶没有人建议跟踪每一行的更新时间:
mysql> CREATE TABLE foo (
id INT PRIMARY KEY
x INT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
KEY (updated_at)
);
mysql> INSERT INTO foo VALUES (1, NOW() - INTERVAL 3 DAY), (2, NOW());
mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x | updated_at |
+----+------+---------------------+
| 1 | NULL | 2013-08-18 03:26:28 |
| 2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+
mysql> UPDATE foo SET x = 1234 WHERE id = 1;
This updates the timestamp even though we didn't mention it in the UPDATE.
这更新了时间戳，尽管我们在更新中没有提到它。
mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x | updated_at |
+----+------+---------------------+
| 1 | 1235 | 2013-08-21 03:30:20 | <-- this row has been updated
| 2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+
Now you can query for the MAX():
现在您可以查询MAX():
mysql> SELECT MAX(updated_at) FROM foo;
+---------------------+
| MAX(updated_at) |
+---------------------+
| 2013-08-21 03:30:20 |
+---------------------+
Admittedly, this requires more storage (4 bytes per row for TIMESTAMP).
But this works for InnoDB tables before 5.7.15 version of MySQL, which INFORMATION_SCHEMA.TABLES.UPDATE_TIME doesn't.
诚然，这需要更多的存储空间(时间戳每一行4字节)。但在5.7.15版本的MySQL (INFORMATION_SCHEMA.TABLES)之前，这对InnoDB表有效。UPDATE_TIME没有。
#4
5
I would create a trigger that catches all updates/inserts/deletes and write timestamp in custom table, something like tablename | timestamp
我将创建一个触发器，它捕获所有更新/插入/删除，并在自定义表中写入时间戳，类似于tablename |时间戳
Just because I don't like the idea to read internal system tables of db server directly
只是因为我不喜欢直接读取db服务器的内部系统表。
#5
4
For a list of recent table changes use this:
对于最近的表更改列表，请使用以下内容:
SELECT UPDATE_TIME, TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY UPDATE_TIME DESC, TABLE_SCHEMA, TABLE_NAME
#6
3
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
最简单的方法是检查磁盘上表文件的时间戳。例如，可以在数据目录下进行检查
cd /var/lib/mysql/<mydatabase>
ls -lhtr *.ibd
This should give you the list of all tables with the table when it was last modified the oldest time, first.
这将首先给出最后一次修改该表时所有表的列表。
#7
0
Just grab the file date modified from file system. In my language that is:
只需从文件系统中获取修改后的文件日期。我的语言是:
tbl_updated = file.update_time(
"C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\person.frm")
Output:
输出:
1/25/2013 06:04:10 AM
#8
0
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.
如果您正在运行Linux，您可以使用inotify来查看表或数据库目录。inotify可从PHP node获得。js、perl和我怀疑大多数其他语言。当然，您必须安装了inotify或让ISP安装了它。很多ISP不会。
#9
0
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.
我不确定这是否有什么意义。在mysql和客户端之间使用mysqlproxy，使用lua脚本根据有趣的表更改更新、删除、插入更新memcached内的键值，这是我最近做的解决方案。如果包装器支持php中的钩子或触发器，那么这可以是eaiser。到目前为止，所有的包装器都没有这样做。
#10
0
OS level analysis:
操作系统级别的分析:
Find where the DB is stored on disk:
查找数据库存储在磁盘上的位置:
grep datadir /etc/my.cnf
datadir=/var/lib/mysql
Check for most recent modifications
检查最近的修改。
cd /var/lib/mysql/{db_name}
ls -lrt
Should work on all database types.
应该适用于所有数据库类型。
#11
0
Although there is an accepted answer I don't feel that it is the right one. It is the simplest way to achieve what is needed, but even if already enabled in InnoDB (actually docs tell you that you still should get NULL ...), if you read MySQL docs, even in current version (8.0) using UPDATE_TIME is not the right option, because:
虽然有一个公认的答案，但我不认为它是正确的。这是实现所需的最简单的方法，但是即使在InnoDB中已经启用(实际上文档告诉您仍然应该得到NULL…)，如果您阅读MySQL文档，即使在当前版本(8.0)中使用UPDATE_TIME也不是正确的选项，因为:
Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.
当服务器重新启动或表被从InnoDB数据字典缓存中删除时，不会持久化时间戳。
If I understand correctly (can't verify it on a server right now), timestamp gets reset after server restart.
如果我理解正确(现在无法在服务器上验证它)，那么时间戳将在服务器重启后重置。
As for real (and, well, costly) solutions, you have Bill Karwin's solution with CURRENT_TIMESTAMP and I'd like to propose a different one, that is based on triggers (I'm using that one).
至于真正(而且代价高昂)的解决方案，您有Bill Karwin的具有CURRENT_TIMESTAMP的解决方案，我想提出另一个基于触发器的解决方案(我正在使用那个)。
You start by creating a separate table (or maybe you have some other table that can be used for this purpose) which will work like a storage for global variables (here timestamps). You need to store two fields - table name (or whatever value you'd like to keep here as table id) and timestamp. After you have it, you should initialize it with this table id + starting date (NOW() is a good choice :) ).
您首先要创建一个单独的表(或者您还有其他可以用于此目的的表)，它将像全局变量的存储(这里是时间戳)一样工作。您需要存储两个字段——表名(或您希望在这里作为表id保留的任何值)和时间戳。有了它之后，应该用这个表id +开始日期(现在()是一个不错的选择)来初始化它。
Now, you move to tables you want to observe and add triggers AFTER INSERT/UPDATE/DELETE with this or similar procedure:
现在，您移动到您想要观察的表和添加触发器后，插入/更新/删除使用以下或类似的过程:
CREATE PROCEDURE `timestamp_update` ()
BEGIN
UPDATE `SCHEMA_NAME`.`TIMESTAMPS_TABLE_NAME`
SET `timestamp_column`=DATE_FORMAT(NOW(), '%Y-%m-%d %T')
WHERE `table_name_column`='TABLE_NAME';
END
#12
-1
This is what I did, I hope it helps.
这就是我所做的，我希望它能有所帮助。
<?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 "<strong>1r tr.: </strong>".$row['UPDATE_TIME'];
}
?>
#13
-8
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.
将一个调用添加到重新加载页面到部署脚本中。