我的 MySQL 表更新后如何接收电子邮件?
您好，我想知道 MySQL 中是否有一种方法可以在 MySQL 表中添加一行时自动向自己发送电子邮件?
Hi I was wondering if there was a way in MySQL to automatically send an e-mail to myself whenever there is a row added to a MySQL table?
推荐答案
实现这一点的最佳方法是使用触发器和 cron.创建一个通知队列"表，并在所需表中插入一行时使用触发器填充该表.
The best way to achieve this would be using a trigger and a cron. Create a 'notification queue' table and populate that with a trigger when a row is inserted in the desired table.
例如.
CREATE TABLE `notification_queue` (
`notification_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sent` tinyint(1) unsigned NOT NULL,
PRIMARY KEY (`notification_id`)
);
然后定义一个简单的触发器:
Then define a simple trigger:
DELIMITER $$
CREATE TRIGGER t_notification_insert
AFTER INSERT ON [table_being_inserted]
FOR EACH ROW
BEGIN
INSERT INTO `notification_queue` (`sent`) VALUES (0);
END$$
DELIMITER ;
从那时起，您需要做的就是在服务器上运行一个 crontab(比如每分钟)，它从 notification 表中选择 sent = 0，发送通知并设置 sent = 1
From that point, all you need to do is make a crontab run on the server (say every minute) which selects from the notification table where sent = 0, send the notification and set sent = 1
据我所知，这是在不读取 bin 日志的情况下从数据库中获取该信息的最佳方式.
As far as I know, that's the best way to get that information out of the DB without reading the bin logs.
如果您需要使用 cron 运行的脚本示例:
If you need an example of the script to run with cron:
#!/bin/bash
DB_USER=''
DB_PASS=''
DB_NAME=''
ID=`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "SELECT notification_id FROM notification_queue WHERE sent=0 LIMIT 1;"`
if [[ ! -z $ID ]]
then
# SEND MAIL HERE
RESULT=`mysql -u$DB_USER -p$DB_PASS $DB_NAME -Bse "UPDATE notification_queue SET sent=1 WHERE notification_id = $ID;"`
echo "Sent"
fi
相关文章