如何通过MySQL/PHP实现带通配符的相似数字数据库搜索？

针对你遇到的带X/Y/Z通配符的格式匹配场景（如123XX、12XYY对应基准值12344，且X/Y有取值限制），之前用嵌套循环生成大量LIKE条件导致查询缓慢的问题，以下是几个实用优化方案：

1. 用正则表达式(REGEXP)替代批量LIKE条件 #

直接将格式规则转换为MySQL正则表达式，单次扫描过滤符合格式的记录，再附加取值限制条件，避免生成冗余的LIKE语句。

以格式12XYY为例（X/Y取值为3-9、0，且X≠Y、X≠1/2、Y≠1/2）：

SELECT target_column 
FROM your_table 
WHERE 
  -- 先匹配5位格式：前两位12，第三位是允许的X值，第四五位是相同的Y值
  target_column REGEXP '^12[34567890][34567890]{2}$'
  -- 附加X≠Y的限制
  AND SUBSTRING(target_column, 3, 1) != SUBSTRING(target_column, 4, 1);

如果目标字段长度固定，可先加LENGTH(target_column) = 5条件缩小扫描范围，进一步提速。

2. 拆分字段+索引优化 #

若匹配的字符串长度固定（如都是5位），可将原字段拆分为单个字符的列（如c1、c2、c3、c4、c5），并为这些列单独创建索引。查询时直接用精准的等值/IN条件，利用索引快速定位数据。

对应12XYY格式的查询语句：

SELECT target_column 
FROM your_table 
WHERE 
  c1 = '1' 
  AND c2 = '2' 
  AND c3 IN ('3','4','5','6','7','8','9','0')
  AND c4 IN ('3','4','5','6','7','8','9','0')
  AND c5 = c4
  AND c3 != c4;

PHP端只需将输入的格式字符串拆解为对应列的条件即可，无需嵌套循环生成大量规则。

3. 自定义MySQL函数处理复杂规则 #

针对更复杂的格式匹配逻辑（如X/Y/Z的关联限制），可以编写自定义MySQL函数封装匹配逻辑，PHP端直接调用函数过滤数据。

示例自定义函数：

DELIMITER //
CREATE FUNCTION match_format(field_val VARCHAR(20), format_str VARCHAR(20)) 
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE f_char, fmt_char CHAR(1);
  DECLARE x_val, y_val, z_val CHAR(1);
  
  WHILE i <= LENGTH(format_str) DO
    SET f_char = SUBSTRING(field_val, i, 1);
    SET fmt_char = SUBSTRING(format_str, i, 1);
    
    CASE fmt_char
      WHEN 'X' THEN
        IF x_val IS NULL THEN
          SET x_val = f_char;
          IF f_char IN ('1','2') THEN RETURN FALSE;
          END IF;
        ELSEIF f_char != x_val THEN RETURN FALSE;
        END IF;
      WHEN 'Y' THEN
        IF y_val IS NULL THEN
          SET y_val = f_char;
          IF y_val IN ('1','2') OR y_val = x_val THEN RETURN FALSE;
          END IF;
        ELSEIF f_char != y_val THEN RETURN FALSE;
        END IF;
      WHEN 'Z' THEN
        IF z_val IS NULL THEN SET z_val = f_char;
        ELSEIF f_char != z_val THEN RETURN FALSE;
        END IF;
      ELSE
        IF f_char != fmt_char THEN RETURN FALSE;
        END IF;
    END CASE;
    
    SET i = i + 1;
  END WHILE;
  
  RETURN TRUE;
END //
DELIMITER ;

调用函数的查询语句：

SELECT target_column 
FROM your_table 
WHERE 
  LENGTH(target_column) = LENGTH('12XYY') -- 先过滤长度匹配的记录
  AND match_format(target_column, '12XYY') = TRUE;

4. 精简PHP端SQL生成逻辑 #

如果必须使用LIKE，避免不必要的嵌套循环，直接生成精简的IN条件而非大量OR连接的LIKE语句：

$xVals = ['3','4','5','6','7','8','9','0'];
$conditions = [];
foreach ($xVals as $x) {
    foreach ($xVals as $y) {
        if ($x !== $y) {
            $conditions[] = "target_column = '12{$x}{$y}{$y}'";
        }
    }
}
$sql = "SELECT target_column FROM your_table WHERE " . implode(' OR ', $conditions);

这种方式比嵌套循环生成重复LIKE高效，但仍不如前三种方案，仅适用于数据量较小的场景。

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