mysql 两个逗号分隔_如何使用MySQL比较两个逗号分隔的字符串列表

I used a Java method called 'containsAll()' to check if ArrayLists have common content.

Let's say I have a list A (one row), and several other lists in a MySQL table (in column 'name', row by row).

All lists consist of comma-separated Strings (at least one String in a list) - names or whatever.

Now, I want to check if all Strings in list A can be found in any of the rows in column 'name'.

The result set should show all the rows in 'name' that match, that includes rows/lists must have all Strings in list A, and can have additional Strings.

Example I

A: 'Mr.T'

____name_________________________________________

'Hannibal'

'Hannibal','Face','Murdock','Mr.T','Donald Duck'

'Face','Donald Duck'

'Superman','Chuck Norris','Mr.T'

_________________________________________________

Result set: 'Hannibal','Face','Murdock','Mr.T','Donald Duck' -AND-

'Superman',Chuck Norris','Mr.T'

Example II

A: 'Rocky', 'Mr.T', 'Apollo'

______name__________________________________________________

'Hannibal','Face','Murdock','Donald Duck','Superman','Mr.T'

'Rocky','Apollo','Ivan'

'Apollo', 'Superman','Hannibal','Rocky','Mr.T','Chuck Norris'

'Rocky','Mr.T','Apollo','Chuck Norris'

_____________________________________________________________

Result set: 'Apollo', 'Superman','Hannibal','Rocky','Mr.T','Chuck Norris' -AND-

'Rocky','Mr.T','Apollo','Cuck Norris'

I wonder if one can carry out those results using a MySQL query.

Thank you in advance.

解决方案

It appears you want to do an array intersection, except your array is a single column. It can be done, but it will be slow, difficult to debug and will not leverage the power of relational databases. A better way would be to change your table schema to something like this:

Table groups

group_id int unsigned not null auto_increment primary key,

character_list text

Table members_in_group

group_id int unsigned not null,

group_member varchar(45) not null

Then you can query like this:

SELECT group_id, character_list

FROM groups g

JOIN members_in_groups m USING (group_id)

WHERE m.group_member IN ('Mr. T', ...);

The groups table is probably very like your current table. The members_in_groups table is the same data chopped up into easily searchable parts.

ETA given your comment, this should work if you can guarantee that each character_list contains only one instance of each character:

SELECT group_id,

SUM(CASE m.group_member IN ('Mr. T', 'Apollo', 'Rocky') THEN 1 ELSE 0 END) AS tally,

character_list

FROM groups g

JOIN members_in_groups m ON (g.group_id=m.group_id)

GROUP BY group_id

HAVING SUM(CASE m.group_member IN ('Mr. T', 'Apollo', 'Rocky') THEN 1 ELSE 0 END) = 3;

In this case the HAVING clause must equal 3 because there are 3 members in IN ('Mr. T', 'Apollo', 'Rocky').