一、服务器环境:
CentOS 7.9 / MySQL 8.0
二、需要组件:
MailX / UDF / Mysql-Devel
三、安装步骤:
1、MailX / Mysql-Devel
yum install mysql-devel
yum install mailx
2、UDF
(1)安装文件下载
(2)在系统中执行命令
gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o lib_mysqludf_sys.so
注意:如果是 32 位操作系统,则上述命令中-m64
替换为-m32
(3)在MySQL中执行命令
SHOW VARIABLES LIKE 'plugin_dir';
(4)将文件lib_mysqludf_sys.so
复制到上述路径
(5)在MySQL中执行命令
CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so';
四、建立触发器
BEGIN
-- 设置变量
SET @Dept = '';
SET @MailID = '';
SET @MailBody = '';
SET @Cmd = '';
SET @GOMail = '';
SET @TempCmd = '';
-- 获取@Dept值
IF @Dept = ''
THEN
SET @Dept = NEW.project_id;
END IF;
-- 根据@Dept值赋值@MailID
IF @MailID = ''
THEN
SET @MailID = (
CASE
WHEN @Dept = 'YhxjL4' THEN '1@qq.com'
WHEN @Dept = 'faDdY2' THEN '2@qq.com'
WHEN @Dept = 'U2nfrt' THEN '3@qq.com'
WHEN @Dept = 'ER8UcB' THEN '4@qq.com'
WHEN @Dept = 'ZDzbZN' THEN '5@qq.com'
WHEN @Dept = 'AgPoIh' THEN '6@qq.com'
END
);
END IF;
-- 根据@Dept值赋值@MailBody
IF @MailID != ''
THEN
SET @MailBody = (
CASE
WHEN @Dept = 'YhxjL4' THEN 'https://www.x1.com'
WHEN @Dept = 'faDdY2' THEN 'https://www.x2.com''
WHEN @Dept = 'U2nfrt' THEN 'https://www.x3.com''
WHEN @Dept = 'ER8UcB' THEN 'https://www.x4.com''
WHEN @Dept = 'ZDzbZN' THEN 'https://www.x5.com''
WHEN @Dept = 'AgPoIh' THEN 'https://www.x6.com''
END
);
END IF;
-- 定义@Cmd命令内容
IF @MailID != ''
THEN
SET @Cmd = (CONCAT('echo ',@MailBody,' | mail -s "您收到了新的申请,清尽快处理!" ',@MailID));
END IF;
-- 通过系统调用执行发送邮件命令
IF @MailID != ''
THEN
SET @GoMail = sys_exec(@Cmd);
END IF;
END
五、注意事项
1、因为信息量很小,所以全部写在了触发器,没有使用存储过程
2、UDF有风险,谨慎使用