2017.01.01

黑龙江广电mysql数据库函数创建

DROP FUNCTION IF EXISTS `func_split`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` FUNCTION`func_split`(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNSvarchar(255) CHARSET utf8

BEGIN

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

;;

DELIMITER ;

-- ----------------------------

-- Function structure for`func_split_TotalLength`

-- ----------------------------

DROP FUNCTION IF EXISTS`func_split_TotalLength`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` FUNCTION`func_split_TotalLength`(f_string varchar(1000),f_delimiter varchar(5)) RETURNSint(11)

BEGIN

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

END

;;

DELIMITER ;

-- ----------------------------

-- Function structure for `func_time_axis`

-- ----------------------------

DROP FUNCTION IF EXISTS `func_time_axis`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` FUNCTION `func_time_axis`(playbgnint(11),playend int(11),playalive int(11)) RETURNS varchar(255) CHARSET utf8

BEGIN

declareresult varchar(255) default '';

declarebgn int(11) default '0';

declareend int(11) default '0';

declarei int(11) default '0';

declareitime int(11) default '0';

SETbgn=playbgn ;

IFplayend>=playbgn THEN

SETend=playend;

ELSEIFplayalive>playbgn THEN

SETend=playalive;

ELSE

SETend=bgn;

ENDIF;

seti=0;

while (end>=bgn && i<30)do

SETresult = concat_ws(',',result,FROM_UNIXTIME(bgn-(bgn%300),'%H:%i'));

SETbgn=bgn+300;

SETi=i+1;

endwhile;

RETURNRIGHT(result,length(result)-1);

END

;;

DELIMITER ;

-- ----------------------------

-- Function structure for `isLowSpeed`

-- ----------------------------

DROP FUNCTION IF EXISTS `isLowSpeed`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION`isLowSpeed`(avgspeed int(11),bitrate int(11)) RETURNS int(11)

BEGIN

DECLARE rs tinyint default 0 ;

DECLARE lowspeedline int default 1024 * 120 ;

setrs = IF(avgspeed <= lowspeedline, 0, 1) ;

return rs ;

END

;;

DELIMITER ;