Find ID in a delimiter-separated ID column in Mysql

Question

I use a table called chat and inside it has a whole structure for my chat script and I’m trying to create chat in groups, so far so good. However, within the table chat has a column called participantes where I insert the group participants in this way:

01,02,03,04,05,6,0,2,522....

Where each numeral up to the comma corresponds to the user ID, ie 01 -> I 02-> you or any other and so it goes...

The point is, how do I search for any results that contain my id in the column participantes? How to read each comma number per comma until I find mine across the table? There is a default Mysql form for this?

Answer 1

Only with SELECT

As remembered by @bfavaretto, Mysql has a specific function for your need:

SELECT * FROM chat WHERE FIND_IN_SET( '09', participantes );

the most general solution for various SQL dialects is you locate this way:

SELECT * FROM chat WHERE CONCAT( ",", participantes, "," ) LIKE '%,09,%';

however, if you have a lot of data the performance won’t be the most surprising ¹. Of course in PHP you will put a variable in place of 09 example (carefully to avoid SQL Injection).

Remember to match your items so that they are consistent, because in your example there are things with one, two and three digits, and some with previous zero and others not, which is a sign of something slightly strange ² if in the real case it really is that way.

Better would be...

...use an auxiliary table relating participants to rooms, so you would have something in this model:

 sala | participante
------+---------------
    1 |  12     
    1 |   7     
    1 | 342     
    1 |   1
    2 |  12
    2 |  28
    3 |   1
...

So just give a study on how to make a JOIN to locate the data. In addition to better organizing your data, you will be able to benefit from the use of indexes to make your SELECT, and you will probably be able to take advantage of this table for other parts of your system, which today depend on the comma-separated field.

_{1. horrible in the case of like, a little better at find_in_set}

_{2. Zica}