函数和储存过程
数据库中,函数分为两类,普通函数,无需外部插件支持;用户自定义函数(user defined function,简称UDF),需要外部插件支持,例如dll文件(windows系统)或者so文件(linux系统)。
函数的调用
普通函数,使用储存过程调用;用户自定义函数,可以直接调用(前提是已有支持插件)。
普通函数实例代码
delimiter $$
create function loop_num() return int(5)
begin
declare i int default 0;
set i = floor(10+rand()*10);
return i;
end $$
上面的函数返回10-20之间的一个整数,首行声明函数分隔符$$,最后以其结束(最后一行)。declare用于变量声明,set为变量赋值。
储存过程实例代码
存储过程与函数的代码类似,如下:
delimiter $$
create procedure insert_id(in start int(5), in max_num int(5))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into test_table(age) values ((start + loop_num()));
until i = max_num
end repeat;
commit;
end $$
该储存过程,会调用前面定义的函数loop_num向表test_table中的age字段插入数据,使用循环(repeat)达到插入指定数量的数据,并且会暂时关闭自动提交(set autocommit = 0)。
储存过程的调用
delimiter ;
call insert_id(10, 20)
调用之后,会向表中插入20条数据,数据值在20-30之间。
自定义函数插件编写
本文以windows系统为例,编写和编译dll插件
必要软件工具:
cmake 和 visual studio(本次所用版本2019)
本地已安装mysql server,安装目录:
“C:\Program Files\MySQL\MySQL Server 8.0”
1、源码下载
通过mysql官网,下载MySQL源码文件到目录mysql(c:/mysql)
2、创建项目文件
新建文件夹test,进入test;
在源码文件夹c:/mysql/sql中找到udf_example.def和udf_example.cc,复制到文件夹test中;
将mysql_version.h(从已安装的mysql目录"C:\Program Files\MySQL\MySQL Server 8.0\include"中查找)放到文件夹test中;
在文件udf_example.cc中添加自定义函数test, test_init, test_deinit;
在文件udf_example.def中注册自定义函数test, test_init
新建文件CMakeLists.txt,写入以下内容:
PROJECT(udf_example)
INCLUDE_DIRECTORIES("c:/mysql/include")
ADD_DEFINITIONS("-DHAVE_DLOPEN")
ADD_LIBRARY(udf_example MODULE udf_example.cc udf_example.def)
TARGET_LINK_LIBRARIES(udf_example wsock32)
3、cmake命令
通过cmd进入test目录中,执行命令,其中generator的部分通过命令cmake --help查看可得到,替换下面命令
cmake -G “generator”
即:
cmake -G "Visual Studio 16 2019"
4、visual studio 编译
使用visual studio打开udf_example.sln,修改工程属性,设置对应版本(Release,32bit或64bit),编译得到dll文件。
5、dll文件使用
将udf_example.dll文件放入mysql插件目录"C:\Program Files\MySQL\MySQL Server 8.0\lib\plugin";
开启mysql服务;
通过命令行进入mysql的bin目录"C:\Program Files\MySQL\MySQL Server 8.0\bin",使用客户端登录mysql;
mysql -u root -p
创建函数
函数名称 参考udf_example.cc和udf_example.def
例如:
CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.dll';
select lookup('www.baidu.com');
select lookup('localhost');
select sys_execs('whoami');
删除函数
drop function lookup;
delete from mysql.func where name=‘lookup’;