当前位置:   article > 正文

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

mysql字段逗号隔开的隐患

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

测试数据

  1. CREATE TABLE `test` (
  2. `rid` varchar(1024) DEFAULT NULL,
  3. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  4. UNIQUE KEY `id` (`id`)
  5. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
  6. -- ----------------------------
  7. -- Records of test
  8. -- ----------------------------
  9. INSERT INTO `test` VALUES ('1,2', '1');
  10. INSERT INTO `test` VALUES ('2', '2');
  11. INSERT INTO `test` VALUES ('3', '3');
  12. INSERT INTO `test` VALUES ('4,5,6', '4');
  13. INSERT INTO `test` VALUES ('2,3', '5');
  14. INSERT INTO `test` VALUES ('1,2', '6');
  15. INSERT INTO `test` VALUES ('3,4', '7');

查询sql

  1. SELECT
  2. *
  3. FROM
  4. (
  5. SELECT
  6. t.rid rid,
  7. (
  8. LENGTH(t.rid) - LENGTH(REPLACE(t.rid, ',', '')) + 1
  9. ) len
  10. FROM
  11. test t
  12. ) m
  13. where
  14. case m.rid
  15. when 1
  16. then m.rid in(1,2,3)
  17. when 2
  18. then (
  19. substring_index(m.rid,',',1) in (1,2,3)
  20. or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
  21. )
  22. when 3
  23. then (
  24. substring_index(m.rid,',',1) in (1,2,3)
  25. or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
  26. or REPLACE(REPLACE(substring_index(m.rid,',',3),substring_index(m.rid,',',3),''),',','') in (1,2,3)
  27. )
  28. when 4
  29. then (
  30. substring_index(m.rid,',',1) in (1,2,3)
  31. or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
  32. or REPLACE(REPLACE(substring_index(m.rid,',',3),substring_index(m.rid,',',3),''),',','') in (1,2,3)
  33. or REPLACE(REPLACE(substring_index(m.rid,',',4),substring_index(m.rid,',',4),''),',','') in (1,2,3)
  34. )
  35. else false
  36. end

 

声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号