mysql分割字符串

最新推荐文章于 2024-01-11 09:57:46 发布

weixin_33963594

最新推荐文章于 2024-01-11 09:57:46 发布

阅读量360

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://segmentfault.com/a/1190000013395614

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