通俗易懂的on DUPLICATE key update用法

<div id="article_content" class="article_content clearfix">
                                    <link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-3019150162.css">
                                        <div id="content_views" class="markdown_views">
                    <!-- flowchart 箭头图标 勿删 -->
                    <svg xmlns="http://www.w3.org/2000/svg" style="display: none;">
                        <path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path>
                    </svg>
                                            <div class="markdown_views" id="content_views">
<h1 id="on-duplicate-key-update作用"><a name="t0"></a><a></a>ON DUPLICATE KEY UPDATE作用</h1>
</div><p><strong>先声明一点，ON DUPLICATE KEY UPDATE为Mysql特有语法，这是个坑</strong> <br>
 语句的作用，当insert已经存在的记录时，执行Update</p>
<h1 id="用法"><a name="t1"></a><a></a>用法</h1>
<p><strong>什么意思？举个例子：</strong> <br>
 user_admin_t表中有一条数据如下</p>
<p><img title="" alt="user_admin_t" src="https://img-blog.csdn.net/20171105150445430?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast"></p>
<p>表中的主键为id，现要插入一条数据，id为‘1’，password为‘第一次插入的密码’，正常写法为：</p>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;"><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1'</span>,<span class="hljs-string">'第一次插入的密码'</span>) </span><div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li></ul></pre>
<p>执行后刷新表数据，我们来看表中内容</p>
<p><img title="" alt="执行insert后" src="https://img-blog.csdn.net/20171105150809525?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast"></p>
<p>此时表中数据增加了一条主键’_id’为‘1’，‘password’为‘第一次插入的密码’的记录，当我们再次执行插入语句时，会发生什么呢？</p>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;"><span class="hljs-comment">-- 执行</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1'</span>,<span class="hljs-string">'第一次插入的密码'</span>) </span><div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li></ul></pre>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">[SQL]<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1'</span>,<span class="hljs-string">'第一次插入的密码'</span>) 

[Err] <span class="hljs-number">1062</span> - Duplicate entry <span class="hljs-string">'1'</span> <span class="hljs-keyword">for</span> <span class="hljs-keyword">key</span> <span class="hljs-string">'PRIMARY'</span>
</span><div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li></ul></pre>
<p>Mysql告诉我们，我们的主键冲突了，看到这里我们是不是可以改变一下思路，当插入已存在主键的记录时，将插入操作变为修改：</p>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;"><span class="hljs-comment">-- 在原sql后面增加 ON DUPLICATE KEY UPDATE </span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1'</span>,<span class="hljs-string">'第一次插入的密码'</span>) 
<span class="hljs-keyword">ON</span> DUPLICATE <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">UPDATE</span> 
_id = <span class="hljs-string">'UpId'</span>,
password = <span class="hljs-string">'upPassword'</span>;</span><div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li></ul></pre>
<p>我们再一次执行：</p>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">[SQL]<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'1'</span>,<span class="hljs-string">'第一次插入的密码'</span>) 
<span class="hljs-keyword">ON</span> DUPLICATE <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">UPDATE</span> 
_id = <span class="hljs-string">'UpId'</span>,
password = <span class="hljs-string">'upPassword'</span>;</span>
受影响的行: 2
时间: 0.131s<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li></ul></pre>
<p>可以看到 受影响的行为2，这是因为将原有的记录修改了，而不是执行插入，看一下表中数据：</p>
<p><img title="" alt="DUPLICATE后" src="https://img-blog.csdn.net/20171105151541091?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast"></p>
<p>原本‘id’为‘1’的记录，改为了‘UpId’，‘password’也变为了‘upPassword’，很好的解决了重复插入问题</p>
<h1 id="扩展"><a name="t2"></a><a></a>扩展</h1>
<p>当插入多条数据，其中不只有表中已存在的，还有需要新插入的数据，Mysql会如何执行呢？会不会报错呢？</p>
<p>其实Mysql远比我们想象的强大，他会智能的选择更新还是插入，我们尝试一下：</p>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;"><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> 
(<span class="hljs-string">'1'</span>,<span class="hljs-string">'第一次插入的密码'</span>) ,
(<span class="hljs-string">'2'</span>,<span class="hljs-string">'第二条记录'</span>)
<span class="hljs-keyword">ON</span> DUPLICATE <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">UPDATE</span> 
_id = <span class="hljs-string">'UpId'</span>,
password = <span class="hljs-string">'upPassword'</span>;</span><div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li></ul></pre>
<p>运行sql</p>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">[SQL]<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> 
(<span class="hljs-string">'1'</span>,<span class="hljs-string">'第一次插入的密码'</span>) ,
(<span class="hljs-string">'2'</span>,<span class="hljs-string">'第二条记录'</span>)
<span class="hljs-keyword">ON</span> DUPLICATE <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">UPDATE</span> 
_id = <span class="hljs-string">'UpId'</span>,
password = <span class="hljs-string">'upPassword'</span>;</span>
受影响的行: 3
时间: 0.045s

<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li><li style="color: rgb(153, 153, 153);">11</li></ul></pre>
<p>Mysql执行了一次修改，一次插入，表中数据为：</p>
<p><img title="" alt="多记录插入" src="https://img-blog.csdn.net/20171105153119038?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast"></p>
<h1 id="values修改"><a name="t3"></a><a></a>VALUES修改</h1>
<p>那么问题又来了，有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值，如果我想要分别给不同的记录插入不同的值怎么办呢？</p>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;"><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> 
(<span class="hljs-string">'1'</span>,<span class="hljs-string">'多条插入1'</span>) ,
(<span class="hljs-string">'UpId'</span>,<span class="hljs-string">'多条插入2'</span>)
<span class="hljs-keyword">ON</span> DUPLICATE <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">UPDATE</span> 
password =  <span class="hljs-keyword">VALUES</span>(password);</span>
<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li></ul></pre>
<p>方法之一可以将后面的修改条件改为VALUES(password)，动态的传入要修改的值，执行以下：</p>
<pre class="prettyprint" name="code"><code class="hljs sql has-numbering" οnclick="mdcp.copyCode(event)" style="position: unset;">[SQL]<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user_admin_t (_id,password) 
<span class="hljs-keyword">VALUES</span> 
(<span class="hljs-string">'1'</span>,<span class="hljs-string">'多条插入1'</span>) ,
(<span class="hljs-string">'UpId'</span>,<span class="hljs-string">'多条插入2'</span>)
<span class="hljs-keyword">ON</span> DUPLICATE <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">UPDATE</span> 
password =  <span class="hljs-keyword">VALUES</span>(password);</span>
受影响的行: 4
时间: 0.187s

<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li></ul></pre>
<p>成功的修改了两条记录，刷新一下表</p>
<p><img title="" alt="多条修改" src="https://img-blog.csdn.net/20171105153701640?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast"></p>
<p>我们成功的为不同id的password修改成了不同的值</p>
<h1 id="总结"><a name="t4"></a><a></a>总结</h1>
<p>其实修改的方法有很多种，包括SET或用REPLACE，连事务都省的做，ON DUPLICATE KEY UPDATE能够让我们便捷的完成重复插入的开发需求，但它是Mysql的特有语法，使用时应多注意主键和插入值是否是我们想要插入或修改的key、Value。</p>            

                                    </div>
                <link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-e44c3c0e64.css" rel="stylesheet">
                    </div>