mysql中on duplicate key update的用法及说明
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1、应用场景</a></li><li><a href="#_label1">2、基础使用语法</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">2.1 假设此时我们表中没有数据</a></li><li><a href="#_lab2_1_1">2.2 有数据后再次执行</a></li></ul><li><a href="#_label2">3、批量插入</a></li><ul class="second_class_ul"></ul><li><a href="#_label3">4、Mybatis中的写法</a></li><ul class="second_class_ul"></ul><li><a href="#_label4">5、情景模拟</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>1、应用场景</h2><p>日常开发中，对于一个数据想做到存在即更新，不存在则新增，通常的做法是先查询数据库中是否存在对应的数据，如果存在就使用更新的方法，不存在就使用新增的方法</p>
<p>如果是单个数据，倒也没什么问题，但如果是批量数据的话，会消耗大量的资源来进行查询操作，这样就得不偿失了。</p>
<p>这种情况我们可以使用mysql提供的 on duplicate key update 来进行操作。</p>
<p class="maodian"><a name="_label1"></a></p><h2>2、基础使用语法</h2>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>2.1 假设此时我们表中没有数据</h3>
<p>执行语句（为了展示效果使用用法2）</p>
<div class="jb51code"><pre class="brush:sql;">#用法1：使用values来获取值（推荐，因为插入多个的时候可以用）
INSERT INTO `test` ( id, name )
VALUES( 1, '晓明' )
ON DUPLICATE KEY UPDATE
id = VALUES(id),
name = VALUES(name)
#用法2：直接使用值
INSERT INTO `user` ( id, name )
VALUES( 1, '晓明')
ON DUPLICATE KEY UPDATE
id = '123',
name = 'xiaoming'
</pre></div>
<ul><li>执行结果</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122209461267.png" /></p>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>2.2 有数据后再次执行</h3>
<p><span>执行相同sql语句，执行结果</span></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122209461269.png" /></p>
<p><span><span>这里就是关键：</span></span></p>
<p><span><span>我们可以看到变动为2，此时就是说明原数据进行了更新，更新内容为下面UPDATE中设置的字段值 ，id也可变化，数据库中数据如下</span></span></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122209461225.png" /></p>
<p><span>Q：我们再次执行一次这个语句，结果是什么样子呢？</span></p>
<p>A：自然是判断出该表中无此数据，新增一条额外的新数据</p>
<ul><li>sql结果</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122209461288.png" /></p>
<p><strong>验证得到结论：</strong></p>
<p>其实就是会自动检测是否存在Duplicate entry，如果存在values后面的值就会自动更改，不存在则插入 </p>
<p class="maodian"><a name="_label2"></a></p><h2>3、批量插入</h2>
<ul><li><span>执行语句</span></li></ul>
<div class="jb51code"><pre class="brush:sql;">INSERT INTO test(`id`,`name`,`address`)
VALUES('4','修改10','北京'),
('1', '晓明',1)
ON DUPLICATE KEY UPDATE
name=VALUES(name),address=VALUES(address);</pre></div>
<ul><li>sql结果</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122209461268.png" /></p>
<p>id为1和id为4的分别被修改和新增，可以同时进行两种操作类型</p>
<p class="maodian"><a name="_label3"></a></p><h2>4、Mybatis中的写法</h2>
<ul><li><span>单独插入</span></li></ul>
<div class="jb51code"><pre class="brush:sql;"><insert id="insertUser" parameterType="com.test.User">
INSERT INTO user(
id,
name,
gender,
birthday,
address)
VALUES
(#{id},
#{name},
#{gender},
#{birthday},
#{address})
ON DUPLICATE KEY UPDATE
id = VALUES(id),
name = VALUES(name),
gender = VALUES(gender)
birthday = VALUES(birthday),
address = VALUES(address)
</insert>
</pre></div>
<ul><li>批量插入</li></ul>
<div class="jb51code"><pre class="brush:sql;"><insert id="insertUser" parameterType="java.util.List">
INSERT INTO user(
id,
name,
gender,
birthday,
address)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},
#{item.name},
#{item.gender},
#{item.birthday},
#{item.address})
</foreach>
ON DUPLICATE KEY UPDATE
id = VALUES(id),
name = VALUES(name),
gender = VALUES(gender)
birthday = VALUES(birthday),
address = VALUES(address)
</insert>
</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>5、情景模拟</h2>
<p>插入失败提示如下</p>
<div class="jb51code"><pre class="brush:sql;">ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'</pre></div>
<p>如果数据库中已有某条数据，以下的两条语句可等同：</p>
<div class="jb51code"><pre class="brush:sql;">INSERT INTO tablename (id, data) VALUES (1, 10)
ON DUPLICATE KEY UPDATE data=data+10;</pre></div>
<div class="jb51code"><pre class="brush:sql;">UPDATE tablename SET data=data+10 WHERE id=1;</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>总结</h2>
<p>以上为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>
頁:
[1]