本文共 2217 字,大约阅读时间需要 7 分钟。
- LInux 发行版:
CentOS Linux release 7.6.1810 (Core)
- MySQL:
5.6.43
UDF(user-defined function) 是 MySQL 的一个拓展接口
- UDF Repository for MySQL:
- 扩展 MySQL:
- MySQL 社区版:
- 依赖包查找:
- mysql-udf-http:
- MySQL UDF :
# git --version
# yum install git
# yum remove git
# yum install gcc gcc-c++
已安装的可以跳过,或者 后面执行命令时,报错了再安装
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 [需要安装的依赖包]
/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/
lib_mysqludf_sys.sql
mysql> source /opt/temp/lib_mysqludf_sys/lib_mysqludf_sys.sqlQuery 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)
CREATE TRIGGER test_trigger AFTER insertON test_table FOR EACH ROWBEGIN SELECT @id:=id,@name:=name FROM test_table INTO @temp; SET @command := 'mkdir /opt/'+@name+'_'+@id; SELECT sys_exec(@command) INTO @msg;END
报错:Not allowed to return a result set from a trigger
原因:MySQL 5 之后的版本,不支持触发器返回结果集
解决:在 SELECT 语句后加 INTO @msg,将结果先保存到 @msg 变量中,具体请参考的测试例子
转载地址:http://nznws.baihongyu.com/