mybatis 批量插入语句和报错信息 Translating SQLException with SQL state '42000', error code '1064',

雨点打透心脏的1/2处 2022-04-12 12:49 332阅读 0赞

@Service
    int updateAllUserProject(List<PubUserProject> mapInfo)｛
    
    ｝
    @Controller
    List<PubUserProject> pubUserProjects = userProjectService.getUserProjectInfo(f);
    int updateAllCount = userProjectService.updateAllUserProject(pubUserProjects);
    
    
    
    Mapper
    <update id="updateAllUserProject" parameterType="java.util.List">
    		<foreach collection="list" separator=";" index="index" item="cus">
    			update pub_user_project SET
    			EXAMORDER = #{cus.examorder} 
    			<where>
    				ID = #{cus.id}
    			</where>
    		</foreach>
    	</update>

Translating SQLException with SQL state '42000', error code '1064',

1.有可能是语句使用了mysql关键字

2.conf-jdbc.properties 文件没开启批量修改

jdbc\_url=jdbc\\:mysql\\://\\:3306/xzff\_frm?useUnicode\\=true&characterEncoding\\=UTF-8&allowMultiQueries\\=true

![11][]

关键字列表：

<table> 
 <tbody> 
  <tr> 
   <td>ADD</td> 
   <td>ALL</td> 
   <td>ALTER</td> 
  </tr> 
  <tr> 
   <td>ANALYZE</td> 
   <td>AND</td> 
   <td>AS</td> 
  </tr> 
  <tr> 
   <td>ASC</td> 
   <td>ASENSITIVE</td> 
   <td>BEFORE</td> 
  </tr> 
  <tr> 
   <td>BETWEEN</td> 
   <td>BIGINT</td> 
   <td>BINARY</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>BOTH</td> 
   <td>BY</td> 
  </tr> 
  <tr> 
   <td>CALL</td> 
   <td>CASCADE</td> 
   <td>CASE</td> 
  </tr> 
  <tr> 
   <td>CHANGE</td> 
   <td>CHAR</td> 
   <td>CHARACTER</td> 
  </tr> 
  <tr> 
   <td>CHECK</td> 
   <td>COLLATE</td> 
   <td>COLUMN</td> 
  </tr> 
  <tr> 
   <td>CONDITION</td> 
   <td>CONNECTION</td> 
   <td>CONSTRAINT</td> 
  </tr> 
  <tr> 
   <td>CONTINUE</td> 
   <td>CONVERT</td> 
   <td>CREATE</td> 
  </tr> 
  <tr> 
   <td>CROSS</td> 
   <td>CURRENT_DATE</td> 
   <td>CURRENT_TIME</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIMESTAMP</td> 
   <td>CURRENT_USER</td> 
   <td>CURSOR</td> 
  </tr> 
  <tr> 
   <td>DATABASE</td> 
   <td>DATABASES</td> 
   <td>DAY_HOUR</td> 
  </tr> 
  <tr> 
   <td>DAY_MICROSECOND</td> 
   <td>DAY_MINUTE</td> 
   <td>DAY_SECOND</td> 
  </tr> 
  <tr> 
   <td>DEC</td> 
   <td>DECIMAL</td> 
   <td>DECLARE</td> 
  </tr> 
  <tr> 
   <td>DEFAULT</td> 
   <td>DELAYED</td> 
   <td>DELETE</td> 
  </tr> 
  <tr> 
   <td>DESC</td> 
   <td>DESCRIBE</td> 
   <td>DETERMINISTIC</td> 
  </tr> 
  <tr> 
   <td>DISTINCT</td> 
   <td>DISTINCTROW</td> 
   <td>DIV</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>DROP</td> 
   <td>DUAL</td> 
  </tr> 
  <tr> 
   <td>EACH</td> 
   <td>ELSE</td> 
   <td>ELSEIF</td> 
  </tr> 
  <tr> 
   <td>ENCLOSED</td> 
   <td>ESCAPED</td> 
   <td>EXISTS</td> 
  </tr> 
  <tr> 
   <td>EXIT</td> 
   <td>EXPLAIN</td> 
   <td>FALSE</td> 
  </tr> 
  <tr> 
   <td>FETCH</td> 
   <td>FLOAT</td> 
   <td>FLOAT4</td> 
  </tr> 
  <tr> 
   <td>FLOAT8</td> 
   <td>FOR</td> 
   <td>FORCE</td> 
  </tr> 
  <tr> 
   <td>FOREIGN</td> 
   <td>FROM</td> 
   <td>FULLTEXT</td> 
  </tr> 
  <tr> 
   <td>GOTO</td> 
   <td>GRANT</td> 
   <td>GROUP</td> 
  </tr> 
  <tr> 
   <td>HAVING</td> 
   <td>HIGH_PRIORITY</td> 
   <td>HOUR_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td>HOUR_MINUTE</td> 
   <td>HOUR_SECOND</td> 
   <td>IF</td> 
  </tr> 
  <tr> 
   <td>IGNORE</td> 
   <td>IN</td> 
   <td>INDEX</td> 
  </tr> 
  <tr> 
   <td>INFILE</td> 
   <td>INNER</td> 
   <td>INOUT</td> 
  </tr> 
  <tr> 
   <td>INSENSITIVE</td> 
   <td>INSERT</td> 
   <td>INT</td> 
  </tr> 
  <tr> 
   <td>INT1</td> 
   <td>INT2</td> 
   <td>INT3</td> 
  </tr> 
  <tr> 
   <td>INT4</td> 
   <td>INT8</td> 
   <td>INTEGER</td> 
  </tr> 
  <tr> 
   <td>INTERVAL</td> 
   <td>INTO</td> 
   <td>IS</td> 
  </tr> 
  <tr> 
   <td>ITERATE</td> 
   <td>JOIN</td> 
   <td>KEY</td> 
  </tr> 
  <tr> 
   <td>KEYS</td> 
   <td>KILL</td> 
   <td>LABEL</td> 
  </tr> 
  <tr> 
   <td>LEADING</td> 
   <td>LEAVE</td> 
   <td>LEFT</td> 
  </tr> 
  <tr> 
   <td>LIKE</td> 
   <td>LIMIT</td> 
   <td>LINEAR</td> 
  </tr> 
  <tr> 
   <td>LINES</td> 
   <td>LOAD</td> 
   <td>LOCALTIME</td> 
  </tr> 
  <tr> 
   <td>LOCALTIMESTAMP</td> 
   <td>LOCK</td> 
   <td>LONG</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>LONGTEXT</td> 
   <td>LOOP</td> 
  </tr> 
  <tr> 
   <td>LOW_PRIORITY</td> 
   <td>MATCH</td> 
   <td>MEDIUMBLOB</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>MEDIUMTEXT</td> 
   <td>MIDDLEINT</td> 
  </tr> 
  <tr> 
   <td>MINUTE_MICROSECOND</td> 
   <td>MINUTE_SECOND</td> 
   <td>MOD</td> 
  </tr> 
  <tr> 
   <td>MODIFIES</td> 
   <td>NATURAL</td> 
   <td>NOT</td> 
  </tr> 
  <tr> 
   <td>NO_WRITE_TO_BINLOG</td> 
   <td>NULL</td> 
   <td>NUMERIC</td> 
  </tr> 
  <tr> 
   <td>ON</td> 
   <td>OPTIMIZE</td> 
   <td>OPTION</td> 
  </tr> 
  <tr> 
   <td>OPTIONALLY</td> 
   <td>OR</td> 
   <td>ORDER</td> 
  </tr> 
  <tr> 
   <td>OUT</td> 
   <td>OUTER</td> 
   <td>OUTFILE</td> 
  </tr> 
  <tr> 
   <td>PRECISION</td> 
   <td>PRIMARY</td> 
   <td>PROCEDURE</td> 
  </tr> 
  <tr> 
   <td>PURGE</td> 
   <td>RAID0</td> 
   <td>RANGE</td> 
  </tr> 
  <tr> 
   <td>READ</td> 
   <td>READS</td> 
   <td>REAL</td> 
  </tr> 
  <tr> 
   <td>REFERENCES</td> 
   <td>REGEXP</td> 
   <td>RELEASE</td> 
  </tr> 
  <tr> 
   <td>RENAME</td> 
   <td>REPEAT</td> 
   <td>REPLACE</td> 
  </tr> 
  <tr> 
   <td>REQUIRE</td> 
   <td>RESTRICT</td> 
   <td>RETURN</td> 
  </tr> 
  <tr> 
   <td>REVOKE</td> 
   <td>RIGHT</td> 
   <td>RLIKE</td> 
  </tr> 
  <tr> 
   <td>SCHEMA</td> 
   <td>SCHEMAS</td> 
   <td>SECOND_MICROSECOND</td> 
  </tr> 
  <tr> 
   <td>SELECT</td> 
   <td>SENSITIVE</td> 
   <td>SEPARATOR</td> 
  </tr> 
  <tr> 
   <td>SET</td> 
   <td>SHOW</td> 
   <td>SMALLINT</td> 
  </tr> 
  <tr> 
   <td>SPATIAL</td> 
   <td>SPECIFIC</td> 
   <td>SQL</td> 
  </tr> 
  <tr> 
   <td>SQLEXCEPTION</td> 
   <td>SQLSTATE</td> 
   <td>SQLWARNING</td> 
  </tr> 
  <tr> 
   <td>SQL_BIG_RESULT</td> 
   <td>SQL_CALC_FOUND_ROWS</td> 
   <td>SQL_SMALL_RESULT</td> 
  </tr> 
  <tr> 
   <td>SSL</td> 
   <td>STARTING</td> 
   <td>STRAIGHT_JOIN</td> 
  </tr> 
  <tr> 
   <td>TABLE</td> 
   <td>TERMINATED</td> 
   <td>THEN</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>TINYINT</td> 
   <td>TINYTEXT</td> 
  </tr> 
  <tr> 
   <td>TO</td> 
   <td>TRAILING</td> 
   <td>TRIGGER</td> 
  </tr> 
  <tr> 
   <td>TRUE</td> 
   <td>UNDO</td> 
   <td>UNION</td> 
  </tr> 
  <tr> 
   <td>UNIQUE</td> 
   <td>UNLOCK</td> 
   <td>UNSIGNED</td> 
  </tr> 
  <tr> 
   <td>UPDATE</td> 
   <td>USAGE</td> 
   <td>USE</td> 
  </tr> 
  <tr> 
   <td>USING</td> 
   <td>UTC_DATE</td> 
   <td>UTC_TIME</td> 
  </tr> 
  <tr> 
   <td>UTC_TIMESTAMP</td> 
   <td>VALUES</td> 
   <td>VARBINARY</td> 
  </tr> 
  <tr> 
   <td>VARCHAR</td> 
   <td>VARCHARACTER</td> 
   <td>VARYING</td> 
  </tr> 
  <tr> 
   <td>WHEN</td> 
   <td>WHERE</td> 
   <td>WHILE</td> 
  </tr> 
  <tr> 
   <td>WITH</td> 
   <td>WRITE</td> 
   <td>X509</td> 
  </tr> 
  <tr> 
   <td>XOR</td> 
   <td>YEAR_MONTH</td> 
   <td>ZEROFILL</td> 
  </tr> 
 </tbody> 
</table>

[11]: /images/20220412/a37efcc4196b429d907b725c4e186d26.png