mysql语句locate与substring联合使用方法

一、应用场景

假设数据库有这样的数据：

1_1_xiao_ming
2_22_zhang_mou_mou
3_333_qian_x_s

现在需要把第2个下划线后的数据查询出来；
如果只用substring_index，较难实现；
此时可以联合使用locate与substring实现，样例sql如下：

select

substring(test,LOCATE('_',test,3)+1),
substring_index(test,'_',-2)

from ttt;

这里做了下对比，可以看到，如果只用substring_index,由于无法确定后面有多少个_，所以不太行，查到的姓名不全；
而联合使用locate与substring，就可以实现需求，把第2个下划线后的数据查询出来。

二、详细说明

1.locate用法

样例1：

LOCATE('_',test)

这个意思是，返回test字段中、首次出现的’_'的位置下标。

样例2：

LOCATE('_',test,3)

这个意思是，返回test字段中、从第3个位置以后的、第一个出现的’_'的位置下标。
上方样例中，第一个'_'出现的位置一般是第2位，但是第二个'_'出现的位置就不固定了，所以需要用locate，找到第二个'_'出现的位置。

2.substring用法

样例1：

substring(test,LOCATE('_',test,3)+1)

这个意思是，返回test字段中、从第3个位置后出现的第一个'_'的位置+1、到最后为止的内容。
上方样例中，就是用这个查询到第2个下划线后的数据的。

样例2：

substring(test,3,1)

这个意思是，返回test字段中、从第3个位置开始、长度为1的内容。

3.substring_index用法

样例1

substring_index(test,'_',-2)

这个的意思是，返回test字段中、按照'_'分隔、倒数第2个'_'开始、到末尾的内容。
上方样例中，倒数第2个下划线开始到末尾的内容，不能符合要求。

样例2

substring_index(test,'_',2)

这个的意思是，返回test字段中、按照'_'分隔、从开始到正数第2个'_'之间的内容。
上方样例中，这样也不符合要求。