ON DUPLICATE KEY UPDATE 示例

最新推荐文章于 2024-06-11 17:04:41 发布

转载最新推荐文章于 2024-06-11 17:04:41 发布 · 149 阅读

mysql笔记专栏收录该内容

12 篇文章

订阅专栏

Table source

<code style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif"><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">    </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">CREATE</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">TABLE</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">source</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
  </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">key</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> int</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">11</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NOT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NULL</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> AUTO_INCREMENT</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
  </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">data</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> int</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">11</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">DEFAULT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NULL</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
  </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">state</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> int</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">11</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">DEFAULT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NULL</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
  </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">group</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> int</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">11</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">DEFAULT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NULL</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
  </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">PRIMARY</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">KEY</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">key</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">);</span></code>

Table dest

<code style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif"><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">    </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">CREATE</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">TABLE</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">dest</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
      </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">dkey</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> int</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">11</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NOT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NULL</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
      </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">ddata</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> int</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">11</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">DEFAULT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NULL</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
      </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">dstate</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> int</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">11</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NOT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">NULL</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
      </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">PRIMARY</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">KEY</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">dkey</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">dstate</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">
    </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">;</span></code>

Source Test Values

<code style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif"><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">INSERT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">INTO</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">source</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">key</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">data</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">state</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">group</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">VALUES</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">);</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">  
</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">INSERT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">INTO</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">source</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">key</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">data</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">state</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">group</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">VALUES</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">2</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">2</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">2</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">);</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">   
</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">INSERT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">INTO</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">source</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">key</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">data</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">state</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">group</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">VALUES</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">8</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">4</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">2</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">,</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">);</span></code>

Query:

<code style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif"><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">INSERT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">INTO</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">test</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`.`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">dest</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">dkey</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">ddata</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">dstate</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`)</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">   
    </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">SELECT</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">group</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">data</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`,`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">state</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">FROM</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">test</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`.`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">source</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">   
    </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">WHERE</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">group</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">=</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">  
    </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">ON</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> DUPLICATE </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">KEY</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">UPDATE</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">  
     </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">ddata</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`=</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">ddata</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`+</span><span class="kwd" style="color:#008b;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">VALUES</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">(`</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">ddata</span><span class="pun" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">`);</span></code>

经crazyhacking测试是正确的，结果是：

<code style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif"><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">  
</span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">1</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">6</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent"> </span><span class="lit" style="color:#8000;margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent;">2</span><span class="pln" style="margin:0px; padding:0px; border:0px; font-size:13.600000381469727px; vertical-align:baseline; background-color:transparent">   </span></code>

转载自：http://stackoverflow.com/questions/5728732/mysql-insert-select-on-duplicate-key-update-increment

可能存在的问题：http://hi.baidu.com/ytjwt/item/22869f092b8146d872e6760a

我想把test中的数据导入到test1中，若a存在，则跟新b，c为test表中数据加上test1的数据
开始时候，这样写
insert into test1(a,b,c) select a , b , c from test ON DUPLICATE KEY UPDATE b=b+values(b),c=c+values(c);
就会出现ERROR 1052 (23000): Column 'b' in field list is ambiguous
这是因为在上面的sql中，MySQL不知道b到底是数据test 还是test1.所以就会出现上面错误。
后跟改为
insert into test1(a,b,c) select a , b , c from test ON DUPLICATE KEY UPDATE test1.b=test1.b+values(b),test1.c=test1.c+values(c);
就可以运行了
注意：values(b) 与values(c) 其实是代表multiple-row inserts的第二列跟第三列，跟表名没有关系，不能写成values(test.b)
mysql> insert into test1(a,b,c) select a , b , c from test ON DUPLICATE KEY UPDATE test1.b=test1.b+values(test.b),test1.c=test1.c+values(test.c);
ERROR 1054 (42S22): Unknown column 'test.b' in 'field list'