如何在MySQL中查询多列是否包含任意顺序的两个指定单词？

你的SQL语句核心问题出在LIKE条件的写法上，like ('%$searchwords[0]%' AND '%$searchwords[1]%')这种语法不符合MySQL规则，数据库无法正确解析。要判断单个列同时包含两个单词，需要对该列分别写两个LIKE条件，再用AND连接。

修正后的SQL语句 #

正确逻辑是任意一列同时包含两个指定单词，所以每个列的判断需满足两个LIKE条件，再将所有列的条件用OR组合：

$word1 = $searchwords[0];
$word2 = $searchwords[1];
$sql = "SELECT * FROM units 
WHERE 
    (name LIKE '%$word1%' AND name LIKE '%$word2%')
    OR (unit LIKE '%$word1%' AND unit LIKE '%$word2%')
    OR (address LIKE '%$word1%' AND address LIKE '%$word2%')
    OR (home LIKE '%$word1%' AND home LIKE '%$word2%')
    OR (cell LIKE '%$word1%' AND cell LIKE '%$word2%')
    OR (work LIKE '%$word1%' AND work LIKE '%$word2%')
    OR (office LIKE '%$word1%' AND office LIKE '%$word2%')
    OR (fax LIKE '%$word1%' AND fax LIKE '%$word2%')
    OR (email LIKE '%$word1%' AND email LIKE '%$word2%')
    OR (specialties LIKE '%$word1%' AND specialties LIKE '%$word2%')
    OR (agency LIKE '%$word1%' AND agency LIKE '%$word2%')
    OR (keywords LIKE '%$word1%' AND keywords LIKE '%$word2%')
    OR (misc1 LIKE '%$word1%' AND misc1 LIKE '%$word2%')
    OR (misc2 LIKE '%$word1%' AND misc2 LIKE '%$word2%')";

必须注意：SQL注入风险 #

你直接将变量拼入SQL的做法存在严重安全漏洞，攻击者可通过恶意输入篡改SQL逻辑、窃取或删除数据。务必使用预处理语句规避风险，以下是PDO实现的安全版本示例：

// 假设已初始化PDO连接$pdo
$word1 = "%{$searchwords[0]}%";
$word2 = "%{$searchwords[1]}%";

$sql = "SELECT * FROM units 
WHERE 
    (name LIKE ? AND name LIKE ?)
    OR (unit LIKE ? AND unit LIKE ?)
    OR (address LIKE ? AND address LIKE ?)
    OR (home LIKE ? AND home LIKE ?)
    OR (cell LIKE ? AND cell LIKE ?)
    OR (work LIKE ? AND work LIKE ?)
    OR (office LIKE ? AND office LIKE ?)
    OR (fax LIKE ? AND fax LIKE ?)
    OR (email LIKE ? AND email LIKE ?)
    OR (specialties LIKE ? AND specialties LIKE ?)
    OR (agency LIKE ? AND agency LIKE ?)
    OR (keywords LIKE ? AND keywords LIKE ?)
    OR (misc1 LIKE ? AND misc1 LIKE ?)
    OR (misc2 LIKE ? AND misc2 LIKE ?)";

$stmt = $pdo->prepare($sql);
// 绑定参数，共14列×2=28个参数
$stmt->execute([$word1, $word2, $word1, $word2, $word1, $word2, $word1, $word2, 
                $word1, $word2, $word1, $word2, $word1, $word2, $word1, $word2, 
                $word1, $word2, $word1, $word2, $word1, $word2, $word1, $word2, 
                $word1, $word2, $word1, $word2]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

优化建议 #

如果后续需要匹配更多单词或列，这种写法会过于冗长。可以考虑：

• 用动态代码生成SQL条件，减少重复代码
• 将所有搜索列合并为一个全文索引列，使用MySQL的MATCH AGAINST全文搜索功能，数据量较大时效率远高于多个LIKE

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