Question 108602 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – Truncated incorrect DOUBLE value – using SELECT IN() on comma separated VARCHAR column

I have a MySQL table with a VARCHAR column VISITOR_ID that saves multiple or single visitor id values. I want the ENTERED_TEXT to have a 1 to many relationship, and don’t want to make multiple rows for that relationship to the VISITOR_ID – So I elected to add multiple id’s to the VISITOR_ID column. I now want to search that column for multiple related id’s.

| VISITOR_ID  |      ENTERED_TEXT        |
------------------------------------------
| 123,133,777 |       text text          |
| 555         |      text text text      |
| 444,133,777 |          text            |
| 999         | text text text text text |

When I try to use a select with IN()

SELECT *

FROM `My_Table` 
WHERE
VISITOR_ID IN(444,777)

I only get back one row and an error:

| 444,133,777 |

Warning: #1292 Truncated incorrect DOUBLE value ‘123,133,777’

I expected these two rows returned:

| 444,133,777 |

| 123,133,777 |

Yet:

If I use:

SELECT *
    FROM `My_Table` 
    WHERE
    VISITOR_ID IN(555,999)

I get back the 2 rows that have the single values in them:

| 555         |
| 999         |

I think I understand that the comma separated values are not being seen as separate values by my IN() clause, but are being seen like this ‘123,133,777’ instead – but how would I have these values read as separate values appropriate for my IN() search?

I was trying to change the column type, and also various means of splitting the values. Not sure what is the best way to go.
I also understand I can remedy this by using individual rows for the VISITOR_ID – which I am considering. It is just that I thought it made sense to have these multiple ID’s share a similar single text value entered by a user. It seemed 1 record would be better than many? not sure this is the way to go.

Tags:

2

Answers


  1. 0

    MySQL has the FIND_IN_SET() function that kind of does what you want, but this isn’t the intended use of it. It’s supposed to be for searching values of MySQL’s SET data type.

    Effectively, it searches a comma-separated string and returns the position in the list where your value was found.

    mysql> select find_in_set(777, '123,133,777') as loc;
+-----+
| loc |
+-----+
|   3 |
+-----+

    It has disadvantages: it’s only for searching for one value at a time, so to search multiple values you’d have to use multiple search terms:

    mysql> select find_in_set(777, '123,133,777') or find_in_set(444, '123,133,777') as loc;

    Also it spoils any chance of using an index. This means your queries will have terribly bad performance because they must do a table-scan.

    Also it’s finicky about spaces. If you have a comma-separated string with spaces like '123, 133, 777' it simply won’t work the way you expect, because it’ll only match if you search for a string that includes the spaces like ' 777'.

    You would be better off normalizing your data properly for a one-to-many relationship (as the comment above from GMB says) and storing one value per row. Then the search is much simpler to code, and it can take advantage of an index to optimize the query.

    This is one of many reasons why we discourage folks from storing comma-separated lists in strings.

    Login or Signup to reply.
  2. 0

    This is a way to do it by splitting the comma separated column into rows using json_table then apply your condition on those generated rows :

    with cte as (
  select t.VISITOR_ID, j.VISITOR
  from mytable t
  join json_table(
    CONCAT('[', t.VISITOR_ID, ']'),
    '$[*]' columns (VISITOR BIGINT(20) path '$')
) j
)
select distinct VISITOR_ID
from cte
where VISITOR IN(444,777);

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search