MyBatis 动态SQL完整指南

概述

MyBatis动态SQL 是MyBatis框架的核心特性之一，允许开发者根据不同的条件动态构建SQL语句。通过使用OGNL表达式和XML标签，可以实现复杂的条件查询、批量操作和SQL片段复用，大大提高了代码的灵活性和可维护性。

核心特性

• 条件构建：根据参数动态生成WHERE条件
• SQL片段复用：提取公共SQL片段，提高代码复用性
• 批量操作：高效处理批量插入、更新和删除
• 参数绑定：安全的参数绑定，防止SQL注入
• 类型处理：自动处理Java类型与数据库类型的转换

应用场景

💡 提示: 动态SQL是构建灵活数据访问层的关键技术，掌握其使用技巧对于企业级应用开发至关重要。

动态SQL基础

基础概念

1. OGNL表达式

MyBatis使用OGNL（Object-Graph Navigation Language）表达式来评估动态SQL中的条件。

<!-- 基础OGNL表达式示例 -->
<select id="findUsers" parameterType="UserQuery" resultType="User">
    SELECT * FROM users
    <where>
        <!-- 简单属性判断 -->
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        
        <!-- 数值比较 -->
        <if test="age != null and age > 0">
            AND age = #{age}
        </if>
        
        <!-- 集合判断 -->
        <if test="roles != null and roles.size() > 0">
            AND role_id IN
            <foreach collection="roles" item="role" open="(" close=")" separator=",">
                #{role.id}
            </foreach>
        </if>
        
        <!-- 复杂条件 -->
        <if test="(status != null and status == 'ACTIVE') or includeInactive">
            AND status = #{status}
        </if>
    </where>
</select>

2. 参数传递方式

// 查询参数对象
public class UserQuery {
    private String name;
    private Integer age;
    private String email;
    private List<String> roles;
    private Boolean includeInactive;
    private Date createTimeStart;
    private Date createTimeEnd;
    
    // 构造函数、getter和setter方法
    public UserQuery() {}
    
    public UserQuery(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
    
    // 便捷方法
    public boolean hasNameFilter() {
        return name != null && !name.trim().isEmpty();
    }
    
    public boolean hasAgeFilter() {
        return age != null && age > 0;
    }
    
    public boolean hasTimeRangeFilter() {
        return createTimeStart != null && createTimeEnd != null;
    }
    
    // getters and setters...
}

// 使用Map传递参数
Map<String, Object> params = new HashMap<>();
params.put("name", "张三");
params.put("minAge", 18);
params.put("maxAge", 65);
params.put("departments", Arrays.asList("IT", "HR", "Finance"));

// 使用@Param注解
public interface UserMapper {
    List<User> findUsers(@Param("query") UserQuery query, 
                        @Param("limit") Integer limit);
    
    List<User> searchUsers(@Param("keyword") String keyword,
                          @Param("status") String status,
                          @Param("roles") List<String> roles);
}

条件判断元素

if元素详解

1. 基础if条件

<select id="findUsersByCondition" parameterType="UserQuery" resultType="User">
    SELECT 
        id, username, email, phone, status, 
        create_time, update_time
    FROM users
    <where>
        <!-- 字符串条件 -->
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        
        <!-- 邮箱精确匹配 -->
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        
        <!-- 状态枚举 -->
        <if test="status != null">
            AND status = #{status}
        </if>
        
        <!-- 数值范围 -->
        <if test="minAge != null">
            AND age >= #{minAge}
        </if>
        <if test="maxAge != null">
            AND age <= #{maxAge}
        </if>
        
        <!-- 日期范围 -->
        <if test="startDate != null">
            AND create_time >= #{startDate}
        </if>
        <if test="endDate != null">
            AND create_time <= #{endDate}
        </if>
        
