MySQL在SQL语句中调用系统命令
一、前言
- 环境
- LInux 发行版:
CentOS Linux release 7.6.1810 (Core)
- MySQL:
5.6.43
- UDF
UDF(user-defined function) 是 MySQL 的一个拓展接口
- 参考:
- UDF Repository for MySQL:https://github.com/mysqludf
- 扩展 MySQL:https://dev.mysql.com/doc/extending-mysql/8.0/en/introduction.html
- MySQL 社区版:https://www.mysql.com/cn/products/community/
- 依赖包查找:https://pkgs.org/
- mysql-udf-http:http://blog.zyan.cc/mysql-udf-http/
- MySQL UDF :https://www.codeguru.com/cpp/data/mfc_database/misc/article.php/c12615/MySQL-UDFs.htm
二、正文
1.工具安装
git安装
- 查看版本,可用于检查当前系统是否安装 git
# git --version
- 安装 git
# yum install git
- 卸载 git
# yum remove git
gcc安装
# yum install gcc gcc-c++
mysql依赖包
已安装的可以跳过,或者 后面执行命令时,报错了再安装
- 查看当前 mysql 版本
mysql> select version() from dual;
+-----------+
| version() |
+-----------+
| 5.6.43 |
+-----------+
1 row in set (0.00 sec)
-
安装对应版本的依赖包
-
mysql-community-devel(依赖:mysql-community-common、mysql-community-libs)
# wget ./ [依赖包下载地址]
# rpm -ivh [需要安装的依赖包]
2.lib_mysqludf_sys安装
- 查看 MySQL 的插件路径
/usr/lib64/mysql/plugin/
mysql> show variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
- 下载、编译和安装
# mkdir /opt/temp
# cd /opt/temp/
#-- git 克隆
# git clone https://github.com/mysqludf/lib_mysqludf_sys.git
# cd lib_mysqludf_sys
#-- 编译
# gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o lib_mysqludf_sys.so
#-- 拷贝文件
# cp lib_mysqludf_sys.so /usr/lib64/mysql/plugin/
- 连接 MySQL ,执行
lib_mysqludf_sys.sql
mysql> source /opt/temp/lib_mysqludf_sys/lib_mysqludf_sys.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3.测试
- 创建触发器
CREATE TRIGGER test_trigger AFTER insert
ON test_table FOR EACH ROW
BEGIN
SELECT @id:=id,@name:=name FROM test_table INTO @temp;
SET @command := 'mkdir /opt/'+@name+'_'+@id;
SELECT sys_exec(@command) INTO @msg;
END
- 最后,表中新增数据,测试触发器效果
三、其它
1.触发器创建报错
-
报错:
Not allowed to return a result set from a trigger
-
原因:MySQL 5 之后的版本,不支持触发器返回结果集
-
解决:在 SELECT 语句后加 INTO @msg,将结果先保存到 @msg 变量中,具体请参考的测试例子