mysql更新判断存在更新，不存在插入

最新推荐文章于 2024-07-30 03:41:19 发布

修行者坚守者

最新推荐文章于 2024-07-30 03:41:19 发布

阅读量600

点赞数

分类专栏： mysql java

java 同时被 2 个专栏收录

166 篇文章 0 订阅

订阅专栏

mysql

32 篇文章 0 订阅

订阅专栏

转：http://blog.csdn.net/zbz0425/article/details/45815635

MySQL 更新插入，可以执行类似oracle 的 merge 语句来做判断是更新还是插入

这就要介绍一下在MySql中INSERT语法具有一个条件DUPLICATE KEY UPDATE，这个语法和适合用在需要判断记录是否存在，不存在则插入存在则更新的记录。

具体的语法可以参见：http://dev.mysql.com/doc/refman/5.0/en/insert.html

基于上面这种情况，针对更新记录，仍然使用insert语句，不过限制主键重复时，更新字段。如下：

<code class="prettyprint" style="font-family: Consolas,Menlo,Monaco,"Lucida Console","Liberation Mono","DejaVu Sans Mono","Bitstream Vera Sans Mono","Courier New",monospace,serif; display: block; max-height: 600px; background-color: rgb(247, 247, 247);"><span class="pln">INSERT INTO t_member </span><span class="pun">(</span><span class="pln">id</span><span class="pun">,</span><span class="pln"> name</span><span class="pun">,</span><span class="pln"> email</span><span class="pun">)</span><span class="pln"> VALUES
    </span><span class="pun">(</span><span class="lit" style="color: rgb(128, 0, 0);">1</span><span class="pun">,</span><span class="pln"> </span><span class="str" style="color: rgb(128, 0, 0);">'nick'</span><span class="pun">,</span><span class="pln"> </span><span class="str" style="color: rgb(128, 0, 0);">'nick@126.com'</span><span class="pun">),</span><span class="pln">
    </span><span class="pun">(</span><span class="lit" style="color: rgb(128, 0, 0);">4</span><span class="pun">,</span><span class="pln"> </span><span class="str" style="color: rgb(128, 0, 0);">'angel'</span><span class="pun">,</span><span class="str" style="color: rgb(128, 0, 0);">'angel@163.com'</span><span class="pun">),</span><span class="pln">
    </span><span class="pun">(</span><span class="lit" style="color: rgb(128, 0, 0);">7</span><span class="pun">,</span><span class="pln"> </span><span class="str" style="color: rgb(128, 0, 0);">'brank'</span><span class="pun">,</span><span class="str" style="color: rgb(128, 0, 0);">'ba198@126.com'</span><span class="pun">)</span><span class="pln">
ON DUPLICATE KEY UPDATE name</span><span class="pun">=</span><span class="pln">VALUES</span><span class="pun">(</span><span class="pln">name</span><span class="pun">),</span><span class="pln"> email</span><span class="pun">=</span><span class="pln">VALUES</span><span class="pun">(</span><span class="pln">email</span><span class="pun">);</span></code><div class="save_code tracking-ad" style="display: none;" data-mod="popu_249"><a><img src="http://static.blog.csdn.net/images/save_snippets.png" alt="" /></a></div><div class="save_code tracking-ad" style="display: none;" data-mod="popu_249"><a><img src="http://static.blog.csdn.net/images/save_snippets.png" alt="" /></a></div>

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！