语法

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

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

RETURNS 返回值类型

　　函数体

删除:

DROP FUNCTION <函数名称>

调用自定义函数语法:

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

语法实例

create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str,floor(1+rand()*62),1));
set i= i+1;
end while;
return return_str;
end;

效果

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

mysql> show function status;
+------+-------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name        | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| 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  |
+------+-------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set
mysql> show create function rand_string;
+-------------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function    | sql_mode               | Create Function                                                                                                                                                                                                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+-------------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| rand_string | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `rand_string`(n int) RETURNS varchar(255) CHARSET latin1
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str,floor(1+rand()*62),1));
set i= i+1;
end while;
return return_str;
end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-------------+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set
mysql> select rand_string(100);
+------------------------------------------------------------------------------------------------------+
| rand_string(100)                                                                                     |
+------------------------------------------------------------------------------------------------------+
| bfvEBTvGJqOBp7ajTm0KAwIPQy7Mmighp2PTN86XmIoCJG2YBYQbbciKG1Tfo3YvsDEaIVhwBhp2QY7xa3ADfd8SNctzjGjlCTuA |
+------------------------------------------------------------------------------------------------------+
1 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；