0

I work with a system that used comma separated values as a one-2-many relation. It is stored in as a blob.

I try to use the MySQL IN (LIST), but only ends up with the rows where the uid is first in the list or only one in the list.

mytable
uid categories
1   24,25,26
2   25
3   22,23,25
4   25,27

run sql:
SELECT * FROM mytable WHERE 25 IN (categories)

Result:
uid categories
2   25
4   25,27

Missing (should have been selected but are not)
uid categories
1   24,25,26
3   22,23,25

Any idea why a string has to start with 25 and not just contain 25? I guess it is a string issue rather than an IN (LIST) issue

UPDATE - based on answers below:

When using th IN (LIST) on a blob, the blob is converted to an int and commas and "digits" are lost.

In stead use FIND_IN_SET(needle, haystack) that will work also on a blob containing comma separated values.

Tillebeck
  • 3,493
  • 7
  • 42
  • 63

4 Answers4

3

I think you are looking for FIND_IN_SET

 SELECT * FROM mytable WHERE FIND_IN_SET(25,category)
Shakti Singh
  • 84,385
  • 21
  • 134
  • 153
3

This should give you your asnwer:

SELECT * FROM mytable WHERE CONCAT(',', categories, ',') LIKE '%,25,%'

But it would make more sense to create a connecting table, with each comma separated value as a new row.

vbence
  • 20,084
  • 9
  • 69
  • 118
  • will also "find" a field with "225". – Raffael Mar 28 '11 at 10:24
  • -1 except if there's an id 125... – jswolf19 Mar 28 '11 at 10:24
  • @jswolf19 You should wait a little time for me to react before -1. Thanks! – vbence Mar 28 '11 at 10:26
  • @vbence, If you edit in an acceptable fashion, then I'll take away the -1, but that seems like a consideration one should take into account before posting. Just think if the OP were looking for id 2 in the data the example. – jswolf19 Mar 28 '11 at 10:31
  • @jswolf19 So what is your opinion about the current solution? – vbence Mar 28 '11 at 10:33
  • @vbence, it should work, but it is a kludge (as you admit, which makes it acceptable, overlooking typos, anyway ^_~) – jswolf19 Mar 28 '11 at 10:36
  • @jswolf19 Yes, it's not an ideal solution, but it's safe to assume that OP is currently under mortar fire and needs to get some coordinates out of the database right away (no time to redesign the DB). In that case it's a pretty good solution isn't it? :) – vbence Mar 28 '11 at 10:46
  • @vbence, Yeah, although I like Shakti's find of `FIND_IN_SET` a little better, I have to admit. I can't imagine either being terribly efficient, though. – jswolf19 Mar 28 '11 at 10:50
  • This one works too. I actually did it this way to get on with the show. Now that I have learned about the FIND_IN_SET I will update my call to use that. – Tillebeck Mar 31 '11 at 08:06
1

What's happening is that MySQL is converting the blob to an integer (not a list of integers) for comparison. So '24,25,26' becomes the value 24. You can confirm this by running the query 

SELECT CAST(categories AS signed) FROM mytable

This is not how IN (nor the blob data type) is meant to be used. Is there a reason you're not using another table for this?

jswolf19
  • 2,303
  • 15
  • 16
  • Sorry, but this is not an answer. – vbence Mar 28 '11 at 10:32
  • @vbence, I think I do answer the question, "Any idea why a string has to start with 25 and not just contain 25?" – jswolf19 Mar 28 '11 at 10:33
  • THANK YOU. That is exactly what happens. The blob is treated as an integer. I can se that now. Now I need to fidure out how to cast it to CHAR (did try with no luck) so I can do the IN on the string. I am quite sure the IN is ment for this. But obviously, the blob is not. – Tillebeck Mar 31 '11 at 07:56
  • Oh, forgot your last question. I work on a framework that quite often uses one-2-many relations as comma separated strings stored as blobs. Before I have selected them using php, and reused them in another query as IN (LIST). No problem. This is first time I have tried to do it in a single query, where aparently the FIND_IN_SET is the right choice. – Tillebeck Mar 31 '11 at 08:12
  • @Tillebeck, unfortunately, `IN` is not meant for the particular method you're using. When you used two queries, you inserted the string into the query causing it to be interpreted as multiple values, but with a single query, using a column name will only ever be interpreted as a single value. Thus, `FIND_IN_SET` is probably the best option for your case, although if the many in your one-2-many really is many, then another table will most likely be more efficient (although that may not be a design decision you can make). – jswolf19 Apr 08 '11 at 09:38
0

Yes, it's astring issue. Your 'list' is just a string to mysql, meaning nothing.

You would have to use RegEx.

But you might think about normalizing your tables instead.

Raffael
  • 19,547
  • 15
  • 82
  • 160