不同数据库中对以逗号分割的字符串筛选操作处理方案总结

不同数据库中对以逗号分割的字符串筛选操作处理方案总结

一、需求描述

1. 数据库中存在某个字段存放以逗号分割的字符串类型数据，如"x,y,z,a,b,c"
2. 前端同样传入以逗号分割的字符串作为筛选条件，如"x,y"
3. 需要实现各类筛选，如等于、不等于、全包含、包含部分、完全不包含等，且不考虑具体顺序，如"x,y"和"y,x"可以视为"相等"

二、实现方案

起初的考虑是用like %字段%组合实现，或者使用不同数据库的正则匹配函数，如"字段1|字段2"，但是都不能很好的实现"不考虑具体顺序的逻辑"，在遇到多个字段时，无论时like模糊匹配或者是正则匹配都会造成漏选或多选的问题。 比较好的一个方案是在数据库中手动实现按逗号分割字符串的自定义函数，然后再依次实现比较逻辑，但是在某些不支持扩展自定义函数的第三方需求下，这个方案也无法实现。 最终选取方案是使用数据库中已存在的特定函数组合实现，但缺点是对于不同数据库需要分别处理，缺乏一定的通用性。此处仅列举全包含与不包含的示例，其余情况类似，通过特定函数与and、or组合实现。

• MySQL数据库实现方案(FIND_IN_SET函数)
  1. 全包含：select * from table where FIND_IN_SET('x', 列名) > 0 and FIND_IN_SET('y', 列名) > 0
  2. 不包含：select * from table where FIND_IN_SET('x', 列名) = 0 and FIND_IN_SET('y', 列名) = 0
• PostgreSQL数据库实现方案(STRING_TO_ARRAY函数)
  1. 全包含：select * from table where 'x' = ANY(STRING_TO_ARRAY(列名, ',') and 'y' = ANY(STRING_TO_ARRAY(列名, ',')
  2. 不包含：select * from table where 'x' <> ALL(STRING_TO_ARRAY(列名, ',') and 'y' <> ALL(STRING_TO_ARRAY(列名, ',')
• Oracle数据库实现方案(REGEXP_SUBSTR函数和子查询组合实现)
  1. 全包含：select * from table where 'x' IN (select REGEXP_SUBSTR(列名, '[^,]+', 1, ROWNUM) from dual connect by ROWNUM <= (LENGTH(列名) - LENGTH(REPLACE(列名, ',', '')) + 1)) and 'y' IN (select REGEXP_SUBSTR(列名, '[^,]+', 1, ROWNUM) from dual connect by ROWNUM <= (LENGTH(列名) - LENGTH(REPLACE(列名, ',', '')) + 1))
  2. 不包含：select * from table where 'x' NOT IN (select REGEXP_SUBSTR(列名, '[^,]+', 1, ROWNUM) from dual connect by ROWNUM <= (LENGTH(列名) - LENGTH(REPLACE(列名, ',', '')) + 1)) and 'y' NOT IN (select REGEXP_SUBSTR(列名, '[^,]+', 1, ROWNUM) from dual connect by ROWNUM <= (LENGTH(列名) - LENGTH(REPLACE(列名, ',', '')) + 1))

三、总结

无论是哪种数据库的实现方式，最终都是通过按逗号分割字符串列，并转为数组或集合类似的形式，再判断单项参数是否在这个集合之中，最后使用AND或OR组合实现筛选逻辑。