MySQL不仅内置了一些常用函数,同时也支持自定义函数,来满足开发者的需求
函数定义
MySQL的函数定义语法如下:
CREATE
- functionName:函数名,同MySQL内置函数一样,大小写不敏感
- varName: 形参名
- varType: 形参类型,其与varName配对使用。形参数量不限( $geq 0$)
- returnVarType: 返回值类型。函数必须有且只能有一个返回值
- characteristic:函数特性,下将详述
- routine_body:函数体。函数体中必须含有 return 语句,当函数体为复合结构时,需要使用begin ... end 语句
characteristic的可用参数值如下:
| 值 | 说明 | | :-: | :-: | | language sql | 指明函数体的语言类型, 目前仅支持sql | | [not] deterministic | deterministic 指明函数的结果是确定的,即相同的输入会得到相同的输出;not deterministic意为结果不确定。默认为 not deterministic | | { contains sql | no sql | reads sql data | modifies sql data } | 指明函数体使用sql语句的限制。contains sql意为函数体包含sql语句,但不包含读写数据的sql语句;no sql意为函数体不包含sql语句;reads sql data意为函数体包含读数据sql语句;modifies sql data意为函数体包含写数据的sql语句。默认为contains sql | | sql security { definer | invoker } | 指明谁有权限执行该函数。definer意为只有定义者才能执行;invoker意为拥有权限的调用者可以执行。默认为definer | | comment 'message' | 函数的注释信息,指明函数的功能 |
示例如下:
- 函数体为简单结构:
create
- 函数体为复合结构:
create
变量
局部变量
定义在sql语句块中的变量,常见于存储过程和函数的 begin ... end 中,语句块执行完后局部变量则结束生命周期
声明定义
函数中可以通过 declare 声明定义局部变量,其作用域为 begin ... end 的函数体中。默认初值为nul,可以通过default指定该语句中所有定义变量的初值
declare
Note: declare声明定义语句,必须放在begin...end函数体中最前面的位置
赋值
- 通过set给变量赋值
set
[Note]: sql下的 = 操作符是比较(判定是否相等)操作符,只有在set语句中可作为赋值操作符使用。故在其他语句中,赋值操作应该使用 := 操作符
- 通过select into 给变量赋值
通过select语句将所查询出的字段数据依次赋值到 into 后的变量中。值得一提的是,当select查询结果为空时(即,无记录),则不对变量进行赋值操作;当select查询的结果不止一条时,MySQL将报错,函数执行失败
select
示例
create
函数测试结果如下:
![748fb199cc66759eb6b7b7eed906f71a.png](https://i-blog.csdnimg.cn/blog_migrate/352535e7088cb9c4f8500022a9cac8dd.jpeg)
用户变量
定义在当前客户端的连接下的变量,其作用域在当前客户端连接下均有效,当当前客户端断开连接后则该变量结束生命周期。其对其他客户端连接不可见
赋值
用户变量无需先行声明创建,直接赋值使用即可。赋值时,当前客户端下若无该用户变量,则会自动创建并完成赋值;查看一个不存在的用户变量时(e.g., select @foo)返回null。需要注意的是,用户变量的变量名必须以 @ 开头
- 通过set 赋值
set
- 通过 select 赋值
通过select语句将所查询出的字段数据赋值到变量中,需要注意的是,只能使用 := 操作符赋值
select
示例
![ed48e9158f56268d95f402d67bf7ae29.png](https://i-blog.csdnimg.cn/blog_migrate/70f37941c2292aa4795eca4ee4486646.jpeg)
常用流程控制
if 语句
if
示例如下:
create
测试结果如下:
![244e50773a2fb047f42355398b538b58.png](https://i-blog.csdnimg.cn/blog_migrate/76445ff72dea081481a4c9476d4e9855.jpeg)
while 语句
[
示例如下:
create
测试结果如下:
![846d8420c12ee33460b07ec4f2d590b9.png](https://i-blog.csdnimg.cn/blog_migrate/adab998565e74069e45bb68e51defcc7.jpeg)
leave、iterate
levae 和 iterate 分别用于循环控制的跳出整个循环 和 跳过循环体的剩余部分并直接进行下一次循环,作用相当于C语言中的 break和 continue。区别在于C语言中的break、continue都是针对所在层循环的控制。而levae 和 iterate是针对label所标注的循环的控制,故其可以实现从最内层循环直接跳出
levae
示例1:
create
测试结果如下:
![3712a55b67fec7c59a5a00da8de90447.png](https://i-blog.csdnimg.cn/blog_migrate/324eff8fe6f34f4eb1bbc012c4dc1c99.jpeg)
示例2:
create
测试结果如下:
![50b78de585329e6361cdb128f0fbd1a7.png](https://i-blog.csdnimg.cn/blog_migrate/cbb9c9385d7c2d4b8734f5530199d1c2.jpeg)
函数相关的操作
查看函数状态
通过show status 命令查看函数的相关信息。可以在其后面使用 like 语句进行函数名匹配,其中functionName同样支持 % 进行模糊匹配
show
示例如下:
![5b014f44d04a456790405b94994f7674.png](https://i-blog.csdnimg.cn/blog_migrate/e9cb4ca3f27644a5142842709ff785a8.jpeg)
查看函数定义
通过show create 命令查看函数的定义内容
show
示例如下:
![85550fab605b359991cc864b11d2cc80.png](https://i-blog.csdnimg.cn/blog_migrate/519c338171521c13c93593f2e5435ae3.jpeg)
修改函数特性
通过alter function实现对函数特性characteristic的修改,注意,不是对函数定义内容的修改
alter
示例如下:
![5045cc761f0866b18d2ac6e25b62c129.png](https://i-blog.csdnimg.cn/blog_migrate/50517a75c71acfca76d40e40a6579d0c.jpeg)
删除函数
通过drop function 删除函数。当指定函数不存在时,会报错,可以添加 if exists 避免出现报错
drop
测试结果如下:
![65bd8009da7125ec18241760995b2040.png](https://i-blog.csdnimg.cn/blog_migrate/04d5a2e9c2ea0e33d5a5a2c1ed97c11f.jpeg)
Note
在命令行中其默认将 ; 符号作为结束符来执行语句。所以如果我们在命令行中创建函数,需要使用 delimiter 命令重定义结束符 如下图所示,先通过 delimiter $$ 将结束符更改为 $$ (可随意更改,一般常用$$)再创建函数。函数创建完毕后,通过我们重定义后的结束符结束(即 $$ )。最后不要忘记,通过 delimiter ; 将结束符重新修改为 ;
![48d6d79addef1c934eff6c79679ca0f5.png](https://i-blog.csdnimg.cn/blog_migrate/d59e84a191e046bd0843fb8169674449.jpeg)
测试结果如下:
![8ff0e18294c432b2ca96b68c9212964c.png](https://i-blog.csdnimg.cn/blog_migrate/c917eb0377a6e537baa43aa4482b6e72.jpeg)