mysql split函数用逗号分隔的实现

DELIMITER $$
USE `mess`$$
DROP PROCEDURE IF EXISTS `splitString`$$
CREATE DEFINER=`root`@`%` PROCEDURE `splitString`(IN f_string VARCHAR(1000),IN f_delimiter VARCHAR(5))
BEGIN  
  DECLARE t INT DEFAULT 0;  
  DECLARE i INT DEFAULT 0;  
  SET t = 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 < t  
  DO  
    SET i = i + 1;  
    INSERT INTO tmp_split(`val_`) VALUES (func_splitString(f_string,f_delimiter,i));  
  END WHILE;  
END$$
DELIMITER ;

REPLACE(str,from_str,to_str)

Returns the string str with all ourrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.
例如
mysql> SELECT REPLACE('.mysql.', 'w', 'Ww');
    -> 'WwWwWw.mysql.'

DELIMITER $$

USE `mess`$$

DROP FUNCTION IF EXISTS `func_get_splitStringTotal`$$

CREATE DEFINER=`root`@`%` FUNCTION `func_get_splitStringTotal`(  
f_string VARCHAR(10000),f_delimiter VARCHAR(50)  
) RETURNS INT(11)
BEGIN  
 RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));  
END$$

DELIMITER ;

（1）REVERSE(str)

Returns the string str with the order of the characters reversed.
例如mysql> SELECT REVERSE('abc');
    -> 'cba'

（2）
SUBSTRING_INDEX(str,delim,count)


Returns the substring from string str before count ourrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

例如
mysql> SELECT SUBSTRING_INDEX('.mysql.', '.', 2);
    -> '.mysql'
mysql> SELECT SUBSTRING_INDEX('.mysql.', '.', -2);
    -> 'mysql.'

DELIMITER $$

USE `mess`$$

DROP FUNCTION IF EXISTS `func_splitString`$$

CREATE DEFINER=`root`@`%` FUNCTION `func_splitString`( 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 ;