MySQL 使用 CONCAT 实现多字段关键字模糊查询

问题分析与解决思路

在 Web 开发中，根据用户输入的关键字过滤列表数据是常见场景。当过滤维度单一（如仅按姓名）时，SQL 编写简单且页面交互清晰。然而，当需要同时支持用户名、昵称、手机号、地址等多个字段的混合搜索时，若采用传统方式，后端需编写大量的 if-else 判断来动态拼装 SQL 条件：and name like ? and phone like ?。这不仅导致后端代码冗长，也增加了出错概率。

优化思路是采用“宽表匹配”概念：将所有可能包含目标关键字的字段在数据库层面拼接成一个长字符串，然后只对这个拼接后的字符串进行一次 LIKE 查询。这样无论用户输入什么关键字，只需一个 SQL 语句即可完成全字段检索，极大降低了前后端的逻辑复杂度。

核心 SQL 编写

MySQL 提供了 CONCAT() 函数用于字符串连接。其基本语法为 CONCAT(str1, str2, ...)。我们将需要检索的字段传入该函数，并将搜索关键字包裹在通配符 % 中，构成最终的模糊查询条件。

第1步：构建基础 Concat 查询

假设有一张用户表 t_user，我们需要根据输入的关键字 x 在 a, b, c 三个字段中查找匹配记录。标准 SQL 格式如下：

SELECT a, b, c FROM table_name 
WHERE CONCAT(a, b, c) LIKE CONCAT('%', x, '%');

原理说明：CONCAT('%', x, '%') 用于生成带有左右通配符的模式字符串（如 %雪%）。CONCAT(a, b, c) 将三个字段的值首尾相连。如果关键字存在于任何一个字段中，拼接后的长字符串必然包含该关键字。

第2步：实战多字段检索 SQL

在实际项目中，可能需要检索更复杂的字段组合。以下示例展示了在 t_user 表中，对用户名、昵称、生日、账号、手机、地址等字段进行综合模糊搜索：

SELECT id, sex, user_name, nick_name, birthday, image, account, 
       password, phone, address, create_time, update_time, is_delete, status 
FROM t_user 
WHERE CONCAT(user_name, nick_name, birthday, account, phone, address, remark) 
      LIKE CONCAT('%', '雪', '%')
ORDER BY id DESC;

注意：查询结果集（SELECT）的字段列表无需与 CONCAT 中的字段完全一致。例如，你可以 SELECT 字段 A,B,C,D,E，但仅在 WHERE 中使用 CONCAT(A, E) 进行搜索，这是完全合法的。

后端代码实现

以下基于 Java Spring Boot + MyBatis Plus 环境，展示如何接收前端关键字并执行上述 SQL。

第3步：Controller 层接收请求

定义一个简单的 GET 接口，接收前端传来的 keyWord 参数，并调用 Service 层处理。

@GetMapping("/queryList")
public Result queryList(String keyWord) {
    // 直接传递关键字给 Service 层
    return Result.ok(userService.queryUserInfo(keyWord));
}

第4步：Service 层透传

Service 接口及实现类主要负责逻辑中转：

List<User> queryUserInfo(String keyWord);

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public List<User> queryUserInfo(String keyWord) {
        return userMapper.queryUserInfo(keyWord);
    }
}

第5步：Mapper/XML 动态 SQL

在 Mapper 接口中定义方法，并在 XML 中使用 <where> 和 <if> 标签处理可选的关键字搜索。这是关键部分，确保只有当 keyWord 不为空时才追加模糊查询条件。

// Mapper.java
List<User> queryUserInfo(@Param("keyWord") String keyWord);

<!-- UserMapper.xml -->
<select id="queryUserInfo" resultType="com.wyh.entity.User">
    SELECT
        id, sex, user_name, nick_name, birthday, image, account,
        password, phone, address, remark, create_time, update_time,
        is_delete, status
    FROM t_user
    <where>
        is_delete = 0
        <if test="keyWord != null and keyWord != ''">
            AND CONCAT(
                id, sex, user_name, nick_name, birthday, image, account, 
                password, phone, address, remark, create_time, 
                update_time, is_delete, status
            ) LIKE CONCAT( '%', #{keyWord}, '%')
        </if>
    </where>
    ORDER BY id DESC
</select>

接口测试验证

完成代码编写后，可通过 API 工具或直接浏览器测试接口行为。

第6步：带参数测试

传入有效关键字 ?keyWord=138（模拟手机号片段），期望返回所有手机号包含 "138" 或名字中包含 "138" 的用户记录。

第7步：无参数/空参测试

不传 keyWord 或传空字符串，期望返回全部未删除的用户列表，不受 CONCAT 条件干扰。

前端代码集成

前端使用 Vue.js + Element UI 构建简单的搜索框组件，将用户输入传递给后端接口。

第8步：Vue 模板与数据绑定

创建一个输入框和一个搜索按钮，使用 v-model 双向绑定关键字输入。

<template>
  <div>
    <el-input v-model="keyWord" placeholder="请输入关键字" clearable class="keyWordText" />
    <el-button type="primary" icon="el-icon-search" @click="queryUserList">搜索</el-button>
    <!-- 列表展示区域 -->
  </div>
</template>

<script>
import axios from 'axios';

export default {
  data() {
    return {
      keyWord: '',       // 输入框关键字
      userTableData: []  // 用户列表数据
    };
  },
  mounted() {
    // 页面加载时默认拉取所有数据
    this.queryUserList();
  },
  methods: {
    queryUserList() {
      axios.get('http://localhost:9090/user/queryList', {
        params: {
          keyWord: this.keyWord
        }
      }).then(res => {
        // 成功回调：更新列表数据
        this.userTableData = res.data.data;
      }).catch(error => {
        console.error(error);
      });
    }
  }
};
</script>

常见问题与排错

潜在风险：CONCAT 遇到 NULL 值导致结果为 NULL

在使用 CONCAT 函数时，有一个经典的 MySQL 特性需要注意：**如果 CONCAT 中任意一个参与拼接的字段值为 NULL，则整个拼接结果的值为 NULL**。
这意味着，即使关键字存在于其他非空字段中，只要拼接串里有一个空值，整体匹配就会失败，导致数据被意外漏查。

现象演示

假设表中有一条数据，user_name 为 "张三"，但 address 为 NULL。如果我们执行 CONCAT(name, address) LIKE '%三%'：

由于 CONCAT('张三', NULL) 返回 NULL，而 NULL LIKE '%三%' evaluates to UNKNOWN (treated as false)，这条数据不会被查出。

解决方案

1. 业务数据清洗：尽量保证参与拼接的常用搜索字段不为空。对于必填字段确保录入，非必填字段给予默认占位符。
2. SQL 优化（推荐）：在 SQL 中使用 COALESCE(field, '') 或 IFNULL(field, '') 将 NULL 转换为空字符串。例如：CONCAT(COALESCE(address, ''), name) LIKE ...。这样可以避免单个 NULL 破坏整个拼接结果。

总结

利用 MySQL 的 CONCAT 函数结合 LIKE 进行多字段模糊查询，是一种以空间换时间、以简洁逻辑替代复杂判断的有效手段。它特别适用于搜索引擎式的全局搜索场景，能显著减少后端 if-else 代码量。但在实施时，务必关注字段中的 NULL 值问题，并通过 COALESCE 等技术手段做好防御，以确保搜索结果的全覆盖。以上就是 MySQL 使用 CONCAT 函数实现多字段关键字模糊查询的详细内容，更多关于 MySQL 高级查询技巧的资料请关注本站其它相关文章！