如何用正则处理SQL中同列多IN条件的合并或改写？

方案一：合并同列IN的取值（去重后合并） #

通过解析WHERE子句中的所有条件，按列分组收集IN的取值，去重后重新构建SQL条件，实现同列IN值的合并。

代码实现（PHP） #

function mergeSameColumnINConditions($sql) {
    // 提取WHERE子句部分
    preg_match('/WHERE (.*)$/i', $sql, $matches);
    if (!isset($matches[1])) {
        return $sql;
    }
    $conditions = explode('AND', $matches[1]);
    $columnValues = [];
    
    foreach ($conditions as $cond) {
        $cond = trim($cond);
        // 匹配column IN (values)格式
        if (preg_match('/^(\w+)\s+IN\s+\((.*?)\)$/i', $cond, $condMatches)) {
            $column = strtolower($condMatches[1]);
            $values = array_map('trim', explode(',', $condMatches[2]));
            if (!isset($columnValues[$column])) {
                $columnValues[$column] = [];
            }
            $columnValues[$column] = array_merge($columnValues[$column], $values);
        } else {
            // 非IN条件直接保留
            $columnValues['_other'][] = $cond;
        }
    }
    
    // 构建新的条件
    $newConditions = [];
    foreach ($columnValues as $col => $vals) {
        if ($col === '_other') {
            $newConditions = array_merge($newConditions, $vals);
            continue;
        }
        // 去重并重新拼接
        $uniqueVals = array_unique($vals);
        $newConditions[] = "$col IN (" . implode(', ', $uniqueVals) . ")";
    }
    
    // 替换回原SQL
    return preg_replace('/WHERE (.*)$/i', 'WHERE ' . implode(' AND ', $newConditions), $sql);
}

// 使用示例
$sql = 'SELECT * FROM table WHERE column1 IN (1,2,3,4) AND column2 IN (1,2,3) AND column1 IN (4,5,6)';
echo mergeSameColumnINConditions($sql);
// 输出：SELECT * FROM table WHERE column1 IN (1, 2, 3, 4, 5, 6) AND column2 IN (1, 2, 3)

方案二：将同列IN条件用OR包裹 #

按列分组收集IN条件，对同一列的多个IN条件用OR连接并包裹在括号中，其他条件保持AND连接。

代码实现（PHP） #

function wrapSameColumnINWithOR($sql) {
    preg_match('/WHERE (.*)$/i', $sql, $matches);
    if (!isset($matches[1])) {
        return $sql;
    }
    $conditions = explode('AND', $matches[1]);
    $columnConditions = [];
    
    foreach ($conditions as $cond) {
        $cond = trim($cond);
        if (preg_match('/^(\w+)\s+IN\s+\((.*?)\)$/i', $cond, $condMatches)) {
            $column = strtolower($condMatches[1]);
            if (!isset($columnConditions[$column])) {
                $columnConditions[$column] = [];
            }
            $columnConditions[$column][] = $cond;
        } else {
            $columnConditions['_other'][] = $cond;
        }
    }
    
    $newConditions = [];
    foreach ($columnConditions as $col => $conds) {
        if ($col === '_other') {
            $newConditions = array_merge($newConditions, $conds);
            continue;
        }
        if (count($conds) > 1) {
            $newConditions[] = '(' . implode(' OR ', $conds) . ')';
        } else {
            $newConditions[] = $conds[0];
        }
    }
    
    return preg_replace('/WHERE (.*)$/i', 'WHERE ' . implode(' AND ', $newConditions), $sql);
}

// 使用示例
$sql = 'SELECT * FROM table WHERE column1 IN (1,2,3,4) AND column2 IN (1,2,3) AND column1 IN (4,5,6)';
echo wrapSameColumnINWithOR($sql);
// 输出：SELECT * FROM table WHERE (column1 IN (1,2,3,4) OR column1 IN (4,5,6)) AND column2 IN (1,2,3)

方案优势 #

• 不再局限于单次处理两组匹配，而是遍历所有条件进行分组，支持同一列出现任意次数的IN条件。
• 兼容非IN格式的条件，直接保留原逻辑。

内容的提问来源于stack exchange，提问作者Victor Canela