MySQL 有没有办法“监听”数据库事件并实时更新页面？

Is there a way to 'listen' for a database event and update a page in real time?

提问by AngeloS

I'm looking for a way to create a simple HTML table that can be updated in real-time upon a database change event; specifically a new record added.

我正在寻找一种方法来创建一个简单的 HTML 表，该表可以在发生数据库更改事件时实时更新；特别是添加了一个新记录。

In other words, think of it like an executive dashboard. If a sale is made and a new line is added in a database (MySQL in my case) then the web page should "refresh" the table with the new line.

换句话说，把它想象成一个执行仪表板。如果进行了销售并在数据库中添加了新行（在我的情况下为 MySQL），则网页应使用新行“刷新”表格。

I have seen some information on the new using EVENT GATEWAYbut all of the examples use Coldfusion as the "pusher" and not the "consumer". I would like to have Coldfusion both update / push an event to the gateway and also consume the response.

我已经看到了一些关于新EVENT GATEWAY用法的信息，但所有示例都使用 Coldfusion 作为“推动者”而不是“消费者”。我想让 Coldfusion 既更新/推送事件到网关，也使用响应。

If this can be done using a combination of AJAX and CF please let me know!

如果这可以使用 AJAX 和 CF 的组合来完成，请告诉我！

I'm really just looking to understand where to get started with real-time updating.

我真的只是想了解从哪里开始实时更新。

Thank you in advance!!

先感谢您！！

EDIT / Explanation of selected answer:

编辑/所选答案的解释：

I ended up going with @bpeterson76's answer because at the moment it was easiest to implement on a small scale. I really like his Datatables suggestion, and that's what I am using to update in close to real time.

我最终选择了@bpeterson76 的答案，因为目前它最容易小规模实施。我真的很喜欢他的 Datatables 建议，这就是我用来接近实时更新的内容。

As my site gets larger though (hopefully), I'm not sure if this will be a scalable solution as every user will be hitting a "listener" page and then subsequently querying my DB. My query is relatively simple, but I'm still worried about performance in the future.

随着我的网站变大（希望如此），我不确定这是否是一个可扩展的解决方案，因为每个用户都会点击“侦听器”页面，然后查询我的数据库。我的查询比较简单，但我还是担心未来的性能。

In my opinion though, as HTML5 starts to become the web standard, the Web Sockets method suggested by @iKnowKungFoo is most likely the best approach. Comet with long polling is also a great idea, but it's a little cumbersome to implement / also seems to have some scaling issues.

不过在我看来，随着 HTML5 开始成为 Web 标准，@iKnowKungFoo 建议的 Web Sockets 方法很可能是最好的方法。Comet 长轮询也是一个好主意，但实现起来有点麻烦/似乎也有一些缩放问题。

So, let's hope web users start to adopt more modern browsers that support HTML5, because Web Sockets is a relatively easy and scalable way to get close to real time.

因此，让我们希望网络用户开始采用支持 HTML5 的更现代的浏览器，因为 Web Sockets 是一种接近实时的相对简单且可扩展的方式。

If you feel that I made the wrong decision please leave a comment.

如果您觉得我做出了错误的决定，请发表评论。

Finally, here is some source code for it all:

最后，这里有一些源代码：

Javascript:

Javascript：

note, this is a very simple implementation. It's only looking to see if the number of records in the current datatable has changed and if so update the table and throw an alert. The production code is much longer and more involved. This is just showing a simple way of getting a close to real-time update.

请注意，这是一个非常简单的实现。它只是查看当前数据表中的记录数是否已更改，如果已更改，则更新表并发出警报。生产代码更长，涉及更多。这只是展示了一种接近实时更新的简单方法。

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.js"></script>
<script type="text/javascript" charset="utf-8">

var originalNumberOfRecsInDatatable = 0;
var oTable;

var setChecker = setInterval(checkIfNewRecordHasBeenAdded,5000); //5 second intervals

function checkIfNewRecordHasBeenAdded() {

        //json object to post to CFM page
        var postData = {
        numberOfRecords:  originalNumberOfRecsInDatatable 
        };

        var ajaxResponse = $.ajax({
        type: "post",
        url: "./tabs/checkIfNewItemIsAvailable.cfm",
        contentType: "application/json",
        data: JSON.stringify( postData )
        })

        // When the response comes back, if update is available
        //then re-draw the datatable and throw an alert to the user
        ajaxResponse.then(
        function( apiResponse ){

         var obj = jQuery.parseJSON(apiResponse);

         if (obj.isUpdateAvail == "Yes")
         {              
            oTable = $('#MY_DATATABLE_ID').dataTable();
            oTable.fnDraw(false);

            originalNumberOfRecsInDatatable = obj.recordcount;

            alert('A new line has been added!');
         }

        }
        );

    }
</script>

Coldfusion:

