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

Question

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?

Answer 1

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!