MySQL查询指定字符串是否在以逗号分割的字段中（FIND_IN_SET使用方法）

函数概述

FIND_IN_SET() 是 MySQL 中用于在逗号分隔的字符串列表中查找指定字符串位置的函数。

基本语法：

FIND_IN_SET(search_string, comma_separated_list)

返回值：

• 如果找到：返回字符串在列表中的位置（从 1 开始计数）
• 如果未找到：返回 0
• 如果任一参数为 NULL：返回 NULL

核心特性

使用场景示例

1. 基本查找

SELECT FIND_IN_SET('apple', 'banana,apple,orange'); -- 返回 2
SELECT FIND_IN_SET('Apple', 'banana,apple,orange'); -- 返回 2 (不区分大小写)
SELECT FIND_IN_SET('grape', 'banana,apple,orange'); -- 返回 0

2. 在 WHERE 子句中使用

-- 查找包含特定标签的文章
SELECT * 
FROM articles 
WHERE FIND_IN_SET('technology', tags) > 0;

-- 查找管理员用户
SELECT *
FROM users
WHERE FIND_IN_SET('admin', roles) > 0;

3. 在 ORDER BY 子句中使用

-- 按指定角色顺序排序
SELECT *
FROM employees
ORDER BY FIND_IN_SET(role, 'manager,developer,designer,intern');

4. 在 UPDATE 语句中使用

-- 为包含特定权限的用户升级
UPDATE users
SET membership_level = 'premium'
WHERE FIND_IN_SET('advanced_access', permissions) > 0;

5. 处理包含逗号的值

-- 使用特殊分隔符处理包含逗号的值
SELECT FIND_IN_SET('New York', REPLACE('London,"New York",Paris', ',', '|'));

总结

FIND_IN_SET() 函数在 MySQL 中的适用场景：

• 小数据量的逗号分隔值查询
• 简单的存在性检查
• 不需要高性能的临时查询

对于生产环境：

1. 优先使用规范化表结构 - 最佳性能和可扩展性
2. 考虑 JSON 类型 - MySQL 5.7+ 的现代解决方案
3. 添加函数索引 - MySQL 8.0+ 的性能优化
4. 避免在大数据量使用 - 会导致全表扫描

真实项目使用的sql语句

<select id="getLibraryBaseInfoList" resultType="com.dtranx.tools.corpora.abilityapi.resp.LibraryInfoDetailRespVo">
        SELECT <include refid="total_columns"/>
        FROM <include refid="ability_table"/>
        LEFT JOIN <include refid="business_table"/> ON a.id = b.db_id
        <where>
            <if test="baseSearchReqVo.infoType != null">
                a.info_type = #{baseSearchReqVo.infoType}
            </if>
            <if test="baseSearchReqVo.name != '' and baseSearchReqVo.name != null">
                AND a.name like concat ('%',#{baseSearchReqVo.name},'%')
            </if>
            <if test="baseSearchReqVo.unions != null and baseSearchReqVo.unions.size > 0">
                AND (a.source, a.target, b.field_id) IN
                <foreach collection="baseSearchReqVo.unions" item="union"  separator="," open="(" close=")">
                    (#{union.source}, #{union.target}, #{union.fieldId})
                </foreach>
            </if>
            <if test="baseSearchReqVo.source != null and baseSearchReqVo.source != '' and (baseSearchReqVo.unions == null or baseSearchReqVo.unions.size == 0)">
                AND a.source = #{baseSearchReqVo.source}
            </if>
            <if test="baseSearchReqVo.target != null and baseSearchReqVo.target != '' and (baseSearchReqVo.unions == null or baseSearchReqVo.unions.size == 0)">
                AND a.target = #{baseSearchReqVo.target}
            </if>
            <if test="baseSearchReqVo.markId != null and baseSearchReqVo.markId != ''">
                AND a.id
                <if test="baseSearchReqVo.contain == null or baseSearchReqVo.contain == true">
                    IN
                </if>
                <if test="baseSearchReqVo.contain != null and baseSearchReqVo.contain == false">
                    NOT IN
                </if>
                (
                    select DISTINCT c.db_id
                    from <include refid="relation_table"/>
                    WHERE c.mark_id = #{baseSearchReqVo.markId}
                )
            </if>
            <if test="baseSearchReqVo.open != null and baseSearchReqVo.open == true">
                AND a.open = 1
            </if>
            <if test="baseSearchReqVo.open != null and baseSearchReqVo.open == false">
                AND a.open = 0
            </if>
            <if test="baseSearchReqVo.allowModified != null and baseSearchReqVo.allowModified == true">
                AND a.allow_modified = 1
            </if>
            <if test="baseSearchReqVo.allowModified != null and baseSearchReqVo.allowModified == false">
                AND a.allow_modified = 0
            </if>
            <if test="baseSearchReqVo.ownerUserCode != null and baseSearchReqVo.ownerUserCode != ''">
                AND a.owner = #{baseSearchReqVo.ownerUserCode}
            </if>
            <if test="baseSearchReqVo.owner != null and baseSearchReqVo.owner != ''">
                AND a.owner = #{baseSearchReqVo.owner}
            </if>
            <if test="baseSearchReqVo.delegateUserCode != null and baseSearchReqVo.delegateUserCode != ''">
                AND FIND_IN_SET(#{baseSearchReqVo.delegateUserCode}, a.delegate) > 0
            </if>
            <if test="baseSearchReqVo.delegate != null and baseSearchReqVo.delegate != ''">
                AND FIND_IN_SET(#{baseSearchReqVo.delegate}, a.delegate)
                <if test="baseSearchReqVo.delegateContain == null or baseSearchReqVo.delegateContain == true">
                    >
                </if>
                <if test="baseSearchReqVo.delegateContain != null and baseSearchReqVo.delegateContain == false ">
                    =
                </if>
                0
            </if>
            <if test="baseSearchReqVo.fieldId != null and baseSearchReqVo.fieldId != '' and (baseSearchReqVo.unions == null or baseSearchReqVo.unions.size == 0)">
                AND b.field_id = #{baseSearchReqVo.fieldId}
            </if>
            <if test="baseSearchReqVo.customId != null and baseSearchReqVo.customId != ''">
                AND b.custom_id = #{baseSearchReqVo.customId}
            </if>
            <if test="baseSearchReqVo.level != null">
                AND b.level = #{baseSearchReqVo.level}
            </if>
            <if test="baseSearchReqVo.businessType != null">
                AND b.business_type = #{baseSearchReqVo.businessType}
            </if>
            <if test="baseSearchReqVo.orderBy != null and baseSearchReqVo.orderBy != '' and baseSearchReqVo.sort != null and baseSearchReqVo.sort != ''">
                ORDER BY ${baseSearchReqVo.orderBy} ${baseSearchReqVo.sort}
            </if>
        </where>
    </select>