mybatis mysql set命令_Mybatis 批量更新 set 多个字段你的时候报错 mysql

日志如下：

2015-10-15 10:18:00,070 DEBUG  - JDBC Connection [jdbc:mysql://localhost:3306/section?useUnicode=true&characterEncoding=UTF-8, UserName=root@localhost, MySQL-AB JDBC Driver] will not be managed by Spring

2015-10-15 10:18:00,070 DEBUG  - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76a2f910] was not registered for synchronization because synchronization is not active

2015-10-15 10:18:00,073 DEBUG  - ==>  Executing: update b_email_msg_remind SET send_status = ?, send_email_code='abc@abc.abc' where 1 = 1 AND email_remind_id = ? and send_status = 0 ; update b_email_msg_remind SET send_status = ?, send_email_code='abc@abc.abc' where 1 = 1 AND email_remind_id = ? and send_status = 0

2015-10-15 10:18:00,073 DEBUG  - ==> Parameters: 1(Integer), 234746e8-4cab-444c-86ee-ea73c57cb7de(String), 1(Integer), 48d4a578-141e-421c-9eed-0c26de4b8f48(String)

2015-10-15 10:18:00,075 DEBUG  - Closing no transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76a2f910]

org.springframework.jdbc.BadSqlGrammarException:

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'update b_email_msg_remind

SET send_status = 1, send_email_code='abc@abc.abc'' at line 6

### The error may involve com.hhsoft.sectionservice.model.persistence.EmailMapper.updateEmailTasks-Inline

### The error occurred while setting parameters

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'update b_email_msg_remind

SET send_status = 1, send_email_code='abc@abc.abc'' at line 6

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'update b_email_msg_remind

SET send_status = 1, send_email_code='abc@abc.abc'' at line 6

Mapper.xml配置

update b_email_msg_remind

SET send_status = #{item.sendStatus}, send_email_code='abc@abc.abc'

where email_remind_id = #{item.emailRemindId} and send_status = 0

原因分析：

目前定位如果SET 只修改一个字段，则正常执行，或者SET 两个字段但是foreach只循环一次，也可以正常执行

由于时间较紧，暂时没有解决此问题，而是找了替代方法，并且效率更高

update b_email_msg_remind

when email_remind_id = #{item.emailRemindId} then #{item.sendStatus}

when email_remind_id = #{item.emailRemindId} then #{item.sendTime}

这样配置生成的sql 是

update b_email_msg_remind

set

send_status =case when email_remind_id = ? then ? when email_remind_id = ? then ? end,

send_email_code =case when email_remind_id = ? then 'abc@abc.abc' when email_remind_id = ? then 'abc@abc.abc' end