MySQL存储过程中省略中间名的姓名搜索失效问题求助

问题根源 #

原查询通过CONCAT_WS(' ', c.firstname, c.middlename, c.lastname)拼接客户全名，当搜索James Luke这类省略中间名的关键词时，拼接后的字符串是James Mark Luke，%James Luke%无法匹配该字符串（中间多了Mark），导致无法检索到目标记录。

解决方案 #

方案1：正则表达式匹配（推荐） #

将搜索词中的空格替换为.*，让正则表达式匹配中间包含任意内容的名字组合，直接覆盖带/不带中间名的场景：

OR CONCAT_WS(' ', c.firstname, c.middlename, c.lastname) REGEXP REPLACE(TRIM(p_search), ' ', '.*')

比如搜索James Luke会被转换为James.*Luke，可以匹配James Mark Luke、James Michael Luke等任意带中间名的全名。

方案2：枚举所有名字组合匹配 #

如果不想使用正则，可以列出所有可能的名字组合（名+姓、名+中间名+姓、中间名+姓），让搜索词匹配任意一个组合：

OR CONCAT_WS(' ', c.firstname, c.lastname) LIKE CONCAT('%', TRIM(p_search), '%')
OR CONCAT_WS(' ', c.firstname, c.middlename, c.lastname) LIKE CONCAT('%', TRIM(p_search), '%')
OR CONCAT_WS(' ', c.middlename, c.lastname) LIKE CONCAT('%', TRIM(p_search), '%')

方案3：多关键词全匹配（适合零散关键词搜索） #

如果用户可能输入多个独立关键词（如James、Luke分开输入），可以拆分搜索词，确保每个关键词都出现在全名中（需MySQL 8.0+支持STRING_SPLIT）：

OR (
  TRIM(p_search) != '' 
  AND NOT EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(TRIM(p_search), ' ') AS words 
    WHERE CONCAT_WS(' ', c.firstname, c.middlename, c.lastname) NOT LIKE CONCAT('%', words.value, '%')
  )
)

修改后的完整查询示例（采用方案1） #

SELECT c.Id,
    c.UserId,
    c.FirstName,
    c.MiddleName,
    c.LastName,
    c.Gender,
    c.DateOfBirth,
    c.EmailAddress,
    c.MobileNumber,
    c.Status,
    c.CreatedOn,
    c.SignUpType,
    c.HolderTier,
    c.WalletNumber
FROM User c
WHERE c.isdeleted = 0
  AND (
    p_search IS NULL
    OR CONCAT_WS(' ', c.firstname, c.middlename, c.lastname) REGEXP REPLACE(TRIM(p_search), ' ', '.*')
    OR c.mobilenumber LIKE CONCAT('%', TRIM(COALESCE(p_search, '')), '%')
    OR c.bvn LIKE CONCAT('%', TRIM(COALESCE(p_search, '')), '%')
    OR c.emailaddress LIKE CONCAT('%', TRIM(COALESCE(p_search, '')), '%')
  )
  AND (p_careofficer IS NULL OR c.careofficer = p_careofficer)
  AND (p_gender IS NULL OR c.gender = p_gender)
  AND (p_status IS NULL OR c.status = p_status)
  AND (p_holdertier IS NULL OR c.holdertier = p_holdertier)
  AND (p_signuptype IS NULL OR c.signuptype = p_signuptype)
  AND (p_datefrom IS NULL OR c.createdon BETWEEN p_datefrom AND p_dateto)
ORDER BY c.CreatedOn DESC
LIMIT p_pagesize OFFSET page_offset;
END

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