对网页进行实时更新时跟踪更改的方法 [英] Methods for tracking changes when making realtime updates to a webpage

问题描述

我希望在网页上实时更新订单(和状态)列表.(MySQL)数据库中的订单通过其他进程(PHP)异步更新.

I'm looking to update a list of orders (and statuses) real-time on a webpage. The orders in the (MySQL) database are updated asynchronously through other processes (PHP).

我熟悉将数据推送到页面的机制(轮询、事件源).这不是关于那个.

I'm familiar with the mechanics of pushing data to pages (polling, event-source). This is not about that.

我正在努力解决的是在没有

What I'm struggling with is figuring out exactly what data to push for each user without

1. 不必要地更新不需要的列表实体
2. 不错过更新.

我的表确实有一个 DateTime 列 last_update_date，当订单有任何更改时，我会更新该列.我知道 MySQL 没有任何可以触发其他代码的事件触发器.

My table does have a DateTime column last_update_date that I update when there are any changes to the order. I know MySQL doesn't really have any event triggers that can trigger other code.

目前的想法:

1. 在我的 JS 中，我可以跟踪最后一个请求的时间，并在每个后续请求中，从那时起请求数据.这不起作用，因为 JS 时间很可能与服务器 MySQL 时间不匹配.
2. 同样可以在用户会话中存储服务器时间.我觉得这可能在大部分时间都有效，但根据数据库更新和请求的时间，可能会遗漏更改，因为数据库仅存储精度为 1 秒的 DateTime.

我确定有一种更原子的方法可以做到这一点，不过我只是在画一个空白.什么是合适的设计模式?

I'm sure there's a more atomic way to do this, I am just drawing a blank though. What are suitable design patterns for this?

推荐答案

O.Jones 提供的解决方案可以使跟踪更新原子化，但如果以下场景在一秒内全部发生，则会失败:

The solution O.Jones provided would work for making tracking updates atomic, though where it fails is if the following scenario occurs all within one second:

1. 订单更新被写入表(更新 1)
2. 发生轮询操作
3. 订单更新被写入表(更新 2)

在这种情况下，下一个轮询操作要么会错过更新 2，要么会复制更新 1，具体取决于您是使用 > 还是 >=询问.这不是代码的错，而是 MySql 日期时间类型只有 1 秒分辨率的限制.MySql v8 可以缓解这种情况，因为它具有 小数秒支持 虽然这仍然不能保证原子性.

In this scenario, the next poll action will either miss update 2, or will duplicate update 1, depending on if you use > or >= in your query. This is not the fault of the code, it's a limitation of the MySql datetime type having only 1 second resolution. This could be somewhat mitigated with MySql v8 as it has Fractional Seconds Support though this still would not guarantee atomicity.

我最终使用的解决方案是创建一个 order_changelog 表

The solution I ended up using was creating a order_changelog table

CREATE TABLE 'NewTable' (
'id'  int NULL AUTO_INCREMENT ,
'order_id'  int NULL ,
'update_date'  datetime NULL ,
PRIMARY KEY ('id')
);

每当对订单进行更改时，都会更新此表，基本上是对每次更新进行编号.

This table is updated any time a change to an order is made essentially numerating every update.

对于客户端，服务器存储来自 order_changelog 在会话中发送的最后一个 ID.每次客户端轮询时，我都会从 order_changelog 中获取 ID 大于会话中存储的 ID 的所有行，并将订单加入其中.

For the client side, the server stores the last ID from order_changelog that was sent in the session. Every time the client polls, I get all rows from order_changelog that have an ID greater than the ID stored in the session and join the orders to it.

$last_id = $_SESSION['last_update_id'];

$sql = "SELECT o.*, c.id as update_id
                FROM order_changelog c
                LEFT JOIN orders o ON c.order_id = o.id
                WHERE c.id > $last_id
                GROUP BY o.id
                ORDER BY order_date";

我现在可以保证拥有自上次投票以来的所有订单，没有重复，而且我不必跟踪单个客户.

I now am guaranteed to have all the orders since last poll, with no duplicates, and I don't have to track individual clients.

这篇关于对网页进行实时更新时跟踪更改的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！