idea的修改mysql语句问题

Question

用代码块功能插入代码，请勿粘贴截图。 不用代码块回答率下降 50%

<update id="updateByUserId" parameterType="com.easypan.entity.po.UserInfo">
    UPDATE user_info
    <trim prefix="SET" suffixOverrides=",">
        <if test="bean.nickName != null">
             nick_name = #{bean.nickName},</if>
        <if test="bean.email != null">
             email = #{bean.email},</if>
        <if test="bean.qqOpenId != null">
             qq_open_id = #{bean.qqOpenId},</if>
        <if test="bean.qqAvatar != null">
             qq_avatar = #{bean.qqAvatar},</if>
        <if test="bean.password != null">
             password = #{bean.password},</if>
        <if test="bean.joinTime != null">
             join_time = #{bean.joinTime},</if>
        <if test="bean.lastLoginTime != null">
             last_login_time = #{bean.lastLoginTime},</if>
        <if test="bean.status != null">
             status = #{bean.status},</if>
        <if test="bean.userSpace != null">
             user_space = #{bean.userSpace},</if>
        <if test="bean.totalSpace != null">
             total_space = #{bean.totalSpace},</if>
      </trim>
      where user_id=#{userId}
</update>

运行结果及详细报错内容

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 user_id='5975107971'' at line 3

The error may involve com.easypan.mappers.UserInfoMapper.updateByUserId-Inline

The error occurred while setting parameters

SQL: UPDATE user_info WHERE user_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 user_id='5975107971'' at line 3

; 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 user_id='5975107971'' at line 3

我的解答思路和尝试过的方法，不写自己思路的，回答率下降 60%

网上试过好几种方法都没用，不知道怎么解决了 问问大家 麻烦了

我想要达到的结果，如果你需要快速回答，请尝试 “付费悬赏”

Answer 1

引自免费微信小程序：皆我百晓生

看起来你的问题是由于 MyBatis 的 XML 映射文件中的标签位置错误导致的。在 <update> 标签内，<trim> 标签应该包含所有要更新的字段，而 where 子句应该紧跟在 set 子句之后，并且不需要放在任何其他标签内。

以下是修复后的 SQL 语句：

<update id="updateByUserId" parameterType="com.easypan.entity.po.UserInfo">
    UPDATE user_info
    <trim prefix="SET" suffixOverrides=",">
        <if test="bean.nickName != null">nick_name = #{bean.nickName}</if>
        <if test="bean.email != null">email = #{bean.email}</if>
        <if test="bean.qqOpenId != null">qq_open_id = #{bean.qqOpenId}</if>
        <if test="bean.qqAvatar != null">qq_avatar = #{bean.qqAvatar}</if>
        <if test="bean.password != null">password = #{bean.password}</if>
        <if test="bean.joinTime != null">join_time = #{bean.joinTime}</if>
        <if test="bean.lastLoginTime != null">last_login_time = #{bean.lastLoginTime}</if>
        <if test="bean.status != null">status = #{bean.status}</if>
        <if test="bean.userSpace != null">user_space = #{bean.userSpace}</if>
        <if test="bean.totalSpace != null">total_space = #{bean.totalSpace}</if>
    </trim>
    WHERE user_id = #{userId}
</update>

确保你在传递给这个方法的 UserInfo 对象中的属性值是正确的，并且 userId 参数也是有效的整数类型。这样应该可以解决 SQL 语法错误的问题。运行这段修正后的代码，看是否还有错误。如果仍然存在问题，请提供更多的上下文信息或者具体的异常堆栈跟踪，以便更深入地分析问题。