mysql分割字符串_mysql分割字符串

项目有通过一批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 ;