Your question is related with [this one] (Is storing a delimited list in a database column really that bad?).
I do think that your task is bound to be awkward and inefficient if you store the values in a comma-separated list. It would be far easier if you stored id values one per row. Then you could do this:
SELECT id, COUNT(*) AS count
FROM mytable
GROUP BY id
ORDER BY count DESC LIMIT 1;
But just telling you "you're doing it wrong, change everything" doesn't answer the question you asked.
The best I can come up with given your comma-separated list format is:
First create another table that has each id value, one per row.
create table idvalues ( id char(4) primary key );
insert into idvalues values ('id_11'),('id_12'),('id_13'),('id_14'),('id_15'),('id_18');
Then you can join this to your table and use FIND_IN_SET() to match each value to the lists:
SELECT idvalues.id
FROM idvalues JOIN yourtable
ON FIND_IN_SET(idvalues.id, yourtable.id_list);
This will produce a result set that you can count, as if you had stored the data in normal form:
SELECT id, COUNT(*) AS count
FROM (
SELECT idvalues.id
FROM idvalues JOIN yourtable
ON FIND_IN_SET(idvalues.id, yourtable.id_list)
) AS t
GROUP BY id
ORDER BY count DESC LIMIT 1;
But the caveat is that FIND_IN_SET() cannot be optimized with an index, so it's bound to do a Cartesian product. In other words, it will have to use that function as many times as the number of rows in idvalues
multiplied by the number of rows in yourtable
. It'll take a long time if these tables are large.
If you want this kind of query to be better optimized, then avoid using comma-separated lists.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…