How to retrieve only updated/new records since the last query in SQL?

I was asked to design a class for caching SQL query results. Calling the class' query method will query and cache the entire set of results at the first time; afterward, each subsequence query will retrieve only the updated portion, and will merge the result into the cache.

If the class is required to be generic, i.e. NO knowledge about the db and the tables, do you have any idea?

Is it possible, and how to retrieve only updated/new records since the last query?

Solution 1:

MySQL (nor any other SQL-based database, as far as I know) does not store the "last update time" or "insert time" of a row anywhere, at least not anywhere you can query (parsing out the binary log is probably not something you're looking to do).

To get "new records" you will need to either

• Query for all records with an autoincrement value higher than the last known max
• Add a datetime/timestamp to the table that would store the insert/updated time of the record, and query based on that

You could consider having your code create its own table that would store a record every time an UPDATE or INSERT happened on another table, and then add triggers to all those other tables that would populate your table.

But that might be a bit much.