php - 试图缩短其他loooong查询

这有点简单，但不是同时发生的。
我有一个用户输入名字的基本搜索框。
仅由first_name和last_name组成的名称。
表中有first_name，last_name，display_name列，其中firs_name和last_name显然是名字和姓氏，display_name是first_name和last_name的连接，或者是首选名称，因此William Smith的显示名称可能是Bill Smith。
怪癖：
名字和姓氏都可以是双姓。我的意思是我们的名字可能是

First Name: Anna Maria    Last Name: Smith
First Name: Anna          Last Name: Bo Johnson
First Name: Anna Maria    Last Name: Bo Johnson.

所以最好的情况下，一个名字应该是Anna Smith，但我有足够的最坏情况下Anna Maria Bo Johnson必须这样做。
当用户搜索Anna Maria Bo Johnson时，我需要运行一个不知道4个“单词”中哪一个是名字或姓氏的查询，不幸的是，double first double last name有84个组合，如Anna Bo Maria Johnson或Bo Maria Johnson Anna等……
现在我的问题是，我不希望mysql查询像这样长84行：（3个单词的名称示例）

SELECT * FROM tablename a
WHERE
    (a.first_name = '$search_term[0]' AND a.last_name = '$search_term[1]') OR
    (a.first_name = '$search_term[0]' AND a.last_name = '$search_term[2]') OR
    (a.first_name = '$search_term[0]' AND a.last_name = '$search_term[1] $search_term[2]') OR
    (a.first_name = '$search_term[0]' AND a.last_name = '$search_term[2] $search_term[1]') OR
    (a.first_name = '$search_term[1]' AND a.last_name = '$search_term[0]') OR
    (a.first_name = '$search_term[1]' AND a.last_name = '$search_term[1]') OR
    (a.first_name = '$search_term[1]' AND a.last_name = '$search_term[0] $search_term[2]') OR
    (a.first_name = '$search_term[1]' AND a.last_name = '$search_term[2] $search_term[0]') OR
    (a.first_name = '$search_term[2]' AND a.last_name = '$search_term[0]') OR
    (a.first_name = '$search_term[2]' AND a.last_name = '$search_term[1]') OR
    (a.first_name = '$search_term[2]' AND a.last_name = '$search_term[0] $search_term[1]') OR
    (a.first_name = '$search_term[2]' AND a.last_name = '$search_term[1] $search_term[0]') OR
    (a.first_name = '$search_term[0] $search_term[1]' AND a.last_name = '$search_term[2]') OR
    (a.first_name = '$search_term[1] $search_term[0]' AND a.last_name = '$search_term[2]') OR
    (a.first_name = '$search_term[0] $search_term[2]' AND a.last_name = '$search_term[1]') OR
    (a.first_name = '$search_term[2] $search_term[0]' AND a.last_name = '$search_term[1]') OR
    (a.first_name = '$search_term[1] $search_term[2]' AND a.last_name = '$search_term[0]') OR
    (a.first_name = '$search_term[2] $search_term[1]' AND a.last_name = '$search_term[0]') OR
    a.display_name = '$search_term[0] $search_term[1] $search_term[2]' OR
    a.display_name = '$search_term[1] $search_term[2] $search_term[0]' OR
    a.display_name = '$search_term[2] $search_term[1] $search_term[0]'
ORDER BY last_name, first_name ASC

那么，用什么样的方法来快速高效地写出这个查询呢？基本上，我需要比较（=）每个单词和随机单个单词或两个单词组合的组中的所有字段。
谢谢你的帮助！

最佳答案

使用IN语句：

SELECT *
FROM tablename a
WHERE concat_ws(' ',a.first_name,a.last_name) IN ('John Smith','Samantha Rose')

anna

php - 试图缩短其他loooong查询