MyBatis+Mysql实现有则更新，无则插入

Posted on

MySQL实现

1
2
3
insert into books (`bookName`,`bookCounts`,`detail`) 
values ('MySQL',5,'快跑辣') 
on duplicate key update `bookCounts`=`bookCounts`+values(`bookCounts`),`detail` = values(`detail`);

错误

1
2
3
4
5
<insert id="addBook" parameterType="Books">
    insert into books (bookName,bookCounts,detail)
    values (#{bookName},#{bookCounts},#{detail})
    on duplicate key update bookCounts=bookCounts+values(#{bookCounts}), detail=values(#{detail}) ;
</insert>

日志：

1
2
==>  Preparing: insert into books (bookName,bookCounts,detail) values (?,?,?) on duplicate key update bookCounts=bookCounts+values(?), detail=values(?) ; 
==> Parameters: MySQL(String), 2(Integer), 2(String), 2(Integer), 2(String)

正确

1
2
3
4
5
6
<insert id="addBook" parameterType="Books">
    insert into books (bookName,bookCounts,detail)
    values (#{bookName},#{bookCounts},#{detail})
    // 下面不用#{}取数值
    on duplicate key update bookCounts=bookCounts+values(bookCounts), detail=values(detail) ;
</insert>

日志：

1
2
==>  Preparing: insert into books (bookName,bookCounts,detail) values (?,?,?) on duplicate key update bookCounts=bookCounts+values(bookCounts), detail=values(detail) ; 
==> Parameters: MySQL(String), 2(Integer), 2(String)