SQLite中拆分逗号分隔列值并用于WHERE条件的查询方法

基础SQL查询语句 #

根据不同数据库的字符串函数特性，提供以下查询语句（需替换语句中的关联字段为实际表间关联的字段）：

MySQL/MariaDB #

SELECT t1.*, t2.*, t3.*
FROM TABLE1 t1
JOIN TABLE2 t2 ON t1.关联字段 = t2.关联字段
JOIN TABLE3 t3 ON t1.关联字段 = t3.关联字段
WHERE 
  -- 匹配首值为'1'的情况：单独的'1' 或 以'1,'开头
  (t1.EMP_Range = '1' OR LEFT(t1.EMP_Range, 2) = '1,')
  AND 
  -- 匹配末值为'3'的情况：单独的'3' 或 以',3'结尾
  (t1.EMP_Range = '3' OR RIGHT(t1.EMP_Range, 2) = ',3');

SQL Server #

SELECT t1.*, t2.*, t3.*
FROM TABLE1 t1
JOIN TABLE2 t2 ON t1.关联字段 = t2.关联字段
JOIN TABLE3 t3 ON t1.关联字段 = t3.关联字段
WHERE 
  (t1.EMP_Range = '1' OR LEFT(t1.EMP_Range, 2) = '1,')
  AND 
  (t1.EMP_Range = '3' OR RIGHT(t1.EMP_Range, 2) = ',3');

PostgreSQL #

SELECT t1.*, t2.*, t3.*
FROM TABLE1 t1
JOIN TABLE2 t2 ON t1.关联字段 = t2.关联字段
JOIN TABLE3 t3 ON t1.关联字段 = t3.关联字段
WHERE 
  (t1.EMP_Range = '1' OR SUBSTRING(t1.EMP_Range FROM 1 FOR 2) = '1,')
  AND 
  (t1.EMP_Range = '3' OR SUBSTRING(t1.EMP_Range FROM LENGTH(t1.EMP_Range)-1 FOR 2) = ',3');

最优实现方案 #

1. 范式化数据结构（长期最优） #

存储逗号分隔值违反数据库设计范式，会导致查询效率低下、数据维护困难。建议改造为关联表结构：

• 创建新表TABLE1_EMP_RANGE，包含TABLE1_ID（关联TABLE1的主键）、EMP_VALUE（原逗号分隔的单个值）、SORT_ORDER（记录原逗号分隔值的顺序）
• 将原TABLE1中EMP_Range的每个值拆分为独立行插入新表
• 改造后的查询可利用索引大幅提升效率：

SELECT t1.*, t2.*, t3.*
FROM TABLE1 t1
JOIN TABLE2 t2 ON t1.关联字段 = t2.关联字段
JOIN TABLE3 t3 ON t1.关联字段 = t3.关联字段
WHERE 
  -- 首值为'1'：没有比它排序更靠前的记录
  EXISTS (
    SELECT 1 FROM TABLE1_EMP_RANGE r1 
    WHERE r1.TABLE1_ID = t1.ID AND r1.EMP_VALUE = '1'
    AND NOT EXISTS (
      SELECT 1 FROM TABLE1_EMP_RANGE r2 
      WHERE r2.TABLE1_ID = t1.ID AND r2.SORT_ORDER < r1.SORT_ORDER
    )
  )
  AND 
  -- 末值为'3'：没有比它排序更靠后的记录
  EXISTS (
    SELECT 1 FROM TABLE1_EMP_RANGE r3 
    WHERE r3.TABLE1_ID = t1.ID AND r3.EMP_VALUE = '3'
    AND NOT EXISTS (
      SELECT 1 FROM TABLE1_EMP_RANGE r4 
      WHERE r4.TABLE1_ID = t1.ID AND r4.SORT_ORDER > r3.SORT_ORDER
    )
  );

2. 不改造结构时的优化方案（短期过渡） #

如果无法修改表结构，可通过以下方式优化查询性能：

• 添加计算列并创建索引：针对首值和末值生成计算列，再为计算列创建索引（以MySQL为例）

  -- 添加首值计算列
  ALTER TABLE TABLE1 ADD COLUMN EMP_FIRST VARCHAR(10) GENERATED ALWAYS AS (
    IF(LOCATE(',', EMP_Range) = 0, EMP_Range, LEFT(EMP_Range, LOCATE(',', EMP_Range)-1))
  ) STORED;
  -- 添加末值计算列
  ALTER TABLE TABLE1 ADD COLUMN EMP_LAST VARCHAR(10) GENERATED ALWAYS AS (
    IF(LOCATE(',', EMP_Range) = 0, EMP_Range, RIGHT(EMP_Range, LENGTH(EMP_Range)-LOCATE(',', REVERSE(EMP_Range))))
  ) STORED;
  -- 为计算列创建联合索引
  CREATE INDEX idx_emp_first_last ON TABLE1(EMP_FIRST, EMP_LAST);
  

  优化后的查询语句：

  SELECT t1.*, t2.*, t3.*
  FROM TABLE1 t1
  JOIN TABLE2 t2 ON t1.关联字段 = t2.关联字段
  JOIN TABLE3 t3 ON t1.关联字段 = t3.关联字段
  WHERE t1.EMP_FIRST = '1' AND t1.EMP_LAST = '3';
  

• 创建函数索引：部分数据库支持直接对字符串函数结果创建索引（以PostgreSQL为例）

  CREATE INDEX idx_emp_first ON TABLE1(LEFT(EMP_Range, 2));
  CREATE INDEX idx_emp_last ON TABLE1(RIGHT(EMP_Range, 2));
  

  原查询语句可直接利用这些索引，避免全表扫描。

内容的提问来源于stack exchange，提问作者Ashish Sharma