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.
我有一个网页，我使用CRUD操作在MySQL表中进行一些数据操作。桌面客户端应用程序有很多正在运行的实例，它们与同一个数据库进行通信，每次我通过网页对这个数据库进行更改时，所有这些客户端都需要被通知更改。我真的不知道最好的处理方法。桌面客户端应用程序是用c#编写的。我还知道，至少在使用ADO的MySQL时，无法异步通知桌面客户端数据库中发生的更改。净提供者。
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?
当客户端应用程序定期创建数据库连接并检查更改MySQL表时，有一个选项。但是，如果我们有1000个正在运行的实例，每个实例每2秒创建一个连接并读取相同的表，会怎么样呢?这不是非常消耗数据库服务器资源吗?
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.
我们还可以在insert/update/delete上写入触发器，该触发器填充另一个关于所做更改的表，然后我们将轮询这个新的“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.
还有一个选项是创建套接字服务器，该服务器轮询“changes_made”表，如果进行了更改，则将更改数据发送给在此上下文中为套接字侦听器的所有桌面客户端。因此，我们将删除客户端进行的轮询，并显著减少数据库上的数据库连接和负载。
Here I want to know what is the most correct and scalable approach to resolve such tasks.
在这里，我想知道解决此类任务的最正确和可伸缩的方法是什么。
1 个解决方案
#1
4
Well, it is not entirely impossible to notify clients of changes done to the database, although there is no built-in solution for this.
当然，通知客户对数据库所做的更改并非完全不可能，尽管没有内置的解决方案。
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).
在mysql中，您可以使用C/ c++创建编译后的用户定义函数(udf)。这些可以扩展mysql的任何功能——比如通过网络发送消息。您可以创建自己的UDF或使用mysqludf.org上可用的库通过网络发送消息。您可以从使用sqmyludf.org上的STOMP库发送STOMP消息开始。由于STOMP是与语言无关的，您也可以在。net环境中使用它，请参见SO主题(如果在Internet上搜索。net STOMP服务器，您将找到更多示例)。
The client application has to implement a STOMP server that is able to receive the STOMP messages from the server.
客户机应用程序必须实现能够从服务器接收STOMP消息的STOMP服务器。
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).
在数据库中，您需要创建一个订阅表，其中包含客户端订阅哪些通知以及如何连接到它的信息(IP地址/主机名和端口号是最小的)。
You also need to create triggers on all those tables and events that you want to send notifications about.
您还需要在您想要发送通知的所有表和事件上创建触发器。
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.
我还将创建一个存储过程或函数，该过程或函数接收修改后的数据，或者某个表已经从触发器中更改，检查订阅表，创建STOMP消息，然后调用编译后的UDF，该UDF将STOMP消息发送给所有订阅的客户端。触发器将调用此存储过程或函数，而不是在每个触发器中实现所有这些功能。
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.
免责声明:我不认为这是最适合您的特殊情况的、可伸缩的解决方案。但是，通过这种方式，您可以自己实现通知系统，从而避免了轮询数据库的需要。
#1
4
Well, it is not entirely impossible to notify clients of changes done to the database, although there is no built-in solution for this.
当然，通知客户对数据库所做的更改并非完全不可能，尽管没有内置的解决方案。
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).
在mysql中，您可以使用C/ c++创建编译后的用户定义函数(udf)。这些可以扩展mysql的任何功能——比如通过网络发送消息。您可以创建自己的UDF或使用mysqludf.org上可用的库通过网络发送消息。您可以从使用sqmyludf.org上的STOMP库发送STOMP消息开始。由于STOMP是与语言无关的，您也可以在。net环境中使用它，请参见SO主题(如果在Internet上搜索。net STOMP服务器，您将找到更多示例)。
The client application has to implement a STOMP server that is able to receive the STOMP messages from the server.
客户机应用程序必须实现能够从服务器接收STOMP消息的STOMP服务器。
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).
在数据库中，您需要创建一个订阅表，其中包含客户端订阅哪些通知以及如何连接到它的信息(IP地址/主机名和端口号是最小的)。
You also need to create triggers on all those tables and events that you want to send notifications about.
您还需要在您想要发送通知的所有表和事件上创建触发器。
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.
我还将创建一个存储过程或函数，该过程或函数接收修改后的数据，或者某个表已经从触发器中更改，检查订阅表，创建STOMP消息，然后调用编译后的UDF，该UDF将STOMP消息发送给所有订阅的客户端。触发器将调用此存储过程或函数，而不是在每个触发器中实现所有这些功能。
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.
免责声明:我不认为这是最适合您的特殊情况的、可伸缩的解决方案。但是，通过这种方式，您可以自己实现通知系统，从而避免了轮询数据库的需要。