Mysql自定义函数（function）

语法

自定义函数也需要相应的要求，语法如下:

CREATE FUNCTION <函数名称>(参数列表)

RETURNS 返回值类型

　　函数体

删除:

DROP FUNCTION <函数名称>

调用自定义函数语法:

SELECT <函数名称>(parameter_value,...)

语法实例

1create function rand_string(n int) returns varchar(255)
2begin
3declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
4declare return_str varchar(255) default '';
5declare i int default 0;
6while i < n do
7set return_str = concat(return_str, substring(chars_str,floor(1+rand()*62),1));
8set i= i+1;
9end while;
10return return_str;
11end;

效果

这样我们自己定义的函数就完成了，先来看看_FUNCTION_属性及运行效果；

1mysql> show function status;
2+------+-------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3| Db   | Name        | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
4+------+-------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
5| test | rand_string | FUNCTION | root@localhost | 2017-05-26 14:13:10 | 2017-05-26 14:13:10 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
6+------+-------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
71 row in set
8mysql> show create function rand_string;
9+-------------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
10| Function    | sql_mode               | Create Function                                                                                                                                                                                                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
11+-------------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
12| rand_string | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `rand_string`(n int) RETURNS varchar(255) CHARSET latin1
13begin
14declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
15declare return_str varchar(255) default '';
16declare i int default 0;
17while i < n do
18set return_str = concat(return_str, substring(chars_str,floor(1+rand()*62),1));
19set i= i+1;
20end while;
21return return_str;
22end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
23+-------------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
241 row in set
25mysql> select rand_string(100);
26+------------------------------------------------------------------------------------------------------+
27| rand_string(100)                                                                                     |
28+------------------------------------------------------------------------------------------------------+
29| bfvEBTvGJqOBp7ajTm0KAwIPQy7Mmighp2PTN86XmIoCJG2YBYQbbciKG1Tfo3YvsDEaIVhwBhp2QY7xa3ADfd8SNctzjGjlCTuA |
30+------------------------------------------------------------------------------------------------------+
311 row in set

名词解释

接下来对上面所涉及到的关键词进行汇总整理。

大写

小写

词义

用法

USE

use

切换数据库

use test

IF EXIST

if exist

判断是否存在

if exist name

DELIMITER 

delimiter

定义结束符

delimiter //        默认为；

DEFINER

definer

权限

definer=root@localhost

CHARSET  

charset

编码

charset =utf8

DECLARE

declare

声明

declare return_str varchar(10240) 

DEFAULT

default

默认值

default 'abcdefgh';

ENGINE

engine

存储引擎

engine=innodb||engine=myisam

FUNCTION

function

函数

function rand_string(n INT)

RETURNS

returns

返回类型

returns varchar(10240)

RETURN

return

返回值

return 'zhangsan'

CONCAT

concat

拼串

concat(string value,string value)

SUBSTRING

substring

截取字符串

substring(被截取名,开始索引，结束索引)

FLOOR

floor

取整

floor（23.33）

RAND

rand

随机数

rand()

WHILE..DO

while..do

while循环

while i < n do

SHOW..STATUS

show..status

查看运行状态

show function status；