mysql函数和过程（包括分割字符串、拼接时长、内容转换等）

使用navicat for mysql 制作报表时用到了函数和过程，总结如下：

一、函数和过程

1、navicat可以点击新建函数建立函数和过程（存储过程），函数可以达成功能，可以利用mysql已有函数、判断、循环等返回mysql支持的数据类型（int、varchar等）自定义函数。过程可以操作函数、操作sql，建立临时表等。

2、自带函数举例：

length(str)      获取str字符串的长度，返回一个整数 

replace(str,from_str,to_str)      返回字符串str，其字符串from_str的所有出现由字符串to_str代替

substring_index(str,delim,count)     返回从字符串str的第count个出现的分隔符delim之后的子串。

如果count是正数，从左数分隔符，返回此分隔符左侧所有。如果count是负数，从右数分隔符，返回此分隔符右侧所有。

reverse(str)    返回颠倒字符顺序的字符串str

reverse(str1,str2)    返回str1与str2连接之后的字符串

3、自定义函数举例：

（1）func_get_splitStringTotal：返回字符串分割单字符后的数组长度

参数：`f_string` varchar(10000),`f_delimiter` varchar(50)

返回类型：int(11)

内容：

BEGIN

#Routine body goes here...

return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));

END

举例：select func_get_splitStringTotal('1,5,6,7',',');#结果为4

关注：

mysql注释方式：#或者/*    */。

BEGIN和END可以省略。

（2）func_splitString：返回分割后索引所在的数组元素，从1开始

参数：`f_string` varchar(1000),`f_delimiter` varchar(5),`f_order` int

返回类型：varchar(255)

内容：

BEGIN

#Routine body goes here...

declare result varchar(255) default '';   

set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));   

return result;

END

举例：select func_splitString('1,5,6,7',',','2');#结果为5

（3）is_true_IntToString：0、1转”否”、”是”

参数：`is_true` tinyint

关注：mysql没有true、false的boolean类型，可以用1、0的tinyint类型代替

返回：varchar(20)

内容：

BEGIN

#Routine body goes here...

IF is_true = 0 THEN RETURN '否';

ELSE RETURN '是';

END IF;

END

关注：

IF之后一定要END IF。

IF is_true = 0等同于IF (is_true = 0)。

举例：is_true_IntToString(isQualified) as '是否合格'

（4）获得登录时长：

CONCAT(FLOOR((TIME_TO_SEC(exitTime) - TIME_TO_SEC(loginTime))/60),'分钟')

#TIME_TO_SEC负责转成秒，FLOOR负责向下取整，CONCAT负责连接，关注：mysql中没有str1 + str2

3、自定义过程举例：

（1）分割字符串得到各子字符串存在临时表中，可查询，用到了上面的两个自定义函数：

参数：IN `f_string` varchar(1000),IN `f_delimiter` varchar(5)

无返回

内容：

BEGIN

#Routine body goes here...

declare cnt int default 0;   

declare i int default 0;   

set cnt = func_get_splitStringTotal(f_string,f_delimiter);   

DROP TABLE IF EXISTS `tmp_split`;   

create temporary table `tmp_split` (`val_` varchar(128) not null) DEFAULT CHARSET=utf8;   

while i < cnt   

do   

set i = i + 1;   

insert into tmp_split(`val_`) values (func_splitString(f_string,f_delimiter,i));   

end while;

END

举例：call splitString('1,5,6,7',',');   

SELECT * from tmp_split;#结果为val:1 5 6 7，适合一个属性是多条记录ID或其他主键的拼接字符串的情况，分割后可对应可得到多条记录ID或其他主键