mysql 字段以逗号隔开查询包含的的问题

解决的问题看截图,这个表里面的rid是逗号隔开的,需要查询rid的任何一个值在  (1,2,3)内

测试数据

CREATE TABLE `test` (
  `rid` varchar(1024) DEFAULT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1,2', '1');
INSERT INTO `test` VALUES ('2', '2');
INSERT INTO `test` VALUES ('3', '3');
INSERT INTO `test` VALUES ('4,5,6', '4');
INSERT INTO `test` VALUES ('2,3', '5');
INSERT INTO `test` VALUES ('1,2', '6');
INSERT INTO `test` VALUES ('3,4', '7');

查询sql

SELECT
	* 
FROM
	(
		SELECT
			t.rid rid,
			(
				LENGTH(t.rid) - LENGTH(REPLACE(t.rid, ',', '')) + 1
			) len
		FROM
			test t
	) m 
where 
case m.rid 
when 1
	then m.rid in(1,2,3)
when 2
	then (
				substring_index(m.rid,',',1) in (1,2,3)
				or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
				)
when 3
	then (
				substring_index(m.rid,',',1) in (1,2,3)
				or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
				or REPLACE(REPLACE(substring_index(m.rid,',',3),substring_index(m.rid,',',3),''),',','') in (1,2,3)
				)
when 4
	then (
				substring_index(m.rid,',',1) in (1,2,3)
				or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
				or REPLACE(REPLACE(substring_index(m.rid,',',3),substring_index(m.rid,',',3),''),',','') in (1,2,3)
				or REPLACE(REPLACE(substring_index(m.rid,',',4),substring_index(m.rid,',',4),''),',','') in (1,2,3)
				)
else false
end