MySQL查询:将逗号分隔的值与包含逗号分隔的字符串的列匹配 [英] MySQL Query : Match comma separated value with column containing comma separted string

问题描述

我有一个用户表，其中包含一个用逗号分隔的兴趣ID作为值的列(例如兴趣). 例如

I have a user table containing a column(say interests) with comma separated interest ids as a value. e.g.

user  interests
A     12,13,15
B     10,11,12,15
C     9,13
D     10,12

现在，我有一个字符串，其逗号分隔值为"13,15".

Now, I have a string with comma separated values as "13,15".

我想从上表中获取具有兴趣13,15的用户，这意味着它应该返回用户A，B和C，因为用户A包含了兴趣(13,15)，用户B将该兴趣与15&用户将兴趣与13匹配.

I want to fetch the users who has the interest 13,15 from above table means it should return the user A, B & C as user A contains both interest(13,15), user B matched the interest with 15 & user matched the interest with 13.

什么是SQL，因为我的表中有很多用户.

what will be the SQL as I have a lots of users in my table.

推荐答案

可以使用regexp来实现，如@ 1000111所述，但是使用更复杂的regexp.看看这个，例如:

It can be done with regexp as @1000111 said, but with more complicated regexp. Look at this, for example:

(^|,)(13|15)(,|$)

这将不匹配135中的13，或13中的1，依此类推.例如，对于数字13，它将匹配下一个字符串:

This will not match 13 from 135, or 1 from 13 and so on. For example, for number 13 this will match next strings:

1,13,2
13,1,2
1,13
13,2
13

但不会与这些匹配

1,135,2
131,2
1,113

这是查询:

SET @search = '13,15';

SELECT *
FROM test
WHERE interests REGEXP CONCAT('(^|,)(', REPLACE(@search, ',', '|'), ')(,|$)')

这篇关于MySQL查询:将逗号分隔的值与包含逗号分隔的字符串的列匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！