冷聚变：

<cfset requestBody = toString( getHttpRequestData().content ) />

<!--- Double-check to make sure it's a JSON value. --->
<cfif isJSON( requestBody )>

<cfset deserializedResult = deserializeJSON( requestBody )>

<cfset numberOFRecords = #deserializedResult.originalNumberOfRecsInDatatable#>


<cfquery  name="qCount" datasource="#Application.DBdsn#" username="#Application.DBusername#" password="#Application.DBpw#">
    SELECT COUNT(ID) as total
    FROM myTable
</cfquery>

<cfif #qCount.total# neq #variables.originalNumberOfRecsInDatatable#>
    {"isUpdateAvail": "Yes", "recordcount": <cfoutput>#qCount.total#</cfoutput>}
<cfelse>
    {"isUpdateAvail": "No"}
</cfif>


</cfif>

采纳答案by bpeterson76

This isn't too difficult. The simple way would be to add via .append:

这不是太难。简单的方法是通过 .append 添加：

$( '#table > tbody:last').append('<tr id="id"><td>stuff</td></tr>');

Adding elements real-time isn't entirely possible. You'd have to run an Ajax query that updates in a loop to "catch" the change. So, not totally real-time, but very, very close to it. Your user really wouldn't notice the difference, though your server's load might.

实时添加元素是不可能的。您必须运行在循环中更新的 Ajax 查询以“捕获”更改。因此，并非完全实时，而是非常非常接近实时。您的用户真的不会注意到差异，尽管您的服务器负载可能会。

But if you're going to get more involved, I'd suggest looking at DataTables. It gives you quite a few new features, including sorting, paging, filtering, limiting, searching, and ajax loading. From there, you could either add an element via ajax and refresh the table view, or simply append on via its API. I've been using DataTables in my app for some time now and they've been consistently cited as the number 1 feature that makes the immense amount of data usable.

但是，如果您要更多地参与，我建议您查看DataTables。它为您提供了许多新功能，包括排序、分页、过滤、限制、搜索和 ajax 加载。从那里，您可以通过 ajax 添加元素并刷新表视图，或者简单地通过其 API 追加。我已经在我的应用程序中使用 DataTables 有一段时间了，它们一直被认为是使大量数据可用的第一大功能。

--Edit --

- 编辑 -

Because it isn't obvious, to update the DataTable you call set your Datatables call to a variable:

因为它并不明显，要更新您调用的 DataTable，请将您的 Datatables 调用设置为一个变量：

var oTable = $('#selector').dataTable();

Then run this to do the update:

然后运行它来进行更新：

  oTable.fnDraw(false);

UPDATE -- 5 years later, Feb 2016: This is much more possible today than it was in 2011. New Javascript frameworks such as Backbone.js can connect directly to the database and trigger changes on UI elements including tables on change, update, or delete of data....it's one of these framework's primary benefits. Additionally, UI's can be fed real-time updates via socket connections to a web service, which can also then be caught and acted upon. While the technique described here still works, there are far more "live" ways of doing things today.

更新 - 5 年后，2016 年 2 月：今天比 2011 年更有可能。新的 Javascript 框架如 Backbone.js 可以直接连接到数据库并触发 UI 元素的更改，包括更改、更新或更新的表删除数据......这是这些框架的主要好处之一。此外，用户界面可以通过套接字连接实时更新到 Web 服务，然后也可以被捕获并采取行动。虽然这里描述的技术仍然有效，但今天有更多“活”的做事方式。

回答by Jude Calimbas

You can use SSE (Server Sent Events) a feature in HTML5.

您可以使用 HTML5 中的 SSE（服务器发送事件）功能。

Server-Sent Events (SSE) is a standard describing how servers can initiate data transmission towards clients once an initial client connection has been established. They are commonly used to send message updates or continuous data streams to a browser client and designed to enhance native, cross-browser streaming through a JavaScript API called EventSource, through which a client requests a particular URL in order to receive an event stream.

服务器发送事件 (SSE) 是一个标准，描述了一旦建立了初始客户端连接，服务器如何向客户端发起数据传输。它们通常用于向浏览器客户端发送消息更新或连续数据流，旨在通过称为 EventSource 的 JavaScript API 增强本机跨浏览器流式传输，客户端通过该 API 请求特定 URL 以接收事件流。

heres a simple example

这是一个简单的例子

http://www.w3schools.com/html/html5_serversentevents.asp

http://www.w3schools.com/html/html5_serversentevents.asp

回答by Michael McConnell

In MS SQL, you can attach a trigger to a table insert/delete/update event that can fire a stored proc to invoke a web service. If the web service is CF-based, you can, in turn, invoke a messaging service using event gateways. Anything listening to the gateway can be notified to refresh its contents. That said, you'd have to see if MySQLsupports triggers and accessing web services via stored procedures. You'd also have to have some sort of component in your web app that's listening to the messaging gateway. It's easy to do in Adobe Flex applications, but I'm not sure if there are comparable components accessible in JavaScript.

在MS SQL 中，您可以将触发器附加到表插入/删除/更新事件，该事件可以触发存储过程以调用 Web 服务。如果 Web 服务是基于 CF 的，那么您可以反过来使用事件网关调用消息传递服务。任何侦听网关的内容都可以收到通知以刷新其内容。也就是说，您必须查看MySQL 是否支持触发器和通过存储过程访问 Web 服务。您还必须在您的 Web 应用程序中使用某种组件来侦听消息传递网关。在 Adob​​e Flex 应用程序中很容易做到，但我不确定是否有可在 JavaScript 中访问的类似组件。

While this answer does not come close to directly addressing your question, perhaps it will give you some ideas as to how to solve the problem using db triggers and CF messaging gateways.

虽然这个答案并不能直接解决您的问题，但它可能会给您一些关于如何使用 db 触发器和 CF 消息传递网关解决问题的想法。

M. McConnell

M.麦康奈尔

回答by Marko Jovanovi?

Check out AJAX long polling. Place to start Comet

查看 AJAX 长轮询。彗星的起点

回答by Adrian J. Moreno

With "current" technologies, I think long polling with Ajax is your only choice. However, if you can use HTML5, you should take a look at WebSockets which gives you the functionality you want.

使用“当前”技术，我认为使用 Ajax 进行长轮询是您唯一的选择。但是，如果您可以使用 HTML5，您应该看看 WebSockets，它为您提供了您想要的功能。

http://net.tutsplus.com/tutorials/javascript-ajax/start-using-html5-websockets-today/

http://net.tutsplus.com/tutorials/javascript-ajax/start-using-html5-websockets-today/

WebSockets is a technique for two-way communication over one (TCP) socket, a type of PUSH technology. At the moment, it's still being standardized by the W3C; however, the latest versions of Chrome and Safari have support for WebSockets.

WebSockets 是一种通过一个 (TCP) 套接字进行双向通信的技术，是一种 PUSH 技术。目前，它仍在被 W3C 标准化；但是，最新版本的 Chrome 和 Safari 支持 WebSockets。

http://html5demos.com/web-socket

http://html5demos.com/web-socket

回答by Feisty Mango

No, you can't have any db code execute server side code. But you could write a service to poll the db periodically to see if a new record has been added then notify the code you have that needs pseudo real-time updates.

不，您不能让任何数据库代码执行服务器端代码。但是您可以编写一个服务来定期轮询数据库以查看是否添加了新记录，然后通知您需要伪实时更新的代码。

回答by Gjorgji Tashkovski

The browser can receive real-time updates via BOSH connection to Jabber/XMPP server. All bits and pieces can be found in this book http://professionalxmpp.com/which I highly recommend. If you can anyhow send XMPP message upon record addition in your DB, then it is relatively easy to build the dashboard you want. You need strophe.js, Jabber/XMPP server (e.g. ejabberd), http server for proxying http-bind requests. All the details can be found in the book. A must read which I strongly believe will solve your problem.

浏览器可以通过与 Jabber/XMPP 服务器的 BOSH 连接接收实时更新。所有的点点滴滴都可以在这本书http://professionalxmpp.com/ 中找到，我强烈推荐它。如果您可以在数据库中添加记录时无论如何发送 XMPP 消息，那么构建您想要的仪表板相对容易。您需要 strophe.js、Jabber/XMPP 服务器（例如 ejabberd）、用于代理 http-bind 请求的 http 服务器。所有的细节都可以在书中找到。必须阅读，我坚信它会解决您的问题。

回答by leggetter

The way I would achieve the notification is after the database update has been successfully committed I would publish an event that would tell any listening systems or even web pages that the change has occurred. I've detailed one way of doing this using an e-commerce solution in a recent blog post. The blog post shows how to trigger the event in ASP.NET but the same thing can easily be done in any other language since ultimately the trigger is performed via a REST API call.

我实现通知的方式是在成功提交数据库更新后，我将发布一个事件，该事件会告诉任何侦听系统甚至网页发生了更改。我在最近的一篇博文中详细介绍了一种使用电子商务解决方案来做到这一点的方法。博客文章展示了如何在 ASP.NET 中触发事件，但同样的事情可以用任何其他语言轻松完成，因为最终触发是通过 REST API 调用执行的。

The solution in this blog post uses Pusherbut there's not reason why you couldn't install your own real-time server or use a Message Queue to communication between your app and the realtime server, which would then push the notification to the web page or client application.

这篇博文中的解决方案使用Pusher但没有理由不能安装自己的实时服务器或使用消息队列在您的应用程序和实时服务器之间进行通信，然后将通知推送到网页或客户端应用程序。