mysql字符串分割

1.需求

表：

所需结果：

2.实现(三种方式，相同的原理)

2.1、方式一: 使用MySql库中的自增序列表

SELECT
    a.id,
    SUBSTRING_INDEX( SUBSTRING_INDEX( a.type, ',', b.help_topic_id + 1 ), ',',- 1 ) AS type
FROM
    `tk_user_demand` AS a
    JOIN mysql.help_topic AS b ON b.help_topic_id < ( length( a.type ) - length( REPLACE ( a.type, ',', '' ) ) + 1 );

2.2、方式二: 自建自增序列表

CREATE TABLE `auto_increment` (
`id` int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `auto_increment` VALUES ('0'); 
INSERT INTO `auto_increment` VALUES ('1');
INSERT INTO `auto_increment` VALUES ('2');
INSERT INTO `auto_increment` VALUES ('3');
INSERT INTO `auto_increment` VALUES ('4');

SELECT
	a.id,
	a.email,
	substring_index( substring_index( a.NAME, ',', b.id + 1 ), ',',- 1 ) AS splitName 
FROM
	t_emp a
	JOIN auto_increment b ON b.id < (
	length( a.NAME ) - length( REPLACE ( a.NAME, ',', '' ) ) + 1 
	)

2.3、方式三: 以数据库里已有的表，构建自增序列表

备注: t_user表是库里已有表(可以不是序列表)，行数必须大于分割字段的最大逗号数

SELECT
	a.id,
	a.email,
	substring_index( substring_index( a.NAME, ',', b.id + 1 ), ',',- 1 ) AS splitName 
FROM
	t_emp a
	JOIN ( SELECT ( @ROW := @ROW + 1 ) AS id FROM t_user, ( SELECT @ROW :=- 1 ) AS zz ) AS b ON b.id < ( length( a.NAME ) - length( REPLACE ( a.NAME, ',', '' ) ) + 1 ) 
ORDER BY
	a.id

3.原理

这里用到Mysql的SUBSTRING_INDEX(str,delim,count)函数，LENGTH(str)函数，REPLACE(str,from_str,to_str)函数，和数据库的一张辅助表help_topic（这张表在默认的mysql库里）。

3.1、SUBSTRING_INDEX(str,delim,count)函数

参数：

str: 要分割的字符串

delim: 分割符（比如逗号,和点.）

count: 计数（分割到第几个），正数表示从左向右数，负数表示从右向左数

例如:

SELECT SUBSTRING_INDEX('www.baidu.com','.',1); -- 得到www
 
SELECT SUBSTRING_INDEX('www.baidu.com','.',2); -- 得到www.baidu
 
SELECT SUBSTRING_INDEX('www.baidu.com','.',-1); -- 得到com
 
SELECT SUBSTRING_INDEX('www.baidu.com','.',-2); -- 得到baidu.com

想要得到中间的baidu怎么办？这个要取两次才能得到的（也可以这么理解，一次从右向左取，一次从左向右取，哈哈，这个比较绕哈），第一次从右向左取，如上例子得到baidu.com，第二次将第一次的结果从左向右取，即可得到中间的字符baidu。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.baidu.com','.',-2),'.',1); -- 得到baidu

有了这个函数的帮助，还需要确定什么呢？需要确定，当前要分割的位置。

如何来获取当前要分割的位置呢？可以先获取总共能拆分成多少个字符串：

SELECT LENGTH('192.168.211.35'); -- 得到长度是14

3.2、LENGTH(str)函数

LENGTH(str) – MYSQL的LENGTH(str)函数，统计字符串的长度

3.3、REPLACE(str,from_str,to_str)函数

REPLACE(str,from_str,to_str) – MYSQL的REPLACE(str,from_str,to_str)函数，意思是替换字符串，用to_str替换from_str得到最终的str

参数：

str：最终得到的字符串

from_str: 旧的字符（被替换的字符）

to_str: 新的字符

结果就是用新的字符替换旧的字符，得到的最终的字符串

SELECT REPLACE('192.168.211.35','.',''); -- 得到19216821135
 
SELECT LENGTH(REPLACE('192.168.211.35','.','')); -- 得到长度是11
最终呢，SELECT LENGTH('192.168.211.35') - LENGTH(REPLACE('192.168.211.35', ',', '')) + 1;  -- 得到长度是4

那么其实我们想要的就是遍历前三个，分别获取当前位置的字符串：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('192.168.211.35',',', index), ',', -1)

4.小结

1. 序列表必须从0开始，行数与最多的逗号个数有关，行数至少比最长逗号个数加1，可以建0~1000。（其实也可以不从0开始，但是sql语句要做相对应的修改）

2. 为什么不用MySQL自带的自增序列表mysql.help_topic？因为好多公司的数据库是没有权限操作这些表的, 不能使用。

3. 序列表必须从0开始，行数与最多的逗号个数有关，行数至少比最长逗号个数加1，可以建0~1000。（其实也可以不从0开始，但是sql语句要做相对应的修改）

4. 为什么不用MySQL自带的自增序列表mysql.help_topic？因为好多公司的数据库是没有权限操作这些表的, 不能使用。