Mysql split函数实例分享
发布时间：2016-03-24 编辑：jiaochengji.com
本文介绍下，在mysql数据库中实现split函数的方法，并提供了split函数的调用示例，供大家学习参考。
首先，创建split函数。
代码：
复制代码 代码示例:
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 ;
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 ;
2，设置参数：
复制代码 代码示例:
SET GLOBAL log_bin_trust_function_creators = 1;
3，测试用例：
复制代码 代码示例:
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)
-> "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_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)
+---------------------------------+
| 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)
您可能感兴趣的文章：
MySql Split函数二例
mysql自定义split函数(字符串分割函数示例)
mysql存储过程实现split函数的例子
mysql split函数代码二例
mysql函数split的三种实现方法
mysql函数split分割字符串代码
mysql split函数多种实现方法
mysql实现字符串分割SPLIT函数的四种方法
mysql split函数用法实例
js split函数的用法说明
[关闭]