MyBatis XML Update使用set标签抛出BadSqlGrammarException问题求助

在MyBatis XML中编写更新查询时，使用<set>标签抛出了org.springframework.jdbc.BadSqlGrammarException异常，移除<set>标签的写法也未解决问题。日志显示生成的SQL缺少SET部分导致语法错误，请问正确写法是什么？

原查询代码 #

<update id="updateRecordingVideoStatus">
    UPDATE
        game_record_metadata
        <set>
            <if test="modifiedVideoStatus = null">
                    status = #{originalVideoStatus}
            </if>
            <if test="modifiedVideoStatus != null">
                    status = #{modifiedVideoStatus}
            </if>
        </set>
    WHERE id = #{gameRecordMetadataId}
    AND game_id = #{gameId}
</update>

尝试的写法 #

<if test="modifiedVideoStatus = null">
       SET status = #{originalVideoStatus}
</if>
<if test="modifiedVideoStatus != null">
       SET status = #{modifiedVideoStatus}
</if>

错误日志 #

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 2
        AND game_id = 204' at line 5
### The error may exist in file [/Users/asd/admin-api/build/resources/main/mybatis/rel/game_recording.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE             game_record_metadata                                     WHERE id = ?         AND game_id = ?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 2
        AND game_id = 204' at line 5
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 2
        AND game_id = 204' at line 5

问题根源 #

你遇到的核心问题是MyBatis的条件判断语法错误：在<if>标签的test属性中，判断null需要用==而非=，你写的modifiedVideoStatus = null是赋值操作，不是逻辑判断，导致两个<if>分支都不生效。此时<set>标签内部无有效语句，MyBatis就不会生成SET关键字，最终SQL缺失关键部分引发语法错误。

修正后的代码 #

将判断条件中的=替换为==即可，推荐使用<set>标签的写法（自动处理字段间的逗号，更稳妥）：

<update id="updateRecordingVideoStatus">
    UPDATE game_record_metadata
    <set>
        <if test="modifiedVideoStatus == null">
            status = #{originalVideoStatus}
        </if>
        <if test="modifiedVideoStatus != null">
            status = #{modifiedVideoStatus}
        </if>
    </set>
    WHERE id = #{gameRecordMetadataId}
    AND game_id = #{gameId}
</update>

替代写法（不用<set>标签） #

如果坚持手动写SET，同样要修正判断条件，确保至少一个分支会生成SET语句：

<update id="updateRecordingVideoStatus">
    UPDATE game_record_metadata
    <if test="modifiedVideoStatus == null">
        SET status = #{originalVideoStatus}
    </if>
    <if test="modifiedVideoStatus != null">
        SET status = #{modifiedVideoStatus}
    </if>
    WHERE id = #{gameRecordMetadataId}
    AND game_id = #{gameId}
</update>

内容的提问来源于stack exchange，提问作者footlessbird