mysql数字逗号分隔_mysql实现逗号分隔的数据包含关系

需求：

有一个字符串逗号分隔，‘65,110,141’，tablea表里有个字段ids也是逗号分隔的数字。要查询ids里被指定字符串完全包含的所有记录。

为了实现这个问题。特意让数据库中字段ids入库是进行了排序加逗号存入。比如‘65，110’；‘65，67，141’等。

开始以为直接使用regexp正则即可满足需求：

select * from table

where ‘65,110,143’ REGEXP ids

结果能查出大部分情况下的匹配关系。测试时发现：

ids是【65，110 】【110，143】【65，110，143】都可以匹配出来，但【65，143】的无法查询出来。

查了很多文章，基本都是利用FIND_IN_SET函数查询某个字段是否包含在一个逗号分隔得字符串里。也有利用substring_index进行行列转换再进行匹配查询的。都不满足我这个场景。

正当要放弃，改用java程序实现时。来了一个小灵感，利用substring_index和FIND_IN_SET两个结合的办法。总体思路是这样的：列转行之后 利用find_in_set进行查找包含关系之后。 再利用group by行转列。然后对比源与find_in_set后转成的值是否还相等。首先利用substring_index和临时表【autoincrement】只有一个自增字段id，需提前插入一定量的数据【数据量大小跟tablea表里ids里逗号分隔的数据多少有关，一定大于最多的那条】。把tablea的ids列转成行。

SELECT

a.tid,

a.ids AS sourceids,

substring_index( substring_index( a.ids, ',', b.id ), ',',- 1 ) AS splitValue

FROM

tablea a

JOIN autoincrement b ON b.id <= (

length( a.ids ) - length(

REPLACE ( a.ids, ',', '' )) + 1)tablea表里的初始数据执行sql之后的数据

2.利用FIND_IN_SET函数查询字段splitValue在指定字符串中的所有数据。

SELECT

*

FROM

(

SELECT

a.tid,

a.ids AS sourceids,

substring_index( substring_index( a.ids, ',', b.id ), ',',- 1 ) AS splitValue

FROM

tablea a

JOIN autoincrement b ON b.id <= ( length( a.ids ) - length( REPLACE ( a.ids, ',', '' )) + 1 )

) AS T

WHERE FIND_IN_SET(T.splitValue,'65,110,141' )查出所有包含在指定字符串内的所有记录

3.再利用group by行转列

SELECT

tid,

sourceids,

GROUP_CONCAT( splitvalue ORDER BY ( splitvalue + 0 ) ASC SEPARATOR ',' ) AS seekids

FROM

(

SELECT

a.tid,

a.ids AS sourceids,

substring_index( substring_index( a.ids, ',', b.id ), ',',- 1 ) AS splitValue

FROM

tablea a

JOIN autoincrement b ON b.id <= ( length( a.ids ) - length( REPLACE ( a.ids, ',', '' )) + 1 )

) AS T

WHERE

FIND_IN_SET( T.splitValue, '65,110,141' )

GROUP BY

tid,

sourceids行转列之后的值

4.最后一步对比源ids与合并行之后的ids是否一致。一致的就是被完全包含的满足需求的数据。

SELECT

*

FROM

(

SELECT

tid,

sourceids,

GROUP_CONCAT( splitvalue ORDER BY ( splitvalue + 0 ) ASC SEPARATOR ',' ) AS seekids

FROM

(

SELECT

a.tid,

a.ids AS sourceids,

substring_index( substring_index( a.ids, ',', b.id ), ',',- 1 ) AS splitValue

FROM

tablea a

JOIN autoincrement b ON b.id <= ( length( a.ids ) - length( REPLACE ( a.ids, ',', '' )) + 1 )

) AS T

WHERE

FIND_IN_SET( T.splitValue, '65,110,141' )

GROUP BY

tid,

sourceids

) T2

WHERE

T2.sourceids = T2.seekids最终结果

注意事项：首先tablea中表ids是按顺序逗号分隔入库的。group by合并行时要按入口的顺序排序合并如一下代码：

GROUP_CONCAT( splitvalue ORDER BY ( splitvalue + 0 ) ASC SEPARATOR ',' ) AS seekids

这两个顺序必须一致。

PS：由于我业务中tablea表数据量很小，目前可以使用此sql一次完成需求。数据量大时请谨慎参考。