        <!-- 布尔条件 -->
        <if test="isActive != null and isActive">
            AND status = 'ACTIVE'
        </if>
    </where>
    ORDER BY create_time DESC
</select>

2. 复杂if条件

<select id="findUsersAdvanced" parameterType="UserQuery" resultType="User">
    SELECT * FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    <where>
        <!-- 多字段模糊搜索 -->
        <if test="keyword != null and keyword != ''">
            AND (
                u.username LIKE CONCAT('%', #{keyword}, '%') OR
                u.email LIKE CONCAT('%', #{keyword}, '%') OR
                up.real_name LIKE CONCAT('%', #{keyword}, '%')
            )
        </if>
        
        <!-- 复合条件：VIP用户或指定部门 -->
        <if test="includeVip or (department != null and department != '')">
            AND (
                <if test="includeVip">
                    up.is_vip = true
                </if>
                <if test="includeVip and department != null and department != ''">
                    OR
                </if>
                <if test="department != null and department != ''">
                    up.department = #{department}
                </if>
            )
        </if>
        
        <!-- 权限级别 -->
        <if test="minLevel != null">
            AND up.level >= #{minLevel}
        </if>
        
        <!-- 地理位置过滤 -->
        <if test="city != null and city != ''">
            AND up.city = #{city}
        </if>
        <if test="province != null and province != ''">
            AND up.province = #{province}
        </if>
        
        <!-- 排除已删除 -->
        <if test="!includeDeleted">
            AND u.deleted = false
        </if>
    </where>
</select>

choose-when-otherwise元素

1. 基础choose结构

<select id="findUsersByPriority" parameterType="UserQuery" resultType="User">
    SELECT * FROM users
    <where>
        <!-- 按优先级选择查询条件 -->
        <choose>
            <!-- 优先级1：按用户ID查询 -->
            <when test="userId != null">
                id = #{userId}
            </when>
            
            <!-- 优先级2：按用户名查询 -->
            <when test="username != null and username != ''">
                username = #{username}
            </when>
            
            <!-- 优先级3：按邮箱查询 -->
            <when test="email != null and email != ''">
                email = #{email}
            </when>
            
            <!-- 优先级4：按手机号查询 -->
            <when test="phone != null and phone != ''">
                phone = #{phone}
            </when>
            
            <!-- 默认：查询活跃用户 -->
            <otherwise>
                status = 'ACTIVE' AND last_login_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            </otherwise>
        </choose>
    </where>
    ORDER BY last_login_time DESC
    LIMIT 100
</select>

2. 复杂choose逻辑

<select id="findProductsByStrategy" parameterType="ProductQuery" resultType="Product">
    SELECT 
        p.*, 
        c.name as category_name,
        b.name as brand_name
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN brands b ON p.brand_id = b.id
    <where>
        <!-- 根据搜索策略选择不同的查询方式 -->
        <choose>
            <!-- 策略1：精确匹配 -->
            <when test="searchStrategy == 'EXACT'">
                <if test="productName != null and productName != ''">
                    AND p.name = #{productName}
                </if>
                <if test="brandName != null and brandName != ''">
                    AND b.name = #{brandName}
                </if>
                <if test="categoryName != null and categoryName != ''">
                    AND c.name = #{categoryName}
                </if>
            </when>
            
            <!-- 策略2：模糊匹配 -->
            <when test="searchStrategy == 'FUZZY'">
                <if test="keyword != null and keyword != ''">
                    AND (
                        p.name LIKE CONCAT('%', #{keyword}, '%') OR
                        p.description LIKE CONCAT('%', #{keyword}, '%') OR
                        b.name LIKE CONCAT('%', #{keyword}, '%') OR
                        c.name LIKE CONCAT('%', #{keyword}, '%')
                    )
                </if>
            </when>
            
            <!-- 策略3：全文搜索 -->
            <when test="searchStrategy == 'FULLTEXT'">
                <if test="searchText != null and searchText != ''">
                    AND MATCH(p.name, p.description) AGAINST(#{searchText} IN NATURAL LANGUAGE MODE)
                </if>
            </when>
            
            <!-- 策略4：按价格区间 -->
            <when test="searchStrategy == 'PRICE_RANGE'">
                <if test="minPrice != null">
                    AND p.price >= #{minPrice}
                </if>
                <if test="maxPrice != null">
                    AND p.price <= #{maxPrice}
                </if>
            </when>
            
            <!-- 默认策略：推荐商品 -->
            <otherwise>
                AND p.is_recommended = true
                AND p.status = 'ACTIVE'
                AND p.stock > 0
            </otherwise>
        </choose>
        
        <!-- 通用过滤条件 -->
        <if test="excludeOutOfStock">
            AND p.stock > 0
        </if>
        
        <if test="onlyActive">
            AND p.status = 'ACTIVE'
        </if>
    </where>
    
    <!-- 动态排序 -->
    ORDER BY
    <choose>
        <when test="sortBy == 'PRICE_ASC'">
            p.price ASC
        </when>
        <when test="sortBy == 'PRICE_DESC'">
            p.price DESC
        </when>
        <when test="sortBy == 'NAME'">
            p.name ASC
        </when>
        <when test="sortBy == 'SALES'">
            p.sales_count DESC
        </when>
        <otherwise>
            p.create_time DESC
        </otherwise>
    </choose>
</select>

where和trim元素

1. where元素用法

<select id="findOrdersWithWhere" parameterType="OrderQuery" resultType="Order">
    SELECT 
        o.*, 
        u.username,
        COUNT(oi.id) as item_count
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    <where>
        <!-- where元素会自动处理第一个AND/OR -->
        <if test="orderId != null">
            AND o.id = #{orderId}
        </if>
        
        <if test="userId != null">
            AND o.user_id = #{userId}
        </if>
        
        <if test="status != null and status != ''">
            AND o.status = #{status}
        </if>
        
        <if test="startDate != null">
            AND o.create_time >= #{startDate}
        </if>
        
        <if test="endDate != null">
            AND o.create_time <= #{endDate}
        </if>
        
        <if test="minAmount != null">
            AND o.total_amount >= #{minAmount}
        </if>
        
        <if test="maxAmount != null">
            AND o.total_amount <= #{maxAmount}
        </if>
        
        <!-- 复杂条件组合 -->
        <if test="paymentMethod != null and paymentMethod.size() > 0">
            AND o.payment_method IN
            <foreach collection="paymentMethod" item="method" open="(" close=")" separator=",">
                #{method}
            </foreach>
        </if>
    </where>
    GROUP BY o.id
    <if test="minItemCount != null">
        HAVING COUNT(oi.id) >= #{minItemCount}
    </if>
    ORDER BY o.create_time DESC
</select>

2. trim元素详解

<select id="findUsersWithTrim" parameterType="UserQuery" resultType="User">
    SELECT * FROM users
    
    <!-- 使用trim替代where，更灵活的控制 -->
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        
        <if test="status != null">
            AND status = #{status}
        </if>
        
        <!-- 复杂OR条件 -->
        <if test="phoneOrEmail != null and phoneOrEmail != ''">
            AND (phone = #{phoneOrEmail} OR email = #{phoneOrEmail})
        </if>
    </trim>
</select>

<!-- 使用trim处理UPDATE语句 -->
<update id="updateUserSelective" parameterType="User">
    UPDATE users
    <trim prefix="SET" suffixOverrides=",">
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        
        <if test="email != null and email != ''">
            email = #{email},
        </if>
        
        <if test="phone != null">
            phone = #{phone},
        </if>
        
        <if test="status != null">
            status = #{status},
        </if>
        
        <if test="profilePicture != null">
            profile_picture = #{profilePicture},
        </if>
        
        <!-- 始终更新修改时间 -->
        update_time = NOW(),
    </trim>
    WHERE id = #{id}
</update>

set元素

1. 基础set用法

<update id="updateUser" parameterType="User">
    UPDATE users
    <set>
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        
        <if test="email != null and email != ''">
            email = #{email},
        </if>
        
        <if test="phone != null">
            phone = #{phone},
        </if>
        
        <if test="realName != null and realName != ''">
            real_name = #{realName},
        </if>
        
        <if test="avatar != null">
            avatar = #{avatar},
        </if>
        
        <if test="birthday != null">
            birthday = #{birthday},
        </if>
        
        <if test="gender != null">
            gender = #{gender},
        </if>
        
        <!-- 总是更新修改时间 -->
        update_time = NOW(),
    </set>
    WHERE id = #{id}
</update>

2. 复杂set操作

<update id="updateUserProfile" parameterType="UserProfile">
    UPDATE user_profiles
    <set>
        <!-- 基础信息更新 -->
        <if test="realName != null and realName != ''">
            real_name = #{realName},
        </if>
        
        <if test="nickname != null">
            nickname = #{nickname},
        </if>
        
        <!-- 联系信息 -->
        <if test="phone != null">
            phone = #{phone},
        </if>
        
        <if test="address != null">
            address = #{address},
        </if>
        
        <!-- 个人设置 -->
        <if test="timezone != null and timezone != ''">
            timezone = #{timezone},
        </if>
        
        <if test="language != null and language != ''">
            language = #{language},
        </if>
        
        <!-- 隐私设置 -->
        <if test="isPublic != null">
            is_public = #{isPublic},
        </if>
        
        <if test="allowMessages != null">
            allow_messages = #{allowMessages},
        </if>
        
        <!-- JSON字段更新 -->
        <if test="preferences != null">
            preferences = #{preferences, typeHandler=JsonTypeHandler},
        </if>
        
        <if test="socialLinks != null">
            social_links = #{socialLinks, typeHandler=JsonTypeHandler},
        </if>
        
        <!-- 计数器字段 -->
        <if test="incrementLoginCount">
            login_count = login_count + 1,
            last_login_time = NOW(),
        </if>
        
        <!-- 总是更新版本号和修改时间 -->
        version = version + 1,
        update_time = NOW(),
    </set>
    WHERE user_id = #{userId}
      AND version = #{version} <!-- 乐观锁 -->
</update>

循环处理元素

foreach元素详解

1. 基础foreach用法

<!-- IN查询 -->
<select id="findUsersByIds" parameterType="java.util.List" resultType="User">
    SELECT * FROM users
    WHERE id IN
    <foreach collection="list" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

<!-- 批量插入 -->
<insert id="batchInsertUsers" parameterType="java.util.List">
    INSERT INTO users (username, email, phone, status, create_time)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.username}, #{user.email}, #{user.phone}, #{user.status}, NOW())
    </foreach>
</insert>

<!-- 批量更新 -->
<update id="batchUpdateUserStatus">
    <foreach collection="userStatusList" item="item" separator=";">
        UPDATE users 
        SET status = #{item.status}, update_time = NOW()
        WHERE id = #{item.userId}
    </foreach>
</update>

2. 复杂foreach操作

<!-- 多条件OR查询 -->
<select id="findUsersByMultipleConditions" resultType="User">
    SELECT DISTINCT u.* FROM users u
    LEFT JOIN user_roles ur ON u.id = ur.user_id
    <where>
        <if test="conditions != null and conditions.size() > 0">
            <foreach collection="conditions" item="condition" open="(" close=")" separator=" OR ">
                (
                    <if test="condition.username != null and condition.username != ''">
                        u.username LIKE CONCAT('%', #{condition.username}, '%')
                    </if>
                    <if test="condition.email != null and condition.email != ''">
                        <if test="condition.username != null and condition.username != ''">AND</if>
                        u.email = #{condition.email}
                    </if>
                    <if test="condition.roles != null and condition.roles.size() > 0">
                        <if test="(condition.username != null and condition.username != '') or (condition.email != null and condition.email != '')">
                            AND
                        </if>
                        ur.role_name IN
                        <foreach collection="condition.roles" item="role" open="(" close=")" separator=",">
                            #{role}
                        </foreach>
                    </if>
                )
            </foreach>
        </if>
    </where>
</select>

<!-- 复杂批量插入带冲突处理 -->
<insert id="batchInsertUsersWithConflict" parameterType="java.util.List">
    INSERT INTO users (username, email, phone, status, create_time)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.username}, #{user.email}, #{user.phone}, #{user.status}, NOW())
    </foreach>
    ON DUPLICATE KEY UPDATE
        email = VALUES(email),
        phone = VALUES(phone),
        status = VALUES(status),
        update_time = NOW()
</insert>

<!-- 动态表名批量查询 -->
<select id="searchAcrossMultipleTables" resultType="java.util.Map">
    <foreach collection="tableNames" item="tableName" open="" close="" separator=" UNION ALL ">
        (
            SELECT 
                '${tableName}' as table_name,
                id,
                name,
                create_time
            FROM ${tableName}
            <where>
                <if test="keyword != null and keyword != ''">
                    name LIKE CONCAT('%', #{keyword}, '%')
                </if>
                <if test="startDate != null">
                    AND create_time >= #{startDate}
                </if>
                <if test="endDate != null">
                    AND create_time <= #{endDate}
                </if>
            </where>
        )
    </foreach>
    ORDER BY create_time DESC
    LIMIT #{limit}
</select>

3. 集合参数处理

// Mapper接口参数定义
public interface UserMapper {
    
    // 简单列表参数
    List<User> findUsersByIds(@Param("ids") List<Long> ids);
    
    // 复杂对象列表
    void batchInsertUsers(@Param("users") List<User> users);
    
    // Map参数中的列表
    List<User> searchUsers(@Param("query") Map<String, Object> query);
    
    // 多个列表参数
    List<Order> findOrdersByMultipleCriteria(
        @Param("userIds") List<Long> userIds,
        @Param("statuses") List<String> statuses,
        @Param("paymentMethods") List<String> paymentMethods
    );
}

<!-- 处理Map中的集合参数 -->
<select id="searchUsers" parameterType="java.util.Map" resultType="User">
    SELECT * FROM users
    <where>
        <if test="userIds != null and userIds.size() > 0">
            AND id IN
            <foreach collection="userIds" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
        
        <if test="keywords != null and keywords.size() > 0">
            AND (
                <foreach collection="keywords" item="keyword" separator=" OR ">
                    username LIKE CONCAT('%', #{keyword}, '%') OR
                    email LIKE CONCAT('%', #{keyword}, '%')
                </foreach>
            )
        </if>
        
        <if test="excludeIds != null and excludeIds.size() > 0">
            AND id NOT IN
            <foreach collection="excludeIds" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
    </where>
</select>

<!-- 复杂的多列表条件查询 -->
<select id="findOrdersByMultipleCriteria" resultType="Order">
    SELECT o.*, u.username, p.name as product_name
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    LEFT JOIN products p ON oi.product_id = p.id
    <where>
        <if test="userIds != null and userIds.size() > 0">
            AND o.user_id IN
            <foreach collection="userIds" item="userId" open="(" close=")" separator=",">
                #{userId}
            </foreach>
        </if>
        
        <if test="statuses != null and statuses.size() > 0">
            AND o.status IN
            <foreach collection="statuses" item="status" open="(" close=")" separator=",">
                #{status}
            </foreach>
        </if>
        
        <if test="paymentMethods != null and paymentMethods.size() > 0">
            AND o.payment_method IN
            <foreach collection="paymentMethods" item="method" open="(" close=")" separator=",">
                #{method}
            </foreach>
        </if>
    </where>
    GROUP BY o.id
</select>

SQL片段管理

sql元素和include元素

1. 基础SQL片段

<!-- 定义可重用的SQL片段 -->
<sql id="userColumns">
    u.id, u.username, u.email, u.phone, u.status, 
    u.create_time, u.update_time, u.last_login_time
</sql>

<sql id="userProfileColumns">
    up.real_name, up.avatar, up.birthday, up.gender,
    up.city, up.province, up.country, up.bio
</sql>

<sql id="userTableJoins">
    FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    LEFT JOIN user_roles ur ON u.id = ur.user_id
    LEFT JOIN roles r ON ur.role_id = r.id
</sql>

<!-- 使用SQL片段 -->
<select id="findUserWithProfile" parameterType="long" resultType="UserVO">
    SELECT 
        <include refid="userColumns"/>,
        <include refid="userProfileColumns"/>,
        GROUP_CONCAT(r.name) as roles
    <include refid="userTableJoins"/>
    WHERE u.id = #{id}
    GROUP BY u.id
</select>

<select id="findActiveUsers" resultType="UserVO">
    SELECT 
        <include refid="userColumns"/>,
        <include refid="userProfileColumns"/>
    <include refid="userTableJoins"/>
    WHERE u.status = 'ACTIVE'
      AND u.deleted = false
    ORDER BY u.last_login_time DESC
</select>

2. 参数化SQL片段

<!-- 带参数的SQL片段 -->
<sql id="dateRangeCondition">
    <if test="${dateField} != null and ${startDate} != null">
        AND ${dateField} >= #{${startDate}}
    </if>
    <if test="${dateField} != null and ${endDate} != null">
        AND ${dateField} <= #{${endDate}}
    </if>
</sql>

<sql id="statusCondition">
    <if test="${statusField} != null and ${statusValue} != null">
        AND ${statusField} = #{${statusValue}}
    </if>
</sql>

<sql id="paginationLimit">
    <if test="offset != null and limit != null">
        LIMIT #{offset}, #{limit}
    </if>
    <if test="offset == null and limit != null">
        LIMIT #{limit}
    </if>
</sql>

<!-- 使用参数化SQL片段 -->
<select id="findOrdersByDateRange" parameterType="OrderQuery" resultType="Order">
    SELECT * FROM orders
    <where>
        <include refid="dateRangeCondition">
            <property name="dateField" value="create_time"/>
            <property name="startDate" value="startDate"/>
            <property name="endDate" value="endDate"/>
        </include>
        
        <include refid="statusCondition">
            <property name="statusField" value="status"/>
            <property name="statusValue" value="status"/>
        </include>
    </where>
    ORDER BY create_time DESC
    <include refid="paginationLimit"/>
</select>

<select id="findUsersByLastLogin" parameterType="UserQuery" resultType="User">
    SELECT * FROM users
    <where>
        <include refid="dateRangeCondition">
            <property name="dateField" value="last_login_time"/>
            <property name="startDate" value="loginStartDate"/>
            <property name="endDate" value="loginEndDate"/>
        </include>
        
        <include refid="statusCondition">
            <property name="statusField" value="status"/>
            <property name="statusValue" value="userStatus"/>
        </include>
    </where>
    <include refid="paginationLimit"/>
</select>

3. 复杂SQL片段组合

<!-- 复杂查询条件片段 -->
<sql id="productSearchConditions">
    <if test="keyword != null and keyword != ''">
        AND (
            p.name LIKE CONCAT('%', #{keyword}, '%') OR
            p.description LIKE CONCAT('%', #{keyword}, '%') OR
            p.sku LIKE CONCAT('%', #{keyword}, '%')
        )
    </if>
    
    <if test="categoryIds != null and categoryIds.size() > 0">
        AND p.category_id IN
        <foreach collection="categoryIds" item="categoryId" open="(" close=")" separator=",">
            #{categoryId}
        </foreach>
    </if>
    
    <if test="brandIds != null and brandIds.size() > 0">
        AND p.brand_id IN
        <foreach collection="brandIds" item="brandId" open="(" close=")" separator=",">
            #{brandId}
        </foreach>
    </if>
    
    <if test="priceMin != null">
        AND p.price >= #{priceMin}
    </if>
    
    <if test="priceMax != null">
        AND p.price <= #{priceMax}
    </if>
    
    <if test="hasStock">
        AND p.stock > 0
    </if>
    
    <if test="isActive">
        AND p.status = 'ACTIVE'
    </if>
</sql>

<sql id="productOrderBy">
    ORDER BY
    <choose>
        <when test="sortBy == 'PRICE_ASC'">
            p.price ASC
        </when>
        <when test="sortBy == 'PRICE_DESC'">
            p.price DESC
        </when>
        <when test="sortBy == 'NAME'">
            p.name ASC
        </when>
        <when test="sortBy == 'POPULARITY'">
            p.view_count DESC, p.sales_count DESC
        </when>
        <when test="sortBy == 'NEWEST'">
            p.create_time DESC
        </when>
        <otherwise>
            p.update_time DESC
        </otherwise>
    </choose>
</sql>

<!-- 商品搜索查询 -->
<select id="searchProducts" parameterType="ProductQuery" resultType="Product">
    SELECT 
        p.*,
        c.name as category_name,
        b.name as brand_name
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN brands b ON p.brand_id = b.id
    <where>
        <include refid="productSearchConditions"/>
    </where>
    <include refid="productOrderBy"/>
    <include refid="paginationLimit"/>
</select>

<!-- 商品统计查询 -->
<select id="getProductStatistics" parameterType="ProductQuery" resultType="ProductStatistics">
    SELECT 
        COUNT(*) as total_count,
        AVG(p.price) as avg_price,
        MIN(p.price) as min_price,
        MAX(p.price) as max_price,
        SUM(p.stock) as total_stock
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN brands b ON p.brand_id = b.id
    <where>
        <include refid="productSearchConditions"/>
    </where>
</select>

高级动态SQL技巧

嵌套查询动态化

1. 动态子查询

<!-- 复杂的用户权限查询 -->
<select id="findUsersWithPermissions" parameterType="UserPermissionQuery" resultType="UserVO">
    SELECT DISTINCT u.*, 
           up.real_name,
           GROUP_CONCAT(DISTINCT r.name) as roles,
           GROUP_CONCAT(DISTINCT p.name) as permissions
    FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    LEFT JOIN user_roles ur ON u.id = ur.user_id
    LEFT JOIN roles r ON ur.role_id = r.id
    LEFT JOIN role_permissions rp ON r.id = rp.role_id
    LEFT JOIN permissions p ON rp.permission_id = p.id
    <where>
        <if test="userQuery != null">
            <!-- 用户基本信息查询 -->
            <if test="userQuery.username != null and userQuery.username != ''">
                AND u.username LIKE CONCAT('%', #{userQuery.username}, '%')
            </if>
            <if test="userQuery.email != null and userQuery.email != ''">
                AND u.email = #{userQuery.email}
            </if>
            <if test="userQuery.status != null">
                AND u.status = #{userQuery.status}
            </if>
        </if>
        
        <!-- 角色权限动态子查询 -->
        <if test="roleQuery != null and roleQuery.requiredRoles != null and roleQuery.requiredRoles.size() > 0">
            AND u.id IN (
                SELECT DISTINCT ur2.user_id 
                FROM user_roles ur2 
                JOIN roles r2 ON ur2.role_id = r2.id
                WHERE r2.name IN
                <foreach collection="roleQuery.requiredRoles" item="role" open="(" close=")" separator=",">
                    #{role}
                </foreach>
                <if test="roleQuery.matchAll">
                    GROUP BY ur2.user_id
                    HAVING COUNT(DISTINCT r2.name) = #{roleQuery.requiredRoles.size()}
                </if>
            )
        </if>
        
        <!-- 权限动态子查询 -->
        <if test="permissionQuery != null and permissionQuery.requiredPermissions != null and permissionQuery.requiredPermissions.size() > 0">
            AND u.id IN (
                SELECT DISTINCT ur3.user_id
                FROM user_roles ur3
                JOIN role_permissions rp3 ON ur3.role_id = rp3.role_id
                JOIN permissions p3 ON rp3.permission_id = p3.id
                WHERE p3.code IN
                <foreach collection="permissionQuery.requiredPermissions" item="permission" open="(" close=")" separator=",">
                    #{permission}
                </foreach>
                <if test="permissionQuery.matchAll">
                    GROUP BY ur3.user_id
                    HAVING COUNT(DISTINCT p3.code) = #{permissionQuery.requiredPermissions.size()}
                </if>
            )
        </if>
        
        <!-- 时间范围查询 -->
        <if test="timeQuery != null">
            <if test="timeQuery.startDate != null">
                AND u.create_time >= #{timeQuery.startDate}
            </if>
            <if test="timeQuery.endDate != null">
                AND u.create_time <= #{timeQuery.endDate}
            </if>
            <if test="timeQuery.lastLoginAfter != null">
                AND u.last_login_time >= #{timeQuery.lastLoginAfter}
            </if>
        </if>
        
        <!-- 排除条件 -->
        <if test="excludeQuery != null">
            <if test="excludeQuery.excludeUserIds != null and excludeQuery.excludeUserIds.size() > 0">
                AND u.id NOT IN
                <foreach collection="excludeQuery.excludeUserIds" item="userId" open="(" close=")" separator=",">
                    #{userId}
                </foreach>
            </if>
            <if test="excludeQuery.excludeRoles != null and excludeQuery.excludeRoles.size() > 0">
                AND u.id NOT IN (
                    SELECT ur4.user_id
                    FROM user_roles ur4
                    JOIN roles r4 ON ur4.role_id = r4.id
                    WHERE r4.name IN
                    <foreach collection="excludeQuery.excludeRoles" item="role" open="(" close=")" separator=",">
                        #{role}
                    </foreach>
                )
            </if>
        </if>
    </where>
    GROUP BY u.id
    <if test="havingQuery != null">
        HAVING 1=1
        <if test="havingQuery.minRoleCount != null">
            AND COUNT(DISTINCT r.id) >= #{havingQuery.minRoleCount}
        </if>
        <if test="havingQuery.minPermissionCount != null">
            AND COUNT(DISTINCT p.id) >= #{havingQuery.minPermissionCount}
        </if>
    </if>
    ORDER BY u.create_time DESC
    <if test="pagination != null">
        LIMIT #{pagination.offset}, #{pagination.limit}
    </if>
</select>

2. 动态Union查询

<!-- 跨表搜索查询 -->
<select id="searchAcrossEntities" parameterType="GlobalSearchQuery" resultType="SearchResultVO">
    <if test="searchTables != null and searchTables.size() > 0">
        <foreach collection="searchTables" item="table" separator=" UNION ALL ">
            (
                <choose>
                    <when test="table == 'USERS'">
                        SELECT 
                            'USER' as entity_type,
                            u.id as entity_id,
                            u.username as title,
                            u.email as description,
                            u.create_time as created_at,
                            u.status as status
                        FROM users u
                        <where>
                            <if test="keyword != null and keyword != ''">
                                AND (u.username LIKE CONCAT('%', #{keyword}, '%') OR
                                     u.email LIKE CONCAT('%', #{keyword}, '%'))
                            </if>
                            <if test="filters != null and filters.userStatus != null">
                                AND u.status = #{filters.userStatus}
                            </if>
                        </where>
                    </when>
                    
                    <when test="table == 'ORDERS'">
                        SELECT 
                            'ORDER' as entity_type,
                            o.id as entity_id,
                            CONCAT('订单 #', o.order_number) as title,
                            CONCAT('金额: ¥', o.total_amount) as description,
                            o.create_time as created_at,
                            o.status as status
                        FROM orders o
                        <where>
                            <if test="keyword != null and keyword != ''">
                                AND (o.order_number LIKE CONCAT('%', #{keyword}, '%') OR
                                     o.customer_name LIKE CONCAT('%', #{keyword}, '%'))
                            </if>
                            <if test="filters != null and filters.orderStatus != null">
                                AND o.status = #{filters.orderStatus}
                            </if>
                            <if test="filters != null and filters.minAmount != null">
                                AND o.total_amount >= #{filters.minAmount}
                            </if>
                        </where>
                    </when>
                    
                    <when test="table == 'PRODUCTS'">
                        SELECT 
                            'PRODUCT' as entity_type,
                            p.id as entity_id,
                            p.name as title,
                            p.description as description,
                            p.create_time as created_at,
                            p.status as status
                        FROM products p
                        <where>
                            <if test="keyword != null and keyword != ''">
                                AND (p.name LIKE CONCAT('%', #{keyword}, '%') OR
                                     p.description LIKE CONCAT('%', #{keyword}, '%') OR
                                     p.sku LIKE CONCAT('%', #{keyword}, '%'))
                            </if>
                            <if test="filters != null and filters.productStatus != null">
                                AND p.status = #{filters.productStatus}
                            </if>
                            <if test="filters != null and filters.categoryId != null">
                                AND p.category_id = #{filters.categoryId}
                            </if>
                        </where>
                    </when>
                    
                    <otherwise>
                        <!-- 默认空查询 -->
                        SELECT 
                            'UNKNOWN' as entity_type,
                            0 as entity_id,
                            '' as title,
                            '' as description,
                            NOW() as created_at,
                            'ACTIVE' as status
                        WHERE 1=0
                    </otherwise>
                </choose>
            )
        </foreach>
    </if>
    <if test="searchTables == null or searchTables.size() == 0">
        <!-- 无搜索表时的默认查询 -->
        SELECT 
            'EMPTY' as entity_type,
            0 as entity_id,
            '无搜索结果' as title,
            '请指定搜索范围' as description,
            NOW() as created_at,
            'INACTIVE' as status
        WHERE 1=0
    </if>
    ORDER BY created_at DESC
    <if test="limit != null">
        LIMIT #{limit}
    </if>
</select>

复杂条件组合

1. 多级条件嵌套

<!-- 复杂的商品筛选查询 -->
<select id="searchProductsAdvanced" parameterType="ProductAdvancedQuery" resultType="ProductVO">
    SELECT 
        p.*,
        c.name as category_name,
        b.name as brand_name,
        AVG(pr.rating) as avg_rating,
        COUNT(pr.id) as review_count,
        SUM(oi.quantity) as total_sales
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN brands b ON p.brand_id = b.id
    LEFT JOIN product_reviews pr ON p.id = pr.product_id
    LEFT JOIN order_items oi ON p.id = oi.product_id
    <where>
        <!-- 基础条件组 -->
        <if test="basicQuery != null">
            <if test="basicQuery.keyword != null and basicQuery.keyword != ''">
                AND (
                    p.name LIKE CONCAT('%', #{basicQuery.keyword}, '%') OR
                    p.description LIKE CONCAT('%', #{basicQuery.keyword}, '%') OR
                    p.sku LIKE CONCAT('%', #{basicQuery.keyword}, '%')
                )
            </if>
            <if test="basicQuery.status != null">
                AND p.status = #{basicQuery.status}
            </if>
            <if test="basicQuery.inStock != null and basicQuery.inStock">
                AND p.stock > 0
            </if>
        </if>
        
        <!-- 分类筛选组 -->
        <if test="categoryQuery != null">
            <choose>
                <when test="categoryQuery.categoryIds != null and categoryQuery.categoryIds.size() > 0">
                    AND p.category_id IN
                    <foreach collection="categoryQuery.categoryIds" item="categoryId" open="(" close=")" separator=",">
                        #{categoryId}
                    </foreach>
                </when>
                <when test="categoryQuery.categoryPath != null and categoryQuery.categoryPath != ''">
                    AND p.category_id IN (
                        SELECT id FROM categories 
                        WHERE path LIKE CONCAT(#{categoryQuery.categoryPath}, '%')
                    )
                </when>
                <when test="categoryQuery.excludeCategoryIds != null and categoryQuery.excludeCategoryIds.size() > 0">
                    AND p.category_id NOT IN
                    <foreach collection="categoryQuery.excludeCategoryIds" item="categoryId" open="(" close=")" separator=",">
                        #{categoryId}
                    </foreach>
                </when>
            </choose>
        </if>
        
        <!-- 价格筛选组 -->
        <if test="priceQuery != null">
            <if test="priceQuery.priceRanges != null and priceQuery.priceRanges.size() > 0">
                AND (
                    <foreach collection="priceQuery.priceRanges" item="range" separator=" OR ">
                        (p.price >= #{range.min} AND p.price <= #{range.max})
                    </foreach>
                )
            </if>
            <if test="priceQuery.discountOnly != null and priceQuery.discountOnly">
                AND p.discount_price IS NOT NULL AND p.discount_price < p.price
            </if>
        </if>
        
        <!-- 品牌筛选组 -->
        <if test="brandQuery != null">
            <if test="brandQuery.brandIds != null and brandQuery.brandIds.size() > 0">
                AND p.brand_id IN
                <foreach collection="brandQuery.brandIds" item="brandId" open="(" close=")" separator=",">
                    #{brandId}
                </foreach>
            </if>
            <if test="brandQuery.popularBrandsOnly != null and brandQuery.popularBrandsOnly">
                AND p.brand_id IN (
                    SELECT id FROM brands WHERE is_popular = true
                )
            </if>
        </if>
        
        <!-- 属性筛选组 -->
        <if test="attributeQuery != null and attributeQuery.attributes != null and attributeQuery.attributes.size() > 0">
            <foreach collection="attributeQuery.attributes" item="attr" separator="">
                AND p.id IN (
                    SELECT product_id FROM product_attributes 
                    WHERE attribute_name = #{attr.name}
                    <if test="attr.values != null and attr.values.size() > 0">
                        AND attribute_value IN
                        <foreach collection="attr.values" item="value" open="(" close=")" separator=",">
                            #{value}
                        </foreach>
                    </if>
                )
            </foreach>
        </if>
        
        <!-- 评分筛选组 -->
        <if test="ratingQuery != null">
            <if test="ratingQuery.minRating != null">
                AND p.id IN (
                    SELECT product_id FROM product_reviews 
                    GROUP BY product_id 
                    HAVING AVG(rating) >= #{ratingQuery.minRating}
                )
            </if>
            <if test="ratingQuery.minReviewCount != null">
                AND p.id IN (
                    SELECT product_id FROM product_reviews 
                    GROUP BY product_id 
                    HAVING COUNT(*) >= #{ratingQuery.minReviewCount}
                )
            </if>
        </if>
        
        <!-- 销量筛选组 -->
        <if test="salesQuery != null">
            <if test="salesQuery.minSales != null">
                AND p.id IN (
                    SELECT product_id FROM order_items 
                    GROUP BY product_id 
                    HAVING SUM(quantity) >= #{salesQuery.minSales}
                )
            </if>
            <if test="salesQuery.hotProductsOnly != null and salesQuery.hotProductsOnly">
                AND p.id IN (
                    SELECT product_id FROM order_items 
                    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                    GROUP BY product_id 
                    HAVING SUM(quantity) >= 100
                )
            </if>
        </if>
        
        <!-- 时间筛选组 -->
        <if test="timeQuery != null">
            <if test="timeQuery.newProductsOnly != null and timeQuery.newProductsOnly">
                AND p.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            </if>
            <if test="timeQuery.createTimeStart != null">
                AND p.create_time >= #{timeQuery.createTimeStart}
            </if>
            <if test="timeQuery.createTimeEnd != null">
                AND p.create_time <= #{timeQuery.createTimeEnd}
            </if>
        </if>
    </where>
    GROUP BY p.id
    <if test="havingQuery != null">
        HAVING 1=1
        <if test="havingQuery.minAvgRating != null">
            AND AVG(pr.rating) >= #{havingQuery.minAvgRating}
        </if>
        <if test="havingQuery.minReviewCount != null">
            AND COUNT(pr.id) >= #{havingQuery.minReviewCount}
        </if>
        <if test="havingQuery.minTotalSales != null">
            AND SUM(oi.quantity) >= #{havingQuery.minTotalSales}
        </if>
    </if>
    ORDER BY
    <choose>
        <when test="sortQuery != null and sortQuery.sortBy != null">
            <choose>
                <when test="sortQuery.sortBy == 'PRICE_ASC'">
                    p.price ASC
                </when>
                <when test="sortQuery.sortBy == 'PRICE_DESC'">
                    p.price DESC
                </when>
                <when test="sortQuery.sortBy == 'SALES_DESC'">
                    SUM(oi.quantity) DESC
                </when>
                <when test="sortQuery.sortBy == 'RATING_DESC'">
                    AVG(pr.rating) DESC
                </when>
                <when test="sortQuery.sortBy == 'NEWEST'">
                    p.create_time DESC
                </when>
                <when test="sortQuery.sortBy == 'NAME'">
                    p.name ASC
                </when>
                <otherwise>
                    p.update_time DESC
                </otherwise>
            </choose>
        </when>
        <otherwise>
            p.update_time DESC
        </otherwise>
    </choose>
    <if test="pagination != null">
        LIMIT #{pagination.offset}, #{pagination.limit}
    </if>
</select>

动态表名和列名

1. 动态表名查询

<!-- 动态表名查询 -->
<select id="queryDynamicTable" parameterType="DynamicTableQuery" resultType="java.util.Map">
    SELECT 
    <if test="selectColumns != null and selectColumns.size() > 0">
        <foreach collection="selectColumns" item="column" separator=",">
            ${column}
        </foreach>
    </if>
    <if test="selectColumns == null or selectColumns.size() == 0">
        *
    </if>
    FROM ${tableName}
    <where>
        <if test="conditions != null and conditions.size() > 0">
            <foreach collection="conditions" item="condition" separator=" AND ">
                ${condition.column} ${condition.operator} 
                <choose>
                    <when test="condition.operator == 'IN'">
                        <foreach collection="condition.values" item="value" open="(" close=")" separator=",">
                            #{value}
                        </foreach>
                    </when>
                    <when test="condition.operator == 'BETWEEN'">
                        #{condition.values[0]} AND #{condition.values[1]}
                    </when>
                    <when test="condition.operator == 'LIKE'">
                        CONCAT('%', #{condition.values[0]}, '%')
                    </when>
                    <otherwise>
                        #{condition.values[0]}
                    </otherwise>
                </choose>
            </foreach>
        </if>
    </where>
    <if test="orderBy != null and orderBy.size() > 0">
        ORDER BY
        <foreach collection="orderBy" item="order" separator=",">
            ${order.column} ${order.direction}
        </foreach>
    </if>
    <if test="limit != null">
        LIMIT #{limit}
    </if>
</select>

<!-- 动态批量插入 -->
<insert id="batchInsertDynamic" parameterType="DynamicInsertQuery">
    INSERT INTO ${tableName} (
        <foreach collection="columns" item="column" separator=",">
            ${column}
        </foreach>
    ) VALUES
    <foreach collection="rows" item="row" separator=",">
        (
            <foreach collection="row" item="value" separator=",">
                #{value}
            </foreach>
        )
    </foreach>
</insert>

<!-- 动态更新 -->
<update id="updateDynamic" parameterType="DynamicUpdateQuery">
    UPDATE ${tableName}
    <set>
        <foreach collection="updates" item="update" separator=",">
            ${update.column} = #{update.value}
        </foreach>
    </set>
    <where>
        <foreach collection="conditions" item="condition" separator=" AND ">
            ${condition.column} = #{condition.value}
        </foreach>
    </where>
</update>

2. 动态列名处理

<!-- 动态统计查询 -->
<select id="getDynamicStatistics" parameterType="StatisticsQuery" resultType="java.util.Map">
    SELECT 
        COUNT(*) as total_count,
        <if test="aggregations != null and aggregations.size() > 0">
            <foreach collection="aggregations" item="agg" separator=",">
                ${agg.function}(${agg.column}) as ${agg.alias}
            </foreach>
        </if>
        <if test="groupByColumns != null and groupByColumns.size() > 0">
            ,<foreach collection="groupByColumns" item="column" separator=",">
                ${column}
            </foreach>
        </if>
    FROM ${tableName}
    <where>
        <if test="filters != null and filters.size() > 0">
            <foreach collection="filters" item="filter" separator=" AND ">
                <choose>
                    <when test="filter.type == 'EQUALS'">
                        ${filter.column} = #{filter.value}
                    </when>
                    <when test="filter.type == 'GREATER_THAN'">
                        ${filter.column} > #{filter.value}
                    </when>
                    <when test="filter.type == 'LESS_THAN'">
                        ${filter.column} < #{filter.value}
                    </when>
                    <when test="filter.type == 'LIKE'">
                        ${filter.column} LIKE CONCAT('%', #{filter.value}, '%')
                    </when>
                    <when test="filter.type == 'IN'">
                        ${filter.column} IN
                        <foreach collection="filter.values" item="value" open="(" close=")" separator=",">
                            #{value}
                        </foreach>
                    </when>
                    <when test="filter.type == 'NULL'">
                        ${filter.column} IS NULL
                    </when>
                    <when test="filter.type == 'NOT_NULL'">
                        ${filter.column} IS NOT NULL
                    </when>
                </choose>
            </foreach>
        </if>
    </where>
    <if test="groupByColumns != null and groupByColumns.size() > 0">
        GROUP BY
        <foreach collection="groupByColumns" item="column" separator=",">
            ${column}
        </foreach>
    </if>
    <if test="havingConditions != null and havingConditions.size() > 0">
        HAVING
        <foreach collection="havingConditions" item="condition" separator=" AND ">
            ${condition.aggregateFunction}(${condition.column}) ${condition.operator} #{condition.value}
        </foreach>
    </if>
</select>

性能优化策略

避免N+1查询问题

1. 使用嵌套查询

<!-- 错误示例：会导致N+1查询 -->
<select id="getAllUsersWithOrders" resultType="User">
    SELECT * FROM users
</select>

<select id="getOrdersByUserId" parameterType="long" resultType="Order">
    SELECT * FROM orders WHERE user_id = #{userId}
</select>

<!-- 正确示例1：使用JOIN查询 -->
<select id="getUsersWithOrdersOptimized" resultMap="UserWithOrdersMap">
    SELECT 
        u.id as user_id,
        u.username,
        u.email,
        o.id as order_id,
        o.order_number,
        o.total_amount,
        o.status as order_status
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    ORDER BY u.id, o.create_time DESC
</select>

<resultMap id="UserWithOrdersMap" type="User">
    <id property="id" column="user_id"/>
    <result property="username" column="username"/>
    <result property="email" column="email"/>
    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="orderNumber" column="order_number"/>
        <result property="totalAmount" column="total_amount"/>
        <result property="status" column="order_status"/>
    </collection>
</resultMap>

<!-- 正确示例2：使用嵌套查询 -->
<select id="getUsersWithOrdersNested" resultMap="UserWithOrdersNestedMap">
    SELECT * FROM users
    <where>
        <if test="userIds != null and userIds.size() > 0">
            AND id IN
            <foreach collection="userIds" item="userId" open="(" close=")" separator=",">
                #{userId}
            </foreach>
        </if>
    </where>
</select>

<resultMap id="UserWithOrdersNestedMap" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="email" column="email"/>
    <collection property="orders" ofType="Order" select="selectOrdersByUserId" column="id"/>
</resultMap>

<select id="selectOrdersByUserId" parameterType="long" resultType="Order">
    SELECT * FROM orders 
    WHERE user_id = #{userId}
    ORDER BY create_time DESC
</select>

2. 批量查询优化

<!-- 批量查询用户订单 -->
<select id="getOrdersByUserIds" parameterType="java.util.List" resultType="Order">
    SELECT * FROM orders
    WHERE user_id IN
    <foreach collection="list" item="userId" open="(" close=")" separator=",">
        #{userId}
    </foreach>
    ORDER BY user_id, create_time DESC
</select>

<!-- 批量查询用户详情 -->
<select id="getUserDetailsByIds" parameterType="java.util.List" resultMap="UserDetailMap">
    SELECT 
        u.*,
        up.real_name,
        up.avatar,
        up.phone,
        COUNT(DISTINCT o.id) as order_count,
        SUM(o.total_amount) as total_amount
    FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id IN
    <foreach collection="list" item="userId" open="(" close=")" separator=",">
        #{userId}
    </foreach>
    GROUP BY u.id
</select>

合理使用索引

1. 索引友好的动态查询

<!-- 确保WHERE条件能使用索引 -->
<select id="findUsersWithIndex" parameterType="UserQuery" resultType="User">
    SELECT * FROM users
    <where>
        <!-- 单列索引查询 -->
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        
        <!-- 复合索引查询 - 遵循最左前缀原则 -->
        <if test="status != null and createTimeStart != null">
            AND status = #{status}
            AND create_time >= #{createTimeStart}
            <if test="createTimeEnd != null">
                AND create_time <= #{createTimeEnd}
            </if>
        </if>
        
        <!-- 避免在索引列上使用函数 -->
        <if test="username != null and username != ''">
            <!-- 错误写法：AND UPPER(username) = UPPER(#{username}) -->
            <!-- 正确写法：在应用层处理大小写 -->
            AND username = #{username}
        </if>
        
        <!-- 范围查询优化 -->
        <if test="ageRange != null">
            <choose>
                <when test="ageRange.min != null and ageRange.max != null">
                    AND age BETWEEN #{ageRange.min} AND #{ageRange.max}
                </when>
                <when test="ageRange.min != null">
                    AND age >= #{ageRange.min}
                </when>
                <when test="ageRange.max != null">
                    AND age <= #{ageRange.max}
                </when>
            </choose>
        </if>
    </where>
    ORDER BY 
    <choose>
        <when test="sortBy == 'create_time'">
            create_time DESC
        </when>
        <when test="sortBy == 'username'">
            username ASC
        </when>
        <otherwise>
            id DESC
        </otherwise>
    </choose>
</select>