mybatis动态sql报错记录

报错代码

### 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 = 1' at line 3
### The error may exist in file [D:\development\code\SpringBootProject\Sky\sky-take-out\sky-server\target\classes\mapper\EmployeeMapper.xml]
### The error may involve com.sky.mapper.EmployeeMapper.updateEmployee-Inline
### The error occurred while setting parameters
### SQL: update employee                    where 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 = 1' 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 id = 1' at line 3] with root 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 = 1' at line 3

错误原因
SQL语法错误，错误提示指出问题出现在where id = 1附近，可能是SQL语句结构不正确或关键字使用错误。

原代码

<update id="updateEmployee" parameterType="Employee">
        update employee
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="username != null and username != ''">
                username = #{username},
            </if>
            <if test="password != null and password != ''">
                password = #{password},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="idNumber != null and idNumber != ''">
                id_number = #{idNumber},
            </if>
            <if test="createUser != null">
                create_user = #{createUser},
            </if>
            <if test="updateUser != null">
                update_user = #{updateUser},
            </if>
            <if test="status != null and status != ''">
                status = #{status},
            </if>
            <if test="createTime != null">
                create_time = #{create_time},
            </if>
            <if test="updateTime != null">
                update_time = #{update_time},
            </if>
        </set>
        where id = #{id}
    </update>

简化一下出错的代码部分:

<update id="updateEmployee" parameterType="Employee">
        update employee 
        <set>
            <if test="status != null and status != ''">
                status = #{status},
            </if>
        </set>
        where id = #{id}
    </update>

对象字段是Integer不是String

解决方案

• 去掉后半段判断and status != ''
  
  动态sql就正常了

原因分析：

• 空字符串和空格和null的分析

在 SQL 和大多数编程语言中（包括 Java），空字符串用两个单引号之间没有字符来表示，即 ''。而 ' '实际上表示一个包含一个空格的字符串，而不是一个空字符串。

原始代码将检查** status 属性是否不为 null 且不是空字符串**。如果 status 是一个 String 类型的字段，并且你想要确保它包含一些实际的值（而不是 null 或空字符串），那么才应该使用上述的 test 条件。

但是我们使用的是Integer限定而不是用的String,所以并不用检查空字符串，是我多余了才导致sql报错从而没有出现status的传参。