如何仅检索自上次 SQL 查询以来更新/新的记录？
我被要求设计一个类来缓存 SQL 查询结果。调用类的query方法会第一时间查询并缓存整组结果；之后，每个子序列查询将仅检索更新的部分，并将结果合并到缓存中。
如果该类要求是通用的，即没有关于数据库和表的知识，您有什么想法吗？
是否可能，以及如何仅检索自上次查询以来更新/新的记录？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
MySQL（据我所知，也没有任何其他基于 SQL 的数据库）不会在任何地方存储行的“上次更新时间”或“插入时间”，至少不会在您可以查询的任何地方存储（解析二进制日志可能是不是您想要做的事情）。
要获取“新记录”，您需要
您可以考虑让代码创建自己的表，每次在另一个表上发生 UPDATE 或 INSERT 时都会存储一条记录，然后将触发器添加到将填充您的表的所有其他表中。
但这可能有点太多了。
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
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.
要么您的缓存类必须收到所有更新的通知，要么您需要某种过期时间。
唯一的其他选择是查询数据库进行检查，但是这样您就失去了缓存的意义:)
上述对于“通用”缓存来说是正确的。如果您为特定表实现专门的缓存，您可能能够利用 DML 模式。
Either your cache class has to be notified of all updates, or you need some kind of expiration.
The only other option is to query the database to check, but then you kind of lose the point of the cache :)
The above is true for a "generic" cache. If you implement a specialized cache for a particular table you might be able to exploit DML patterns.