1：定義存儲(chǔ)過(guò)程，用于分隔字符串
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 cnt INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SET cnt = 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 cnt
DO
SET i = i + 1;
INSERT INTO tmp_split(`val_`) VALUES (func_splitString(f_string,f_delimiter,i));
END WHILE;
END$$
DELIMITER ;
2：實(shí)現(xiàn)func_get_splitStringTotal函數(shù)：該函數(shù)用于計(jì)算分隔之后的長(zhǎng)度，這里需要了解的函數(shù)：
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences 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('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
具體實(shí)現(xiàn)：
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 ;
3：實(shí)現(xiàn)func_splitString函數(shù)：用于獲取分隔之后每次循環(huán)的值，這里需要了解的函數(shù)：
（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 occurrences 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('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
具體實(shí)現(xiàn)：
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 ;
使用：
（1）調(diào)用存儲(chǔ)過(guò)程：
CALL splitString('1,3,5,7,9',',');
（2）：查看臨時(shí)表
SELECT val_ FROM tmp_split AS t1;
結(jié)果：
到此這篇關(guān)于mysql split函數(shù)用逗號(hào)分隔的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)mysql split逗號(hào)分隔內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- mysql存儲(chǔ)過(guò)程實(shí)現(xiàn)split示例
- MySQL里實(shí)現(xiàn)類似SPLIT的分割字符串的函數(shù)
- mysql函數(shù)split功能實(shí)現(xiàn)
