MYSQL passing string into IN operator not working in nodejs

Question

by right SELECT topicFollowed FROM User WHERE id = 10000) return "[0,2]" <<-string

but when i execute SELECT * from News WHERE topicId IN (SELECT topicFollowed FROM User WHERE id = 10000) only 0 is pass in and 2 is not. i cant figure out why. it only return topicId that is 0

Gordon Linoff · Accepted Answer · 2016-09-23 01:35:01Z

1

Your IN list consists of exactly one item, a string of the form '[0,2]'. If that is what topicId looks like, then your query will work.

In other words, you have a problem with your data structure. You shouldn't be storing lists of things in strings. The proper data structure would have two rows, one for each of the ids in the list.

Sometimes, you are stuck with someone else's bad data design. You can do what you want, by doing something like this:

SELECT n.*
from News n
WHERE EXISTS (SELECT 1
              FROM user u
              WHERE find_in_set(topicId, replace(replace(topicFollowed, '[', ''), ']', '') AND
                    id = 10000
             );

answered Sep 23, 2016 at 1:35

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

phongyewtong Over a year ago

I am storing array in varchar, is this the correct way or there is a better way to do this?

Gordon Linoff Over a year ago

@phongyewtong . . . Yes, use a junction table, with one row per id and one row per topic id.

Collectives™ on Stack Overflow

MYSQL passing string into IN operator not working in nodejs

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related