linux平台
步骤如下:
下载源码:https://github.com/mysqludf/lib_mysqludf_sys
一、解压附件的压缩包之后
或者配置makefile
LIBDIR=/usr/lib64/mysql/plugin
install:
gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I /space/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/include/ -I. -shared lib_mysqludf_sys.c
然后make即可
手动编译:
gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I /space/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/include/ -I. -shared lib_mysqludf_sys.c -o lib_mysqludf_sys.so
最后把lib_mysqludf_sys.so文件放到 mysql的plugin目录
cp lib_mysqludf_sys.so /space/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/lib/plugin
二、执行chcon -t texrel_shlib_t /space/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/lib/plugin/lib_mysqludf_sys.so
这一步我觉得执行了没用
三、创建函数
DROP FUNCTION IF EXISTS lib_mysqludf_sys_info;
DROP FUNCTION IF EXISTS sys_get;
DROP FUNCTION IF EXISTS sys_set;
DROP FUNCTION IF EXISTS sys_exec;
DROP FUNCTION IF EXISTS sys_eval;
CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys.so';
四 测试
mysql> select sys_eval('/root/test1/1.sh');
+------------------------------+
| sys_eval('/root/test1/1.sh') |
+------------------------------+
| |
+------------------------------+
1 row in set (0.50 sec)
自定义测试:
创建test.c
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <m_ctype.h>
#include <m_string.h>
#include <stdlib.h>
#include <ctype.h>
#if defined(_WIN32) || defined(_WIN64) || defined(__WIN32__) || defined(WIN32)
#define DLLEXP __declspec(dllexport)
#else
#define DLLEXP
#endif
/* 自定义的函数 */
DLLEXP long long udf_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
/* 对应自定义函数的初始化和结束的函数,在调用udf_add函数时由系统调用 */
DLLEXP my_bool udf_add_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
DLLEXP void udf_add_deinit(UDF_INIT *initid);
long long udf_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
int a = *((long long *)args->args[0]);
int b = *((long long *)args->args[1]);
return a + b;
}
my_bool udf_add_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
return 0;
}
void udf_add_deinit(UDF_INIT *initid)
{
//
}
创建共享库:
[root@centos7 lib_mysqludf_sys-master]# gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I /space/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/include/ -I. -shared test.c -o test_udf.so
[root@centos7 lib_mysqludf_sys-master]# ls
install.sh lib_mysqludf_sys.c lib_mysqludf_sys.html lib_mysqludf_sys.so lib_mysqludf_sys.sql Makefile test.c test_udf.so
[root@centos7 lib_mysqludf_sys-master]# cp test.so /space/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/lib/plugin
[root@centos7 lib_mysqludf_sys-master]# chown mysql:mysql /space/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/lib/plugin/test_udf.so
执行测试:
mysql> create function udf_add returns integer soname 'test_udf.so';
Query OK, 0 rows affected (0.00 sec)
mysql> select udf_add(10,99);
+----------------+
| udf_add(10,99) |
+----------------+
| 109 |
+----------------+
1 row in set (0.01 sec)
window平台;
创建工程
修改后缀为c
配置头文件路径和一些参数
然后修改源文件Win32Project1.c中函数popen等为_popen,这是windows平台下的函数
最后编译生成dll文件即可,放到mysql的指定目录
创建函数:
CREATE FUNCTION lib_mysqludf_sys_info RETURNS STRING SONAME 'Win32Project1.dll';
CREATE FUNCTION sys_get RETURNS STRING SONAME 'Win32Project1.dll';
CREATE FUNCTION sys_set RETURNS INT SONAME 'Win32Project1.dll';
CREATE FUNCTION sys_exec RETURNS INT SONAME 'Win32Project1.dll';
CREATE FUNCTION sys_eval RETURNS STRING SONAME 'Win32Project1.dll';
测试:
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select sys_eval('echo "hello"');
+--------------------------+
| sys_eval('echo "hello"') |
+--------------------------+
| "hello"
|
+--------------------------+
1 row in set (0.08 sec)
更多内容: