MySQL ON DUPLICATE KEY UPDATE为Mysql特有语法
ON DUPLICATE KEY UPDATE 用法与说明
Mysql中INSERT ... ON DUPLICATE KEY UPDATE的实践
INSERT ... ON DUPLICATE KEY UPDATE语句
扩展:
INSERT IF NOT EXIST
使用范例:
数据库当前存在id为20的记录,这时insert id = 20 的记录会报错:
INSERT INTO `book`.`book_test` (`id`, `book_name`, `isbn`, `author`, `price`, `publisher`, `publish_date`, `is_sale`) VALUES ('20', '博弈论', '56', '刘一手', '52.00', '测试出版社', '-12-06', '1');
[SQL]INSERT INTO `book`.`book_test` (`id`, `book_name`, `isbn`, `author`, `price`, `publisher`, `publish_date`, `is_sale`) VALUES ('20', '博弈论', '56', '刘一手', '52.00', '测试出版社', '-12-06', '1');[Err] 1062 - Duplicate entry '20' for key 'PRIMARY'
SQL的改进:存在则更新,不存在则插入
范例:
INSERT INTO `book`.`book_test` (`id`,`book_name`,`isbn`,`author`,`price`,`publisher`,`publish_date`,`is_sale`)VALUES('20','博弈论','56','刘一手','100.00','测试出版社','-12-06','1') ON DUPLICATE KEY UPDATE book_name =VALUES(book_name),isbn =VALUES(isbn),author =VALUES(author),price =VALUES(price),publisher =VALUES(publisher),publish_date =VALUES(publish_date),is_sale =VALUES(is_sale)
执行结果:
[SQL]INSERT INTO `book`.`book_test` (...VALUES(is_sale)受影响的行: 2时间: 0.001s
可以看到原始数据进行了更新:
Mybatis 中使用范例:
======>> 其中 id,system_id为联合索引
<insert id="insertOrUpdate" parameterType="pany.xxx.model.MyTestBean">insert into ${tableName}<trim prefix="(" suffix=")" suffixOverrides=","><if test="systemId != null">system_id,</if><if test="id != null">id,</if><if test="order != null">order,</if><if test="desc != null">desc,</if></trim><trim prefix="values (" suffix=")" suffixOverrides=","><if test="systemId != null">#{systemId,jdbcType=INTEGER},</if><if test="id != null">#{id,jdbcType=BIGINT},</if><if test="order != null">#{order,jdbcType=VARCHAR},</if><if test="desc != null">#{desc,jdbcType=LONGVARCHAR},</if></trim>ON DUPLICATE KEY UPDATE<trim suffixOverrides=","><if test="order != null">order=VALUES(order),</if><if test="desc != null">desc=VALUES(desc),</if></trim></insert>