Find ID in a column of delimiter-separated IDs in MySQL

5

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 chat table there is a column named participantes where I enter the participants of the group in this way:

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

Where each numeral to the comma corresponds to the user ID, ie 01 -> I 02- > you or anyone else and so on ...

The question is, how do I search for all results that contain my id in the participantes column? How to read each comma number by comma until I find mine across the table? Is there a standard MySQL way to do this?

    
asked by anonymous 19.06.2014 / 23:51

1 answer

14

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 several SQL dialects is to find it this way:

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

However, if you have a lot of data the performance will not be the most surprising 1 . Of course in PHP you will put a variable in place of the 09 of the example (be careful to avoid SQL Injection).

Remember to hit your items consistently, because in your example you have one, two and three digit things, and some with zero before and others not, which is a sign of something slightly strange 2 if in real case you really are that way.


It would be better ...

... use an auxiliary table relating the participants to the 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 you just have to study how to JOIN to find the data . In addition to better organizing your data, you'll benefit from using indexes to make your SELECT more efficient, and you'll probably be able to take advantage of this table for other parts of your system, which now depend on the comma-separated field. p>

1. horrible, in the case of like , a little better in find_in_set

2. zica

    
20.06.2014 / 00:45