MySQL search in comma list

Question

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?

score 82 · Accepted Answer · edited May 03 '12 at 19:42

82

You need the FIND_IN_SET function:

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

edited May 03 '12 at 19:42

Bohemian

412,405
93
575
722

answered Mar 28 '11 at 12:06

awm

6,526
25
24

how to find multiple values – nilesh Feb 07 '17 at 00:38
1

Beware: `FIND_IN_SET` doesn't uses indexes on table. – Gaurav Gandhi Dec 16 '19 at 11:20

score 18 · Answer 2 · edited Jul 05 '15 at 19:42

18

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.

edited Jul 05 '15 at 19:42

livestrong

65
6

answered Apr 18 '12 at 09:47

Scott Chu

972
14
26

score 7 · Answer 3 · answered Mar 28 '11 at 12:15

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.

score 4 · Answer 4 · answered Mar 28 '11 at 12:07

4

FIND_IN_SET is your best bet

 SELECT ... WHERE FIND_IN_SET(1,field_name)

answered Mar 28 '11 at 12:07

Shakti Singh

84,385
21
134
153

The first parameter needs to be a string. – awm Mar 28 '11 at 12:09

score 3 · Answer 5 · answered Aug 10 '16 at 01:29

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.

MySQL search in comma list

5 Answers5

Linked

Related