要实现MySQL新增数据的实时监控和短信通知,有以下几种主要方案:
创建触发器:
DELIMITER //
CREATE TRIGGER after_insert_trigger
AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
-- 将新增数据写入通知表
INSERT INTO notification_queue (table_name, record_id, created_at)
VALUES ('your_table', NEW.id, NOW());
END //
DELIMITER ;
创建轮询程序(Python示例):
import pymysql
import requests # 用于调用短信API
from time import sleep
def check_notifications():
db = pymysql.connect(host='localhost', user='user', password='pass', db='your_db')
while True:
with db.cursor() as cursor:
cursor.execute("SELECT * FROM notification_queue WHERE processed = 0")
notifications = cursor.fetchall()
for n in notifications:
# 获取完整数据
cursor.execute(f"SELECT * FROM {n['table_name']} WHERE id = {n['record_id']}")
data = cursor.fetchone()
# 发送短信
send_sms(data)
# 标记为已处理
cursor.execute("UPDATE notification_queue SET processed = 1 WHERE id = %s", (n['id'],))
db.commit()
sleep(5) # 每5秒检查一次
def send_sms(data):
# 调用短信服务API
sms_api_url = "https://your-sms-api.com/send"
payload = {
'phone': '目标手机号',
'message': f"新增数据: {str(data)}"
}
requests.post(sms_api_url, data=payload)
启用MySQL Binlog:
[mysqld]
log-bin=mysql-bin
binlog-format=ROW
使用Python监听Binlog(需安装python-mysql-replication):
from pymysqlreplication import BinLogStreamReader
import requests
mysql_settings = {
'host': 'localhost',
'port': 3306,
'user': 'user',
'passwd': 'password'
}
stream = BinLogStreamReader(
connection_settings=mysql_settings,
server_id=100,
blocking=True,
only_events=[WriteRowsEvent] # 只监听插入事件
)
for binlogevent in stream:
for row in binlogevent.rows:
if binlogevent.table == 'your_table':
# 发送短信
send_sms(row["values"])
def send_sms(data):
# 同上
架构: MySQL → Debezium → Kafka → 消费者程序 → 短信服务
优势:
如果使用阿里云RDS,可以直接使用DTS数据订阅功能: 1. 配置DTS数据订阅任务 2. 编写SDK消费程序 3. 在程序中集成短信服务
国内短信服务:
国际短信服务:
您可以根据实际场景复杂度、数据量和实时性要求选择合适的方案。简单场景推荐方案一或二,企业级应用推荐方案三或四。