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

Select by value in list in mySQL

I’ve got an older table where some IDs are stored in a varchar as comma separated list.

name street  some_ids
Pete Teststr 3,4,7,2
Tom  Teststr 1,4,7,3
Inga Teststr 3,7,2,5

I.e. need to find the entries containing 2 in column "some_ids" I tried:

WHERE 2 IN (some_ids) = 0 results

WHERE 3 IN (some_ids) = only rows where some_ids starts with 3 = 2 results

Another possibility is to use find_in_set() but there are lots of rows in the table and I want to use the index which isn’t by find_in_set().

Any ideas?

Tags:

2

Answers


  1. 0

    I think you could try to search with LIKE, and to simplify the searching you can add first , at the begining and end of the some_ids column.

    SELECT *
FROM Table
WHERE CONCAT(',', some_ids, ',') LIKE '%,2,%'
    Login or Signup to reply.
  2. 0

    Here’s a solution to make a multi-valued array on the comma-separated string, after forming it into JSON array syntax. The EXPLAIN shows that the query will use the index, if we search the exact same expression.

    mysql> create table mytable (name text, street text, some_ids text);

mysql> insert into mytable values ('Pete', 'Teststr', '3,4,7,2');

mysql> alter table mytable add key ((cast(concat('[',some_ids,']') as unsigned array)));

mysql> explain select * from mytable where 3 member of (concat('[',some_ids,']'));
+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | ref  | functional_index | functional_index | 9       | const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+

    The multi-valued index feature requires MySQL 8.0.17 or later. If you’re still using MySQL 5.x, and you want to optimize a search with an index, then you must normalize your table, and not store comma-separated lists.

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

Back To Top
Search