How to compare two arrays in mysql query?

Question

I have a table named mm_test like follows:-

id    educations 
1     3,4,6,8,9

I want to check whether this array contain (4,6). How to check this using mysql query. Mysql 'IN' command is not working for me.

I put the query like this...

select * from mm_test where educations IN (4,6);

But this query returned empty result. Please any one help me.

Don't use SQL for this. Just store the data in a flat file and use your preferred coding language. — Strawberry
– Strawberry, Commented Mar 9, 2015 at 12:18
FIND_IN_SET() and mysqli_num_rows() would work beautifully. Which API are you using to connect with? — Funk Forty Niner
– Funk Forty Niner, Commented Mar 9, 2015 at 12:51
"But this query returned empty result. Please any one help me." - Show your full code and the API you are using to connect to your database with. Otherwise, I'm not going to put in a working answer from my own test. Good luck. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 9, 2015 at 14:19

Gordon Linoff · Accepted Answer · 2015-03-09 12:10:36Z

8

You can do this using find_in_set():

where find_in_set(4, educations) > 0 and
      find_in_set(6, educations) > 0

However, this is generally inefficient. The problem is your data structure. You should be using a junction table with one column for each education, instead of storing a list of integers in a highly-inappropriate data structure -- a string.

answered Mar 9, 2015 at 12:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rakhi Vijayan Over a year ago

Thank you for your answer, but I think find_in_set will check for only one number in the array. I know the table structure is not proper, but I need like this to avoid join queries

Gordon Linoff Over a year ago

@RakhiVijayan . . . That's why there are two conditions. You should fix the data structure, if you have any influence over that.

Funk Forty Niner Over a year ago

Shouldn't that be find_in_set(educations,4) etc. ? As per dev.mysql.com/doc/refman/5.0/en/… SELECT FIND_IN_SET('b','a,b,c,d');

Jay Blanchard Over a year ago

@Fred-ii- the order in the answer is right - needle (integer), haystack(educations)

Funk Forty Niner Over a year ago

@JayBlanchard I always thought it was the other way around.

|

Jay Blanchard · Accepted Answer · 2015-03-09 13:00:37Z

0

Use this FIND_IN_SET Manual

SELECT * 
FROM mm_test 
WHERE FIND_IN_SET(4, educations) > 0 
AND FIND_IN_SET(6, educations) > 0;

edited Mar 9, 2015 at 13:00

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

answered Mar 9, 2015 at 12:13

I'm Geeker

4,6575 gold badges25 silver badges42 bronze badges

Collectives™ on Stack Overflow

How to compare two arrays in mysql query?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related