mysql 存储过程 substring_index,mysql 利用分割符SUBSTRING_INDEX进行字符串数据拆分，...

mysql 利用分割符SUBSTRING_INDEX进行字符串数据拆分，

我们要将一个字符串的数据根据指定的符号进行拆分，我这里用逗号(，)分割，要将数据分成每条4个数据，结果如下图：

准备数据表：substring_index_test

其中我们要用到的关键函数：

substring_index(str,delim,count)：(被截取字段，关键字，关键字出现的次数)

length(str)：(字段)  (一个汉字是算三个字符，一个数字或字母算一个字符)

replace(field,find_str,replace_str): (被替换字段，字段中被替换内容，替换的内容)

cast(value as type)：(被转换类型的值 as 转换的类型)

ceil(X):返回不小于X的最小整数值。

我们先做出每条数据的结果：

1.进行每条值查询：

select SUBSTRING_INDEX(t.name,',','4') from substring_index_test t;

结果：

2.第二条数据查询：

select SUBSTRING_INDEX(SUBSTRING_INDEX(t.name,',',-(length(t.name) - length(REPLACE(t.name, ',', '')) + 1)+4),',','4') from substring_index_test t;

结果：

3.第三条数据查询：

select SUBSTRING_INDEX(SUBSTRING_INDEX(t.name,',',-(length(t.name) - length(REPLACE(t.name, ',', '')) + 1)+8),',','4') from substring_index_test t;

结果：

可以看出规律，得出以下结论：

select SUBSTRING_INDEX(SUBSTRING_INDEX(t.name,',',-(length(t.name) - length(REPLACE(t.name, ',', '')) + 1)+4x),',','4') from substring_index_test t， x为变量。

我们单独查出数据，然后我们进行组合：

4.我们可以利用length和replace来计算出可以拆分多少：先用length算出数据的长度，然后再用replace替换掉(，)，再用length算出长度，再进行相减+1，最终得到可以分割的值；如下：

SELECT (length(t.name) - length(REPLACE(t.name, ',', '')) + 1) FROM substring_index_test t

结果如下：

然后算出我们需要看出有多少行数据，利用ceil(x)函数,如下：

SELECT ceil((length(t.name) - length(REPLACE(t.name, ',', '')) + 1)/4) FROM substring_index_test t;

结果：

然后我们可以根据以上规则进行关联查询，请注意：cast(h.help_topic_id as SIGNED INTEGER) * 4)，h.help_topic_id这是一个游标，所以不能进行法则运算，必须转化为有符号的数据类型。

select SUBSTRING_INDEX(SUBSTRING_INDEX(t.name,',',-(length(t.name) - length(REPLACE(t.name, ',', '')) + 1)+(cast(h.help_topic_id as SIGNED INTEGER) * 4)),',','4') from substring_index_test t join

mysql.help_topic h

on h.help_topic_id < ceil((length(t.name) - length(REPLACE(t.name, ',', '')) + 1)/4);

结果：

如果我想变成一个每条3个数据的怎么办？那我们就用存储过程来实现，设置一个变量x，如下：

DELIMITER //

CREATE PROCEDURE substring_index_test(in x INT)

BEGIN

select SUBSTRING_INDEX(SUBSTRING_INDEX(t.name,',',-(length(t.name) - length(REPLACE(t.name, ',', '')) + 1)+(CONVERT(h.help_topic_id, SIGNED INTEGER) * x)),',',x) from substring_index_test t join

mysql.help_topic h

on h.help_topic_id < ceil((length(t.name) - length(REPLACE(t.name, ',', '')) + 1)/x);

END //

DELIMITER ;

有什么问题，欢迎交流。

相关文章暂无相关文章