MySQL search in comma list [duplicate]

This question already has answers here: MySQL query finding values in a comma separated string (11 answers) Closed 5 years ago.

I have a MySQL field with a reference to another table where ids are saved as comma seperated list, eg:

12,13,14,16

which stand for values in another table. I know this is very bad and wrong, but this comes from above and I cant do anything about that. The problem now is that i want to search in that field with a query like this:

SELECT ... WHERE field LIKE '%1%'

The Problem now is obviously that almost all entries can be found with this example Query, because the most common IDs are in Range 10-20. My Idea is to search for %,1,% instead, but this does not work for the first and last 开发者_运维问答id in the field. Ist there something like an internal replace or how do i fix this the best way?

---

You need the FIND_IN_SET function:

SELECT ... WHERE FIND_IN_SET('1', field)

---

Be aware that plain FIND_IN_SET is case-insensitive,

i.e. FIND_IN_SET('b3','a1,a2,B3,b3') and FIND_IN_SET('B3','a1,a2,B3,b3') both return 3.

To be case sensitive, add 'binary' modifier to the 1st argument, e.g. FIND_IN_SET (binary 'b3', 'a1,a2,B3,b3') returns 4.

---

As others have said, Find_In_Set will let you write the query, but you really need to look at your database design (and I know you know this...)

The trouble with including Foreign Keys in a delimited list like this is that whole point of a foreign key is to enable you to locate the information in the other table quickly, using Indexes. By implementing a database as it sounds you have, you have all sorts of issues to resolve:

• How do I prevent duplicates (which would waste space)
• How do I remove a given value (Requires custom function, leading to possibility of errors?
• How do I respond to performance issues as the size of my tables increase?

There's only one truly acceptable way to address this - which is not to face the problem in the first place.

Have a sit down chat with those on high, and explain the problems with their solution - then explain the advantages of doing the job properly.

If they won't even discuss the point, look for a job with a decent employer who values your contributions.

Martin.

---

FIND_IN_SET is your best bet

 SELECT ... WHERE FIND_IN_SET(1,field_name)

---

After reading this question Id like to add that if your comma delimited list has spaces i.e. (1, 2,3 ,4) you will need to remove the leading/trailing spaces or use WHERE FIND_IN_SET(X,field) OR FIND_IN_SET(' X',field) OR FIND_IN_SET('X ',field)..... Just thought i'd share that since i came across that problem..... just gotta create those databases right the first time or they will give you all kinds of trouble.