面试重要程度：⭐⭐⭐⭐⭐

常见提问方式：动态SQL标签使用、批量操作优化、SQL性能调优

预计阅读时间：30分钟

开场白

兄弟，动态SQL绝对是MyBatis的核心特性！面试官特别喜欢考这个，因为它直接体现你对复杂业务场景的处理能力。特别是批量操作优化，这在实际项目中太重要了！

今天我们就把动态SQL的各种标签用法和性能优化技巧全部搞定，让你在面试中展现出对数据访问层的深度理解。

🔄 动态SQL标签详解

核心标签全解析

面试必问：

面试官："MyBatis的动态SQL有哪些标签？如何处理复杂的查询条件？"

1. if标签 - 条件判断

<mapper namespace="com.example.mapper.UserMapper">
    
    <!-- 基础条件查询 -->
    <select id="selectByCondition" resultType="User">
        SELECT * FROM user
        WHERE 1=1
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="createTimeStart != null">
            AND create_time >= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND create_time <= #{createTimeEnd}
        </if>
    </select>
    
    <!-- 复杂条件判断 -->
    <select id="selectByComplexCondition" resultType="User">
        SELECT * FROM user
        WHERE 1=1
        <if test="condition != null">
            <if test="condition.name != null and condition.name.trim() != ''">
                AND name LIKE CONCAT('%', #{condition.name}, '%')
            </if>
            <if test="condition.ageRange != null">
                <if test="condition.ageRange.min != null">
                    AND age >= #{condition.ageRange.min}
                </if>
                <if test="condition.ageRange.max != null">
                    AND age <= #{condition.ageRange.max}
                </if>
            </if>
            <if test="condition.statusList != null and condition.statusList.size() > 0">
                AND status IN
                <foreach collection="condition.statusList" item="status" open="(" separator="," close=")">
                    #{status}
                </foreach>
            </if>
        </if>
    </select>
    
</mapper>

2. where标签 - 智能WHERE处理

<!-- where标签自动处理AND/OR -->
<select id="selectByConditionWithWhere" resultType="User">
    SELECT * FROM user
    <where>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
    </where>
    ORDER BY create_time DESC
</select>

<!-- 对比：不使用where标签的问题 -->
<select id="badExample" resultType="User">
    SELECT * FROM user
    WHERE 
    <if test="name != null and name != ''">
        name LIKE CONCAT('%', #{name}, '%')
    </if>
    <if test="age != null">
        AND age = #{age}  <!-- 如果name为空，这里会出现语法错误 -->
    </if>
</select>

3. choose/when/otherwise - 多分支选择

<!-- 类似Java的switch-case -->
<select id="selectByPriority" resultType="User">
    SELECT * FROM user
    <where>
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="email != null and email != ''">
                email = #{email}
            </when>
            <when test="phone != null and phone != ''">
                phone = #{phone}
            </when>
            <when test="username != null and username != ''">
                username = #{username}
            </when>
            <otherwise>
                status = 1  <!-- 默认查询激活用户 -->
            </otherwise>
        </choose>
    </where>
</select>

<!-- 复杂业务场景应用 -->
<select id="selectByUserType" resultType="User">
    SELECT 
        <choose>
            <when test="userType == 'VIP'">
                id, name, email, vip_level, vip_expire_time
            </when>
            <when test="userType == 'ADMIN'">
                id, name, email, role, permissions
            </when>
            <otherwise>
                id, name, email
            </otherwise>
        </choose>
    FROM user
    <where>
        <choose>
            <when test="userType == 'VIP'">
                vip_level > 0 AND vip_expire_time > NOW()
            </when>
            <when test="userType == 'ADMIN'">
                role IN ('ADMIN', 'SUPER_ADMIN')
            </when>
            <otherwise>
                status = 1
            </otherwise>
        </choose>
    </where>
</select>

4. foreach标签 - 循环处理

<!-- IN查询 -->
<select id="selectByIds" resultType="User">
    SELECT * FROM user
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 批量条件查询 -->
<select id="selectByMultipleConditions" resultType="User">
    SELECT * FROM user
    WHERE 
    <foreach collection="conditions" item="condition" separator=" OR ">
        (name = #{condition.name} AND age = #{condition.age})
    </foreach>
</select>

<!-- 动态表名查询（注意SQL注入风险） -->
<select id="selectFromMultipleTables" resultType="Map">
    <foreach collection="tableNames" item="tableName" separator=" UNION ALL ">
        SELECT '${tableName}' as table_name, COUNT(*) as count FROM ${tableName}
    </foreach>
</select>

<!-- 批量插入VALUES -->
<insert id="batchInsert" parameterType="list">
    INSERT INTO user (name, age, email, create_time)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.name}, #{user.age}, #{user.email}, NOW())
    </foreach>
</insert>

5. set标签 - 动态更新

<!-- 动态更新字段 -->
<update id="updateSelective">
    UPDATE user
    <set>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="age != null">
            age = #{age},
        </if>
        <if test="email != null and email != ''">
            email = #{email},
        </if>
        <if test="phone != null and phone != ''">
            phone = #{phone},
        </if>
        <if test="status != null">
            status = #{status},
        </if>
        update_time = NOW()  <!-- 总是更新修改时间 -->
    </set>
    WHERE id = #{id}
</update>

<!-- 批量更新不同字段 -->
<update id="batchUpdateSelective">
    <foreach collection="users" item="user" separator=";">
        UPDATE user
        <set>
            <if test="user.name != null and user.name != ''">
                name = #{user.name},
            </if>
            <if test="user.age != null">
                age = #{user.age},
            </if>
            <if test="user.email != null and user.email != ''">
                email = #{user.email},
            </if>
            update_time = NOW()
        </set>
        WHERE id = #{user.id}
    </foreach>
</update>

6. trim标签 - 自定义格式化

<!-- 自定义前缀和后缀处理 -->
<select id="selectByConditionWithTrim" resultType="User">
    SELECT * FROM user
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="name != null and name != ''">
            AND name = #{name}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="status != null">
            OR status = #{status}
        </if>
    </trim>
</select>

<!-- 复杂的trim应用 -->
<insert id="insertSelective">
    INSERT INTO user
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="name != null">name,</if>
        <if test="age != null">age,</if>
        <if test="email != null">email,</if>
        <if test="phone != null">phone,</if>
        create_time,
    </trim>
    <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
        <if test="name != null">#{name},</if>
        <if test="age != null">#{age},</if>
        <if test="email != null">#{email},</if>
        <if test="phone != null">#{phone},</if>
        NOW(),
    </trim>
</insert>

SQL片段复用

面试加分项：

<!-- 定义可复用的SQL片段 -->
<sql id="userColumns">
    id, name, age, email, phone, status, create_time, update_time
</sql>

<sql id="userConditions">
    <where>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="ageMin != null">
            AND age >= #{ageMin}
        </if>
        <if test="ageMax != null">
            AND age <= #{ageMax}
        </if