mysql分割字符串

2018-03-01 11:07:28来源:segmentfault作者:Cedrus人点击

分享

项目有通过一批ID去过滤结果的需求,因为这个ID是从其他平台拉下来的excel,为了避免加引号逗号的麻烦,在mysql存储过程里面拼接。在此做个记录。很多地方用得上。
1.通过某个字符,分割字符串的函数。输入分别为f_string(待分割字符串),f_delimiter(分割字符),f_order(取的字符串的index)


DELIMITER $$USE `money`$$DROP FUNCTION IF EXISTS `F_SPLIT_STRING`$$CREATE DEFINER=`root`@`%` FUNCTION `F_SPLIT_STRING`(
f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(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 ;

2.要循环取分割出来的字符串就得知道分割出来多少个字符串


SELECT LENGTH(APPSFLYERID) - LENGTH(REPLACE(APPSFLYERID, '/n', '')) + 1 INTO @APPSFLYERID_CNT;

3.处理一串以n分割的字符串,把它们加上分号和逗号。
'1518393292248-9957634
1518404097075-8568716
1518392385006-9013409'转换成
'1518393292248-9957634','1518404097075-8568716','1518392385006-9013409'


DELIMITER $$
USE `money`$$DROP PROCEDURE IF EXISTS `P_GET_ROI_BY_APPFLYER_ID`$$CREATE DEFINER=`root`@`%` PROCEDURE `P_GET_ROI_BY_APPFLYER_ID`(IN STAT_DATE VARCHAR(32),IN DAY_CNT INT,IN APPSFLYERID MEDIUMTEXT)
BEGIN
SET @APPSFLYERID_STR = "";
SET @TEMP_ID = "";
SELECT LENGTH(APPSFLYERID) - LENGTH(REPLACE(APPSFLYERID, '/n', '')) + 1 INTO @APPSFLYERID_CNT;SET @i = 1;
WHILE @i <= @APPSFLYERID_CNT DO
SELECT F_SPLIT_STRING(APPSFLYERID,"/n",@i) INTO @TEMP_ID;
IF @i = 1 THEN
SET @APPSFLYERID_STR = CONCAT("'",@TEMP_ID,"'");
ELSE
SET @APPSFLYERID_STR = CONCAT(@APPSFLYERID_STR,",'",@TEMP_ID,"'");
END IF;
SET @i = @i + 1;
END WHILE;
SELECT @APPSFLYERID_STR;
END$$DELIMITER ;

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台