How can I tell when a MySQL table was last updated

问题:

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?

回答1:

In later versions of MySQL you can use the information_schema database to tell you when another table was updated:

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:

On database updates:

• Open your timestamp file in O_RDRW mode
• close it again

or alternatively

• use touch(), the PHP equivalent of the utimes() function, to change the file timestamp.

On page display:

• use stat() to read back the file modification time.

回答2:

I don\'t have information_schema database, using mysql version 4.1.16, so in this case you can query this:

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.

回答3:

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():

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:

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

回答5:

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

回答6:

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:

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:

Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

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).

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 :) ).

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

回答8:

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

回答9:

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.

回答10:

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.

回答11:

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.

回答12:

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:

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.