mysql 字符串 hash_Mysql分割字符串并对分割后的数据进行查询翻译

最近在处理ElasticSearch的数据同步。有一个需求要在sql里对字符串进行分割并对其进行翻译。

需要同步的表里的数据结构是这样子的，而mysql的函数是没有split的，只有SubString。这里重点就是通过SubString函数得到split的效果。

'103,105,106,107'

翻译的表结构是这样子的

需要通过dic_kind和dic_code两个数据找到对应的翻译字段。

可以看到翻译字典表里的数据只支持一个一个数据的翻译。系统里是在java后台通过分割字符串进行翻译，这里由于是用logstash同步数据，所以只能在sql里直接翻译。

想法

对字符串进行分割，然后对每一个分割的数据进行翻译，随后再把翻译后的数据添加在一起返回。

函数代码

CREATE DEFINER=`root`@`localhost` FUNCTION `translate`(dat VARCHAR(255),type VARCHAR(255)) RETURNS varchar(255) CHARSET utf8mb4

BEGIN

DECLARE i INT DEFAULT 1;

DECLARE str VARCHAR(255);

DECLARE returnStr VARCHAR(255);

while i <= (LENGTH(dat)-LENGTH(REPLACE(dat,',','')) + 1) DO

SELECT DIC_DESC from ts_dic where DIC_CODE = SUBSTRING_INDEX(SUBSTRING_INDEX(dat,',',i),',',-1) AND DIC_KIND = type into str;

SET returnStr = CONCAT_WS(',',returnStr,str) ;

SET i=i+1;

END WHILE;

RETURN returnStr;

END

解释：

LENGTH(dat)-LENGTH(REPLACE(dat,',','')) + 1

LENGTH：获取字符串长度

REPLACE(p1,p2,p3):替换函数，p1是要替换的字符串，p2：要被替换掉的字符串，p3：用什么来替换

所以这一句代码的作用就是为了得到字符串被分割后的长度，如果是103,105,106,107的话，最后的值就是4.因为替换掉的只有中间的三个字符，所以最后要加一才是对应的长度。

解释

SELECT DIC_DESC from ts_dic where DIC_CODE = SUBSTRING_INDEX(SUBSTRING_INDEX(dat,',',i),',',-1) AND DIC_KIND = type into str;

这句是精华

SUBSTRING_INDEX(p1,p2,p3)：对字符串进行截取，p1是要被截取的字符串，p2是从这个字符开始截取。

p3如果是正数，则从左往右数第几个p2的位置。如果是1，则是从左往右数第一个p2的位置开始往左截取，如果是2，便是从左往右数第二个p2的位置开始往左截取。

p3如果是负数，则是从右往左数第几个p2的位置开始往右截取。

例子1：

SUBSTRING_INDEX(‘103,105,106,107’ , ‘,’ ,1) 之后得到的便是103

再经过外层的分割 SUBSTRING_INDEX(‘103’ , ‘,’ ,-1) 得到的还是103

例子2：

SUBSTRING_INDEX(‘103,105,106,107’ , ‘,’ ,2) 之后得到便是103,105

再经过外层的分割 SUBSTRING_INDEX(‘103,105’ , ‘,’ ,-1) 得到的便是105

这样通过SUBSTRING_INDEX 函数便能得到Java后台split的效果。

这样拿到对应的编码之后就可以去对应的表里寻找对应的翻译字段了。最后将查到的数据放入到str中

最后将str与returnStr拼接到一起返回。这里用到的CONCAT_WS 拼接函数可以保证如果一方为空，最终结果不会为空。

这样函数就可以使用了，只需要传入对应的编码值还有对应的dic_kind便可以对其进行翻译。

SELECT translate('104,106', 'crClass') as title

结果