語法
自定義函數也需要相應的要求,語法如下:
CREATE FUNCTION(參數列表)
RETURNS返回值類型
函數體
刪除:
DROPFUNCTION
調用自定義函數語法:
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
SHOW..STATUS
show..status
查看運行狀態
show function status;