在C#中,我如何从MySQL获取事件,当有人插入、删除或修改记录时?

huangapple go评论59阅读模式
英文:

In C# How can I get a event from MySQL when somebody Insert, Delete or Modify a record?

问题

我正在开发一个WPF.Net程序,我需要知道当有人对数据库中的任何表进行更改时。

想法是在数据库发生更改时接收一个事件。我已经阅读了很多文章,但找不到解决我的问题的方法。

祝好。

英文:

I am developing a program in WPF.Net, and I need to know when somebody makes a change over any table of the database.

The idea is receive a event from the database when it was changed. I was reading a lot of articles but I can't find a method to resolve my problem.

Kind Regards

答案1

得分: 2

最佳解决方案是使用消息队列。在您的应用程序将更改提交到数据库后,该应用程序还会在消息队列上发布一条消息。然后,其他客户端只需等待该消息队列上的通知。

还有一些其他常见的解决方案,但它们都有缺点。

轮询。 如果客户端对最新更改感兴趣,它们会每隔N秒运行一次查询,搜索新数据。

缺点是即使在没有更改的时候,您也必须保持轮询。根据您需要多快地注意到更改,您可能需要经常轮询。这增加了数据库负载,仅仅是为了支持轮询查询。

如果有许多客户端都在轮询查询,成本也会更高。在我支持的一个系统中,数据库每秒处理 30,000 个查询,只是为了支持运行轮询的客户端而努力。

更改数据捕获。 使用二进制日志作为事实上的消息队列,因为它记录了所有更改。使用客户端工具,如Debezium,或编写自己的 binlog 尾部客户端(这是很多工作)。

缺点是 binlog 记录_所有_更改,而不仅仅是您想收到通知的那些更改。您必须以某种方式对其进行过滤。此外,您必须学会如何使用 Debezium 或类似工具。

触发器。 在表上编写触发器,调用一个 UDF 来在数据库外发布通知。这是一个坏主意,因为触发器在插入/更新/删除执行时执行,而不是在事务提交时执行。在更改提交之前,客户端可能会收到更改的通知,因此如果他们在收到通知后立即查询数据库,更改对他们来说尚不可见。

这也是一个缺点,因为它需要在 MySQL Server 中安装一个 UDF 扩展。MySQL 通常没有任何发布外部通知的方式。


我不是 C# 开发人员,因此无法提供特定的代码建议。但是上述通用方法在应用程序使用的语言不同的情况下基本相似。

英文:

The best solution is to use a message queue. After your app commits a change to the database, the app also publishes a message on the message queue. Other clients then just wait for notifications on that message queue.

There are a few other common solutions, but all of them have disadvantages.

Polling. If a client is interested in recent changes, they run a query searching for new data every N seconds.

The downside is you have to keep polling even during times when there are no changes. You might have to poll very frequently, depending on how promptly you need to notice the changes. This adds to database load just to support the polling queries.

Also it costs more if you have many clients all polling for queries. In one system I supported, the database was struggling to process 30,000 queries per second just for clients running polling.

Change Data Capture. Using the binary log as a de facto message queue, because it records all the changes. Use a client tool such as Debezium, or write your own binlog tail client (this is a lot of work).

The downside is the binlog records all changes, not just those you want to be notified about. You have to filter it somehow. Also you have to learn how to use Debezium or equivalent tool.

Triggers. Write a trigger on the table that invokes a UDF to post notification outside the database. This is a bad idea, because the trigger executes when your insert/update/delete executes, not when the transaction commits. Clients could be notified of changes before the changes are committed, so if they go query the database right after they get the notification, the change is not visible to them yet.

Also a disadvantage because it requires you install a UDF extension in MySQL Server. MySQL doesn't normally have any way of posting an external notification.


I'm not a C# developer so I can't suggest specific code. But the general methods above are similar regardless of which language the app is written in.

答案2

得分: -1

我不认为MySQL能够实现这一功能,像MondgoDB这样的数据库拥有这种功能。

你可能会喜欢使用此答案中描述的方法。

基本上,在具有日期/时间字段的行上,您可以提取自特定日期时间以来的数据。或者,您可以使用CQRS/事件策略,也许使用消息队列。

英文:

I don't think this is possible with MySQL, DBs like MondgoDB have this sort of feature.

You may like to use the method described in this answer.

Essentially have date/time fields on rows where you can pull data since a certain date time. Or you could use a CQRS/Event stratagem and maybe use a message queue.

huangapple
  • 本文由 发表于 2023年2月7日 02:07:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/75365033.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定