想在mysql里把一段用固定符号分隔的字符串,按分隔符分别列出来。就是一个典型的split应用,但是mysql却没有自带这个函数,在网上搜了一把。可以通过自建函数来解决,以下的函数来自互联网。
-
DELIMITER $$
-
CREATE FUNCTION `func_get_split_string_total`(
-
f_string varchar(1000),f_delimiter varchar(5)
-
) RETURNS int(11)
-
BEGIN
-
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
-
END$$
-
DELIMITER ;
-
-
-
DELIMITER $$
-
CREATE FUNCTION `func_get_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 ;
需要设置下参数:
SET GLOBAL log_bin_trust_function_creators = 1;
下面我们来测试下:
-
CREATE TABLE "t" (
-
-> "t1" varchar(100) DEFAULT NULL,
-
-> "t2" int(11) DEFAULT NULL
-
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-
-
mysql> insert into t(t1,t2) values('a,b,c,d',1);
-
Query OK, 1 row affected (0.00 sec)
-
-
mysql> insert into t(t1,t2) values('a b c d',2);
-
Query OK, 1 row affected (0.00 sec)
-
-
mysql> select * from t;
-
+---------+------+
-
| t1 | t2 |
-
+---------+------+
-
| a,b,c,d | 1 |
-
| a b c d | 2 |
-
+---------+------+
-
2 rows in set (0.00 sec)
首先用func_get_split_string_total函数得到符合匹配的数量
-
mysql> select func_get_split_string_total(t1,',') from t where t2=1;
-
+-------------------------------------+
-
| func_get_split_string_total(t1,',') |
-
+-------------------------------------+
-
| 4 |
-
+-------------------------------------+
-
1 row in set (0.00 sec)
然后用func_get_split_string得出你想要的字符串
-
mysql> select func_get_split_string(t1,',',1) from t where t2=1;
-
+---------------------------------+
-
| func_get_split_string(t1,',',1) |
-
+---------------------------------+
-
| a |
-
+---------------------------------+
-
1 row in set (0.00 sec)
-
-
mysql> select func_get_split_string(t1,',',2) from t where t2=1;
-
+---------------------------------+
-
| func_get_split_string(t1,',',2) |
-
+---------------------------------+
-
| b |
-
+---------------------------------+
-
1 row in set (0.00 sec)
-
-
mysql> select func_get_split_string(t1,',',3) from t where t2=1;
-
+---------------------------------+
-
| func_get_split_string(t1,',',3) |
-
+---------------------------------+
-
| c |
-
+---------------------------------+
-
1 row in set (0.00 sec)
-
-
mysql> select func_get_split_string(t1,',',4) from t where t2=1;
-
+---------------------------------+
-
| func_get_split_string(t1,',',4) |
-
+---------------------------------+
-
| d |
-
+---------------------------------+
-
1 row in set (0.00 sec)
当然,这只是个思路,如果你想把所有字符串都列出来,可以写个过程,在里面循环一下。
希望能对这位朋友有帮助。