sql insert语法报错?

Question

写了一个简单的插入语句，但是一直报错：

### 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 ')values( 'hello' at line 11 ### The error may exist in file [D:\mes-master\mes-master\ruoyi-system\target\classes\mapper\system\pnMapperRuKu.xml] ### The error may involve com.ruoyi.system.mapper.pnMapperRuKu.insertPnAssertRukuDetails-Inline ### The error occurred while setting parameters ### SQL: insert into pnAssertRukuDetails ( storageRoom, )values( ?, ) ### 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 ')values( 'hello' at line 11 ; 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 ')values( 'hello' at line 11

调用方法：

@Override
    public int insertPnCode(PnModelInOutInventory pnModelInOutInventory) {
//        pnMapperRuKu.insertPnAssertRuku(pnModelInOutInventory);
        return pnMapperRuKu.insertPnAssertRukuDetails(pnModelInOutInventory);
    }


mapper.xml----
<insert id="insertPnAssertRukuDetails" parameterType="com.ruoyi.system.domain.PnModelInOutInventory">
        insert into pnAssertRukuDetails (
     <if test="parentPnAssertRukuId != null and parentPnAssertRukuId != '' ">parent_pnAssertRuku_id,</if>
     <if test="materialInformation != null and materialInformation != '' ">materialInformation,</if>
     <if test="numbersRuKu != null and numbersRuKu != '' ">numbersRuKu,</if>
     <if test="qualified != null and qualified != '' ">qualified,</if>
     <if test="unit != null and unit != '' ">unit,</if>
     <if test="storageRoom != null and storageRoom != ''">storageRoom,</if>
     <if test="sn != null and sn != ''">sn,</if>
     <if test="remark != null and remark != ''">remark,</if>
     <if test="batchNumber != null and batchNumber != ''">batchNumber,</if>
    )values(
     <if test="parentPnAssertRukuId != null and parentPnAssertRukuId != '' ">#{parentPnAssertRukuId},</if>
     <if test="materialInformation != null and materialInformation != '' ">#{materialInformation},</if>
     <if test="numbersRuKu != null and numbersRuKu != '' ">#{numbersRuKu},</if>
     <if test="qualified != null and qualified != '' ">#{qualified},</if>
     <if test="unit != null and unit != '' ">#{unit},</if>
     <if test="storageRoom != null and storageRoom != ''">#{storageRoom},</if>
     <if test="sn != null and sn != ''">#{sn},</if>
     <if test="remark != null and remark != ''">#{remark},</if>
     <if test="batchNumber != null and batchNumber != ''">#{batchNumber},</if>
    )
</insert>

我对比了Mapper.xml中的语法，包括调整英文符号，调整英文的括号；都没有作用；

有么有大佬帮忙看下，问题出在哪里？？

Answer 1

问题点在于: 类似如下示例SQL

name后面的 --> , <--

insert newschema.new_table (name,) values ('hello',);

Answer 2

你直接用TitanIDE的navicat模板试试，里面提供智能助手辅助，你看我文章就知道多好用了

这个错误是由于SQL语法不正确引起的。根据错误信息，问题出在insert语句中。具体来说，insert into pnAssertRukuDetails (storageRoom, ) values( ?, )这条语句中存在以下几个问题：

1. 列名后有多余的逗号：在storageRoom,后面多了一个逗号，而列名部分不能有多余的逗号。
2. values部分也有多余的逗号：values( ?, )中的值列表中多了一个逗号，这也是不合法的SQL语法。

修复方式：

确保insert语句的列名部分和values部分的格式正确，删除多余的逗号。例如，如果你只插入storageRoom这个列，可以修改成如下的SQL语句：

insert into pnAssertRukuDetails (storageRoom) values (?)

如果有其他列要插入，确保每个列名和对应的值之间用逗号正确分隔，但不要在最后一个列名或值之后添加逗号。