MySQL匹配LIKE或REGEX的数字列表

Question

I can't quite figure out the best way to search for matching numbers in my MySQL database.

Currently my query ends up as:

SELECT count(*) AS total FROM `videos_test` WHERE `channels` LIKE '%$cid%';

$cid can be any integer and the channels is in the format, "chan1,chan2,chan3,..." with any number of (even 0) channels set. Some valid strings would be:

1,12,5
5

8,1,2

The problem is that when $cid=2, I get back the rows that contain any 2, even as part of a larger number (12,32,25,...). It looks like I can do this with REGEX or MATCH/AGAINST, but I'm very confused about the syntax. Could someone point me in the right direction?

Answer 1

Using like you would fix this as:

SELECT count(*) AS total
FROM `videos_test`
WHERE concat(',', channels, ',') LIKE '%,$cid,%';

Another way to do this in MySQL is:

SELECT count(*) AS total
FROM `videos_test`
WHERE find_in_set($cid, channels) > 0;

As mentioned in the comment, you seem to be storing lists of things in a column. This is a bad idea. You should create an association/junction table with one row per video and channel combination.