MyBatis 的动态 SQL 是指在 MyBatis 的映射文件中，根据不同的条件动态生成 SQL 语句。这样可以简化代码，提高可读性和可维护性。以下是八大动态 SQL 的使用场景及示例：

1.if

场景：当某个条件成立时，需要执行一段 SQL 语句。

<select id="findUsers" resultType="User">
  SELECT * FROM user
  <where>
    <if test="username != null and username != ''">
      AND username = #{username}
    </if>
    <if test="age != null">
      AND age = #{age}
    </if>
  </where>
</select>

2.choose、when、otherwise

场景：类似于 Java 中的 switch-case 语句，根据条件选择不同的 SQL 语句。

<select id="findUsers" resultType="User">
  SELECT * FROM user
  <where>
    <choose>
      <when test="username != null and username != ''">
        AND username = #{username}
      </when>
      <when test="age != null">
        AND age = #{age}
      </when>
      <otherwise>
        AND email = #{email}
      </otherwise>
    </choose>
  </where>
</select>

3.trim、where、set

场景：用于自动处理 SQL 语句中的多余逗号和 AND/OR 关键字。

<update id="updateUser" parameterType="User">
  UPDATE user
  <set>
    <if test="username != null and username != ''">
      username = #{username},
    </if>
    <if test="age != null">
      age = #{age},
    </if>
    <if test="email != null and email != ''">
      email = #{email},
    </if>
  </set>
  <where>
    <trim prefix="AND" suffixOverrides=",">
      <if test="username != null and username != ''">
        username = #{username},
      </if>
      <if test="age != null">
        age = #{age},
      </if>
      <if test="email != null and email != ''">
        email = #{email},
      </if>
    </trim>
  </where>
</update>

4.foreach

场景：用于遍历集合，生成 IN 语句。

<select id="findUsersByIds" resultType="User">
  SELECT * FROM user
  <where>
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
      id = #{id}
    </foreach>
  </where>
</select>

5.bind

场景：用于创建一个变量，可以在后续的 SQL 语句中使用。

<select id="findUsersByName" resultType="User">
  SELECT * FROM user
  <where>
    <bind name="pattern" value="'%' + username + '%'"/>
    username LIKE #{pattern}
  </where>
</select>

6.when

场景：类似于 Java 中的 switch-case 语句，根据条件选择不同的 SQL 片段。

<update id="updateUser" parameterType="User">
  UPDATE user
  <set>
    <if test="username != null and username != ''">
      username = #{username},
    </if>
    <if test="age != null">
      age = #{age},
    </if>
    <if test="email != null and email != ''">
      email = #{email},
    </if>
  </set>
  <when test="username != null and username != ''">
    AND username = #{username}
  </when>
  <when test="age != null">
    AND age = #{age}
  </when>
  <when test="email != null and email != ''">
    AND email = #{email}
  </when>
</update>

7.choose、when、otherwise、bind

场景：与第 2 点类似，但可以绑定变量。

<select id="findUsersByNameOrAge" resultType="User">
  SELECT * FROM user
  <where>
    <choose>
      <when test="name != null and name != ''">
        AND name = #{name}
      </when>
      <when test="age != null">
        AND age = #{age}
      </when>
      <otherwise>
        AND email = #{email}
      </otherwise>
    </choose>
  </where>
</select>

8.trim、where、set、choose、when、otherwise、bind

场景：与第 3、4、5、6、7 点类似，但可以自动处理多余的逗号和 AND/OR 关键字。

<update id="updateUser" parameterType="User">
  UPDATE user
  <set>
    <trim prefix="AND" suffixOverrides=",">
      <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>
    </trim>
  </set>
  <where>
    <choose>
      <when test="name != null and name != ''">
        AND name = #{name}
      </when>
      <when test="age != null">
        AND age = #{age}
      </when>
      <when test="email != null and email != ''">
        AND email = #{email}
      </when>
      <otherwise>
        AND id = #{id}
      </otherwise>
    </choose>
  </where>
</update>