当表更新时从 MySQL 触发器发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28917411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Send email from MySQL trigger when a table updated
提问by user3784251
Consider a table as table2, i like to add a triggeron this table updateas
考虑一个表作为table2，我想补充一个trigger在此表update作为
select Num into num from table1 where ID=new.id;
BEGIN
DECLARE done int default false;
DECLARE cur1 CURSOR FOR select EmailId from users where Num=num;
DECLARE continue handler for not found set done = true;
OPEN cur1;
my_loop: loop
fetch cur1 into email_id;
if done then
leave my_loop;
end if;
//send mail to all email id.
end loop my_loop;
close cur1;
END;
Is there any simple method to write in the place commented? To send a email to all the email id retrieved from table users.
有什么简单的方法可以写在评论的地方吗？向从 table 中检索到的所有电子邮件 ID 发送电子邮件users。
I am using MySQL in XAMPP(phpmyadmin).
我在 XAMPP(phpmyadmin) 中使用 MySQL。
回答by barbarity
I'm against that solution. Putting the load of sending e-mails in your Database Server could end very bad.
我反对那个解决方案。将发送电子邮件的负载放在数据库服务器中可能会导致非常糟糕的结果。
In my opinion, what you should do is create a table that queues the emails you would want to send and have a process that checks if there are emails to be sent and send them if positive.
在我看来，您应该做的是创建一个表，将您想要发送的电子邮件排队，并有一个过程来检查是否有要发送的电子邮件，如果是肯定的，则发送它们。
回答by Krish
As everyone said, making your database to deal with E-Mails is a bad idea. Making your database to write an external file is another bad idea as IO operations are time consuming compared to database operations.
正如大家所说，让您的数据库来处理电子邮件是一个坏主意。使您的数据库写入外部文件是另一个坏主意，因为与数据库操作相比，IO 操作更耗时。
There are several ways to deal with your issue, one that I like: Having a table to queue up your notifications/emails and a client program to dispatch the emails.
有几种方法可以解决您的问题，我喜欢一种方法：有一张桌子来排队您的通知/电子邮件和一个客户端程序来发送电子邮件。
Before you continue, you should make sure you have an E-Mail account or service that you can use to send out emails. It could be SMTP or any other service.
在继续之前，您应该确保您有一个可以用来发送电子邮件的电子邮件帐户或服务。它可以是 SMTP 或任何其他服务。
If you are on .NET you can copy this, if not you get the idea how to rewrite in your platform
如果你在 .NET 上，你可以复制这个，如果不是，你知道如何在你的平台上重写
Create a table in your DB
在您的数据库中创建一个表
CREATE TABLE `tbl_system_mail_pickup` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mFrom` varchar(128) NOT NULL DEFAULT '[email protected]',
`mTo` varchar(128) NOT NULL,
`mCC` varchar(128) DEFAULT NULL,
`mBCC` varchar(128) DEFAULT NULL,
`mSubject` varchar(254) DEFAULT NULL,
`mBody` longtext,
`added_by` varchar(36) DEFAULT NULL,
`updated_by` varchar(36) DEFAULT NULL,
`added_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`email_sent` bit(1) DEFAULT b'0',
`send_tried` int(11) DEFAULT '0',
`send_result` text,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
this table will hold all your notifications and emails. Your Table trigger or events which is trying to send an email will add a record to above table. Your client will then pickup the emails and dispatch at an interval you specify.
此表将保存您的所有通知和电子邮件。您的表触发器或尝试发送电子邮件的事件将在上表中添加一条记录。然后，您的客户将接收电子邮件并按您指定的时间间隔发送。
Here is a sample console app on .NET just to give you an idea how the client might look like. You can modify it according to your needs. ( I use Entity-framework as its much easier and simpler)
这是 .NET 上的示例控制台应用程序，只是为了让您了解客户端的外观。您可以根据需要对其进行修改。（我使用实体框架因为它更容易和更简单）
- Create new Console Project in Visual Studio.
- In package manager console, install following packages (install-package EntityFramework, install-package MySQL.Data.Entity)
- Add a reference to System.Configuration
- Now open "App.Config" file and add your connection-strings to your MySQL database.
In the same App.Config file, add your SMTP email settings. check your port and ssl option
<system.net> <mailSettings> <smtp from="[email protected]"> <network host="smtp.office365.com(your host)" port="587" enableSsl="true" password="dingDong" userName="YourUsername@YourDomain" /> </smtp> </mailSettings>Create new Folder called Models in your project tree.
- Add new item -> ADO.NET Entity Data Model -> EF Designer from database
- Select your Connection-String -> tick the Save connection setting in App.Config - > Name your database.
- Next - > Select only the above table from your database and complete the wizard.
- 在 Visual Studio 中创建新的控制台项目。
- 在包管理器控制台中，安装以下包（安装包 EntityFramework，安装包 MySQL.Data.Entity）
- 添加对 System.Configuration 的引用
- 现在打开“App.Config”文件并将您的连接字符串添加到您的 MySQL 数据库。
在同一个 App.Config 文件中，添加您的 SMTP 电子邮件设置。 检查您的端口和 ssl 选项
<system.net> <mailSettings> <smtp from="[email protected]"> <network host="smtp.office365.com(your host)" port="587" enableSsl="true" password="dingDong" userName="YourUsername@YourDomain" /> </smtp> </mailSettings>在项目树中创建名为 Models 的新文件夹。
- 添加新项目 -> ADO.NET 实体数据模型 -> 来自数据库的 EF 设计器
- 选择您的连接字符串 -> 在 App.Config 中勾选保存连接设置 -> 命名您的数据库。
- 下一步 - > 从您的数据库中仅选择上表并完成向导。
Now you have a connection to your database and email configurations in place. Its time to read emails and dispatch them.
现在您已连接到您的数据库和电子邮件配置。是时候阅读和发送电子邮件了。
Here is a full program to understand the concept. You can modify this if you have win application, or web application an in cooperate this idea. This console application will check every minutes for new email records and dispatches them. You can extend above table and this script to be able to add attachments.
这是一个完整的程序来理解这个概念。如果你有 win 应用程序，或者 web 应用程序配合这个想法，你可以修改它。此控制台应用程序将每分钟检查一次新的电子邮件记录并发送它们。您可以扩展上面的表格和这个脚本来添加附件。
using System;
using System.Linq;
using System.Timers;
using System.Data;
using System.Net.Mail;
using ccms_email_sender_console.Models;
namespace ccms_email_sender_console
{
class Program
{
static Timer aTimer = new Timer();
static void Main(string[] args)
{
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);// add event to timer
aTimer.Interval = 60000; // timer interval
aTimer.Enabled = true;
Console.WriteLine("Press \'q\' to exit");
while (Console.Read() != 'q');
}
private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
var db_ccms_common = new DB_CCMS_COMMON(); // the database name you entered when creating ADO.NET Entity model
Console.WriteLine(DateTime.Now+"> Checking server for emails:");
aTimer.Stop(); /// stop the timer until we process current queue
var query = from T1 in db_ccms_common.tbl_system_mail_pickup where T1.email_sent == false select T1; // select everything that hasn't been sent or marked as sent.
try
{
foreach (var mRow in query)
{
MailMessage mail = new MailMessage();
mail.To.Add(mRow.mTo.ToString());
if (mRow.mCC != null && !mRow.mCC.ToString().Equals(""))
{
mail.CC.Add(mRow.mCC.ToString());
}
if (mRow.mBCC != null && !mRow.mBCC.ToString().Equals(""))
{
mail.CC.Add(mRow.mBCC.ToString());
}
mail.From = new MailAddress(mRow.mFrom.ToString());
mail.Subject = mRow.mSubject.ToString();
mail.Body = mRow.mBody.ToString();
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Send(mail);
mRow.email_sent = true;
Console.WriteLine(DateTime.Now + "> email sent to: " + mRow.mTo.ToString());
}
db_ccms_common.SaveChanges(); // mark all sent emails as sent. or you can also delete the record.
aTimer.Start(); // start the timer for next batch
}
catch (Exception ex)
{
Console.WriteLine(DateTime.Now + "> Error:" + ex.Message.ToString());
// you can restart the timer here if you want
}
}
}
}
to Test: Run above code and insert a record into your tbl_system_mail_pickup table. In about 1 minute you will receive an email to the sender you've specified.
测试：运行上面的代码并将记录插入到您的 tbl_system_mail_pickup 表中。大约 1 分钟后，您将收到一封发送给您指定的发件人的电子邮件。
Hope this helps someone.
希望这可以帮助某人。
回答by O. Jones
You asked, "is there a simple method to ... send a email" from a trigger?
您问，“有没有一种简单的方法可以……从触发器发送电子邮件”？
The answer is no, not in standard MySQL.
答案是否定的，不在标准 MySQL 中。
This is usually done by creating some kind of pending_message table and writing a client program to poll that table regularly and send the messages.
这通常是通过创建某种 pending_message 表并编写客户端程序来定期轮询该表并发送消息来完成的。
Or, you could consider using a user-defined MySQL function. But most production apps don't much like building network functions like email sending into their databases, because performance can be unpredictable.
或者，您可以考虑使用用户定义的 MySQL 函数。但是大多数生产应用程序不太喜欢构建网络功能，例如将电子邮件发送到他们的数据库中，因为性能是不可预测的。
