Mysql自定义函数（function）

语法

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

　　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；