在单个查询中搜索逗号分隔的MySQL列中的多个值

问题描述：

I am fully aware of how bad it is to have Delimited Lists in a Table Column, instead of normalizing data into multiple tables.

Although, I do have a table structure designed years ago, which I will re-design soon.
Simplified, it looks like this:

Table: Newsletters
+------------+---------------+
| subject    | mailing_lists |
+------------+---------------+
| A Test     | 1,2           |
| More Tests | 2,3           |
+------------+---------------+

You can see it in this SQLFiddle, if you prefer to.

Recently, I gave my users the option to write a delimited list of mailing list ids (like 1,3), as a feature to select which newsletters to show in a view.
(e.g. Only show newsletters that were sent to lists with id 1 or 3)

So: A table column with a delimited list of ids, and delimited ids as an input.

This would obviously be much easier if the tables were normalized.

So, I solved this in PHP, by exploding the input ids and iterating them to create a query like the one in the fiddle mentioned above, which looks like this:

SELECT * FROM `newsletters`
  WHERE FIND_IN_SET("1", `mailing_lists`) > 0
     OR FIND_IN_SET("3", `mailing_lists`) > 0

This query perfectly fetches the data I want to get, but I can only create it programmatically, since I have to add a new condition for each id in my delimited list.

Question is: Out of pure curiosity: Is there a way to avoid the loop in PHP, and make a query without splitting the ids in code?

答

After the very useful post by rakeshjain, I managed to transform my query into this:

SELECT * FROM (SELECT *,
               `mailing_lists` REGEXP REPLACE("1,3", ',', '(\\,|$)|')
                  as haslists
               FROM `newsletters` B) A
  WHERE A.haslists = 1

In the above, I assume that "1,3" is the value provided by the user.
Here is the solved fiddle: http://sqlfiddle.com/#!2/4621b0/19

Thank you rakeshjain!

答

Yes, there is a way to avoid the loop. Just normalize your database structure.

Table "Subjects" subjectID (INT(11), auto_increment)) | subject (varchar(255))

Table "MailingLists" listID (INT(11), auto_increment) | listName

Table "Subjects2Lists" (many to many) subjectID (index) | listID (index)

So you can get every single list ID by executing a simple select / join statement.

SELECT 
    list.listID, 
    names.listName 
FROM 
    Subject2Lists AS list 
LEFT JOIN 
    MailingLists AS names 
    ON (list.listID = names.listID) 
WHERE 
    subjectID = 1

在单个查询中搜索逗号分隔的MySQL列中的多个值

相关推荐