聆听MySQL中的数据库更改 [英] Listen to database changes in MySQL

问题描述

我有一个网页，并且使用CRUD操作在MySQL表中进行了一些数据操作.桌面客户端应用程序有许多正在运行的实例，它们与同一个数据库进行通信，每次我通过网页对该数据库进行更改时，都需要将这些更改通知所有这些客户端.我真的不知道如何处理此问题的最佳方法.桌面客户端应用程序是用C#编写的.我也知道，没有办法至少使用ADO.NET提供程序异步通知桌面客户端有关数据库中发生的更改(至少在MySQL中如此).

I have a webpage and I make some data manipulations in MySQL table using CRUD operations. There are a lot of running instances of desktop client application which communicates with the same database and every time I make changes in this database through the webpage all these clients need to be notified about changes. And I do not really know the best way how to handle this. The desktop client application is written in C#. Also I know that there is no way to asynchronously notify desktop clients about changes that happened in database at least in MySQL using ADO.NET provider.

当客户端应用程序定期创建数据库连接并检查MySQL表的更改时，有一个选项.但是，如果我们有1000个正在运行的实例，并且每个实例都创建连接并每2秒读取同一张表，该怎么办?难道不是很消耗数据库服务器资源吗?

There is an option when the client application creates database connection periodically and checks for changes MySQL table. But what if we have 1000 of running instances and each one creates connection and reads the same table every 2 seconds? Wouldn't it be very database server resource consuming an so on?

我们还可以在insert/update/delete上编写触发器，以填充另一个有关所做更改的表，然后，我们将轮询该新的"changes_made"表.

Also we can write triggers on insert/update/delete which populate another table about changes made and after that we are going to poll this new "changes_made" table.

还有一个创建套接字服务器的选项，该服务器轮询"changes_made"表，如果进行了更改，则将更改数据发送到所有在此上下文中为套接字侦听器的桌面客户端.因此，我们将摆脱客户端的轮询，并显着减少数据库连接的总量和数据库的负载.

Also there is an option to create socket server which polls the "changes_made" table and if changes are made then send changes data to all desktop clients which are socket listeners in this context. Thus we will get rid of polling made by clients and significantly reduce an overall amount of database connection and load on the database.

在这里，我想知道什么是解决此类任务的最正确和可扩展的方法.

Here I want to know what is the most correct and scalable approach to resolve such tasks.

推荐答案

好吧，尽管对此没有内置解决方案，但并非完全不可能将数据库所做的更改通知客户端.

Well, it is not entirely impossible to notify clients of changes done to the database, although there is no built-in solution for this.

在mysql中，您可以在C/C ++中创建编译的用户定义函数(UDF).这些几乎可以按照您想要的任何方式扩展mysql的功能-例如通过网络发送消息.您可以创建自己的UDF或使用 mysqludf.org 上可用的库通过网络发送消息.您可以首先使用mysqludf.org上的STOMP库发送 STOMP 消息.由于STOMP与语言无关，因此您也可以在.NET环境中使用它，请参见此SO主题(如果在Internet上搜索.net STOMP服务器，则会发现更多示例).

In mysql you can create compiled user defined functions (UDFs) in C/C++. These can extend the functionality of mysql pretty much any way you want it - such as sending messages over the network. You can create your own UDF or use libraries available on mysqludf.org to send messages over the network. You could start with using the STOMP library on mysqludf.org to send STOMP messages. Since STOMP is language-agnostic, you can use it from .NET environment as well, see this SO topic (if search the Internet for .net STOMP server, then you will find more examples).

客户端应用程序必须实现STOMP服务器，该服务器能够从服务器接收STOMP消息.

The client application has to implement a STOMP server that is able to receive the STOMP messages from the server.

在数据库中，您需要创建一个预订表，该表包含客户端为哪些通知以及如何连接通知(至少IP地址/主机名和端口号)的客户端所预订的信息.

In the database you need to create a subscription table that holds the information which client subscribes for what notifications and how to connect to it (IP address / host name and port number as a minimum).

您还需要在所有要发送通知的表和事件上创建触发器.

You also need to create triggers on all those tables and events that you want to send notifications about.

我还将创建一个存储过程或函数，以接收修改后的数据或某些表已从触发器更改的事实，检查订阅表，创建STOMP消息，然后调用发送STOMP消息的已编译UDF所有订阅的客户.触发器将调用此存储过程或函数，而不是在每个触发器中实现所有这些功能.

I would also create a stored procedure or function that receives the modified data or the fact that certain table has been changed from the triggers, checks the subscription table, creates the STOMP message and then calls the compiled UDF that send the STOMP message to all the subscribed clients. The triggers would call this stored procedure or function instead of implementing all these functionality in each of the triggers.

需要修改客户端以处理通知并采取进一步措施或提示用户.

The clients need to be modified to process the notifications and take further action or prompt the user.

免责声明:对于您的特殊情况，我不认为这是最合适且可扩展的解决方案.但是，通过这种方式，您可以自己实现一个通知系统，而无需轮询数据库.

Disclaimer: I do not claim that this is the most adequate and scalable solution for your particular case. However, this way you can implement a notification system on your own avoiding the need to poll your database.

这篇关于聆听MySQL中的数据库更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！