0

I have a table in MySQL, like below:

------------------------
    id    |    codes
------------------------
     1    |   1,2,3,4
------------------------
     2    |    1,4
------------------------
     3    |    1,2,4
------------------------
     4    |    1
------------------------
  • Example-1: select all case that codes column contains 1 and 4, then the result is below (because the id=4 row that codes column not contain 4): 
------------------------
    id    |    codes
------------------------
     1    |   1,2,3,4
------------------------
     2    |    1,4
------------------------
     3    |    1,2,4
------------------------
  • Example-2: select all case that codes column contains 2 or 3, then the result is below (because the id=4, id=2 rows that codes column not contain any value in 2,3): 
------------------------
    id    |    codes
------------------------
     1    |   1,2,3,4
------------------------
     3    |    1,2,4
------------------------

Question: But I don't how to write sql to do that if codes stores lots of value such as 1,2,3,..20,21.

I tried this:

SELECT SQL_NO_CACHE * FROM table WHERE 
codes like '%1%4%' 
and codes REGEXP'[2|3]';

Is there any better way to do that?

jkdev
  • 11,360
  • 15
  • 54
  • 77
Q10Viking
  • 982
  • 8
  • 9
  • 1
    try `FIND_IN_SET` – Devsi Odedra Sep 20 '19 at 11:44
  • 4
    Fix your data model! Do not store multiple values in a string column! Do not store numbers as string! Declare foreign key relationships! – Gordon Linoff Sep 20 '19 at 11:45
  • @ Devsi Odedra : if use `FIND_IN_SET` ,then will be the below case: ```sql SELECT SQL_NO_CACHE * FROM table WHERE codes like '%1%4%' and (codes find_in_set(2,codes) or find_in_set(3,codes)) ``` if code have more values then the sql will be too long – Q10Viking Sep 20 '19 at 11:50
  • @ Gordon Linoff: yes i agree with you. But Is there adivice for data model.If the I have Ten thousand rows,and the code has 20 more value? – Q10Viking Sep 20 '19 at 11:56

0 Answers0