加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

mysql replace into 的使用情况

发布时间：2020-12-11 23:59:02 所属栏目：MySql教程 来源：网络整理
导读：div id="cnblogs_post_body" class="blogpost-body" h3 data-source-line="1"replace into的存在的几种情况 ul data-source-line="3" 当表存在主键并且存在唯一键的时候 如果只是主键冲突 mysql> mysqlspan style="color: #000000" mysqlspan style="color:

<div id="cnblogs_post_body" class="blogpost-body">
<h3 data-source-line="1">replace into的存在的几种情况
<ul data-source-line="3">

  • 当表存在主键并且存在唯一键的时候
    • 如果只是主键冲突
    • mysql> mysql><span style="color: #000000">
    
mysql><span style="color: #000000"> show create table autoG
    
 <span style="color: #800080">1. row <span style="color: #000000">
    
Table: auto
    
Create Table: CREATE TABLE auto (
    
<span style="color: #0000ff"&gt;id <span style="color: #0000ff">int(<span style="color: #800080">10<span style="color: #000000">) unsigned NOT NULL AUTO_INCREMENT,k <span style="color: #0000ff">int(<span style="color: #800080">10<span style="color: #000000">) unsigned NOT NULL,v varchar(<span style="color: #800080">100<span style="color: #000000">) DEFAULT NULL,extra varchar(<span style="color: #800080">200<span style="color: #000000">) DEFAULT NULL,PRIMARY KEY (<span style="color: #0000ff"&gt;id<span style="color: #000000"&gt;),UNIQUE KEY uk_k (k)
    
) ENGINE=InnoDB AUTO_INCREMENT=<span style="color: #800080">5 DEFAULT CHARSET=<span style="color: #000000">latin1
    
<span style="color: #800080">1 row <span style="color: #0000ff">in set (<span style="color: #800080">0.00 sec)

    <div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码">

    <img src="https://www.52php.cn/res/2019/01-31/09/51e409b11aa51c150090697429a953ed.gif" alt="复制代码">

    mysql> replace into auto(mysql> <span style="color: #0000ff">select *<span style="color: #000000"> from auto;
    
+----+---+------+---------+
    
| <span style="color: #0000ff">id | k | v    | extra   |
    
+----+---+------+---------+
    
|  <span style="color: #800080">2 | <span style="color: #800080">2 | <span style="color: #800080">2    | extra <span style="color: #800080">2 |
    
|  <span style="color: #800080">3 | <span style="color: #800080">3 | <span style="color: #800080">3    | extra <span style="color: #800080">3 |
    
|  <span style="color: #800080">4 | <span style="color: #800080">5 | NULL | NULL    |
    
+----+---+------+---------+
    
<span style="color: #800080">3 rows <span style="color: #0000ff">in set (<span style="color: #800080">0.00<span style="color: #000000"> sec)
    

    mysql><span style="color: #000000"> show create table auto G
    
 <span style="color: #800080">1. row <span style="color: #000000">
    
Table: auto
    
Create Table: CREATE TABLE auto (
    
<span style="color: #0000ff"&gt;id <span style="color: #0000ff">int(<span style="color: #800080">10<span style="color: #000000">) unsigned NOT NULL AUTO_INCREMENT,UNIQUE KEY uk_k (k)
    
) ENGINE=InnoDB AUTO_INCREMENT=<span style="color: #800080">5 DEFAULT CHARSET=<span style="color: #000000">latin1
    
<span style="color: #800080">1 row <span style="color: #0000ff">in set (<span style="color: #800080">0.00 sec)

    <div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码">

    <img src="https://www.52php.cn/res/2019/01-31/09/51e409b11aa51c150090697429a953ed.gif" alt="复制代码">

      如果主键跟唯一键都冲突并且在同一行里 
    mysql> mysql><span style="color: #000000"> show create table auto G
    
 <span style="color: #800080">1. row <span style="color: #000000">
    
Table: auto
    
Create Table: CREATE TABLE auto (
    
<span style="color: #0000ff"&gt;id <span style="color: #0000ff">int(<span style="color: #800080">10<span style="color: #000000">) unsigned NOT NULL AUTO_INCREMENT,UNIQUE KEY uk_k (k)
    
) ENGINE=InnoDB AUTO_INCREMENT=<span style="color: #800080">6 DEFAULT CHARSET=<span style="color: #000000">latin1
    
<span style="color: #800080">1 row <span style="color: #0000ff">in set (<span style="color: #800080">0.00<span style="color: #000000"> sec)
    

    mysql><span style="color: #000000">
    
mysql><span style="color: #000000">
    
mysql> replace into auto(<span style="color: #0000ff">id,k,extra)values(<span style="color: #800080">5,<span style="color: #800080">6,<span style="color: #800080">77<span style="color: #000000">);
    
Query OK,<span style="color: #800080">2 rows affected (<span style="color: #800080">0.01<span style="color: #000000"> sec)
    

    mysql> <span style="color: #0000ff">select *<span style="color: #000000"> from auto;
    
+----+---+------+---------+
    
| <span style="color: #0000ff">id | k | v    | extra   |
    
+----+---+------+---------+
    
|  <span style="color: #800080">2 | <span style="color: #800080">2 | <span style="color: #800080">2    | extra <span style="color: #800080">2 |
    
|  <span style="color: #800080">3 | <span style="color: #800080">3 | <span style="color: #800080">3    | extra <span style="color: #800080">3 |
    
|  <span style="color: #800080">4 | <span style="color: #800080">5 | NULL | NULL    |
    
|  <span style="color: #800080">5 | <span style="color: #800080">6 | NULL | <span style="color: #800080">77      |
    
+----+---+------+---------+
    
<span style="color: #800080">4 rows <span style="color: #0000ff">in set (<span style="color: #800080">0.00<span style="color: #000000"> sec)
    

    mysql><span style="color: #000000"> show create table auto G
    
 <span style="color: #800080">1. row <span style="color: #000000">
    
Table: auto
    
Create Table: CREATE TABLE auto (
    
<span style="color: #0000ff"&gt;id <span style="color: #0000ff">int(<span style="color: #800080">10<span style="color: #000000">) unsigned NOT NULL AUTO_INCREMENT,UNIQUE KEY uk_k (k)
    
) ENGINE=InnoDB AUTO_INCREMENT=<span style="color: #800080">6 DEFAULT CHARSET=<span style="color: #000000">latin1
    
<span style="color: #800080">1 row <span style="color: #0000ff">in set (<span style="color: #800080">0.00 sec)

    <div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码">

    <img src="https://www.52php.cn/res/2019/01-31/09/51e409b11aa51c150090697429a953ed.gif" alt="复制代码">

      如果主键跟唯一键都冲突不在同一行，对应2条记录呢 

    mysql>mysql> <span style="color: #0000ff">select *<span style="color: #000000"> from auto;
    
+----+---+------+---------+
    
| <span style="color: #0000ff">id | k | v    | extra   |
    
+----+---+------+---------+
    
|  <span style="color: #800080">2 | <span style="color: #800080">2 | <span style="color: #800080">2    | extra <span style="color: #800080">2 |
    
|  <span style="color: #800080">3 | <span style="color: #800080">3 | <span style="color: #800080">3    | extra <span style="color: #800080">3 |
    
|  <span style="color: #800080">4 | <span style="color: #800080">5 | NULL | NULL    |
    
|  <span style="color: #800080">6 | <span style="color: #800080">6 | <span style="color: #800080">66   | NULL    |
    
+----+---+------+---------+
    
<span style="color: #800080">4 rows <span style="color: #0000ff">in set (<span style="color: #800080">0.00<span style="color: #000000"> sec)
    

    mysql> replace into auto(<span style="color: #0000ff">id,v)values(<span style="color: #800080">6,<span style="color: #800080">2,<span style="color: #800080">88<span style="color: #000000">);
    
Query OK,<span style="color: #800080">3 rows affected (<span style="color: #800080">0.03 sec)

    <div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码">

    <img src="https://www.52php.cn/res/2019/01-31/09/51e409b11aa51c150090697429a953ed.gif" alt="复制代码">

    mysql> replace into auto(mysql> <span style="color: #0000ff">select *<span style="color: #000000"> from auto;
    
+----+---+------+---------+
    
| <span style="color: #0000ff">id | k | v    | extra   |
    
+----+---+------+---------+
    
|  <span style="color: #800080">3 | <span style="color: #800080">3 | <span style="color: #800080">3    | extra <span style="color: #800080">3 |
    
|  <span style="color: #800080">4 | <span style="color: #800080">5 | NULL | NULL    |
    
|  <span style="color: #800080">6 | <span style="color: #800080">2 | <span style="color: #800080">88   | NULL    |
    
+----+---+------+---------+
    
<span style="color: #800080">3 rows <span style="color: #0000ff">in set (<span style="color: #800080">0.00<span style="color: #000000"> sec)
    

    mysql><span style="color: #000000"> show create table auto G
    
 <span style="color: #800080">1. row <span style="color: #000000">
    
Table: auto
    
Create Table: CREATE TABLE auto (
    
<span style="color: #0000ff"&gt;id <span style="color: #0000ff">int(<span style="color: #800080">10<span style="color: #000000">) unsigned NOT NULL AUTO_INCREMENT,UNIQUE KEY uk_k (k)
    
) ENGINE=InnoDB AUTO_INCREMENT=<span style="color: #800080">7 DEFAULT CHARSET=<span style="color: #000000">latin1
    
<span style="color: #800080">1 row <span style="color: #0000ff">in set (<span style="color: #800080">0.00 sec)

    <div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码">

    <img src="https://www.52php.cn/res/2019/01-31/09/51e409b11aa51c150090697429a953ed.gif" alt="复制代码">

      如果仅仅是唯一键冲突呢？ 
    mysql> mysql><span style="color: #000000"> show create table auto G
    
 <span style="color: #800080">1. row <span style="color: #000000">
    
Table: auto
    
Create Table: CREATE TABLE auto (
    
<span style="color: #0000ff"&gt;id <span style="color: #0000ff">int(<span style="color: #800080">10<span style="color: #000000">) unsigned NOT NULL AUTO_INCREMENT,UNIQUE KEY uk_k (k)
    
) ENGINE=InnoDB AUTO_INCREMENT=<span style="color: #800080">6 DEFAULT CHARSET=<span style="color: #000000">latin1
    
<span style="color: #800080">1 row <span style="color: #0000ff">in set (<span style="color: #800080">0.00<span style="color: #000000"> sec)
    

    mysql><span style="color: #000000">
    
mysql> replace into auto(k,<span style="color: #800080">66<span style="color: #000000">);
    
Query OK,<span style="color: #800080">2 rows affected (<span style="color: #800080">0.04<span style="color: #000000"> sec)
    

    mysql> <span style="color: #0000ff">select *<span style="color: #000000"> from auto;
    
+----+---+------+---------+
    
| <span style="color: #0000ff">id | k | v    | extra   |
    
+----+---+------+---------+
    
|  <span style="color: #800080">2 | <span style="color: #800080">2 | <span style="color: #800080">2    | extra <span style="color: #800080">2 |
    
|  <span style="color: #800080">3 | <span style="color: #800080">3 | <span style="color: #800080">3    | extra <span style="color: #800080">3 |
    
|  <span style="color: #800080">4 | <span style="color: #800080">5 | NULL | NULL    |
    
|  <span style="color: #800080">6 | <span style="color: #800080">6 | <span style="color: #800080">66   | NULL    |
    
+----+---+------+---------+
    
<span style="color: #800080">4 rows <span style="color: #0000ff">in set (<span style="color: #800080">0.00<span style="color: #000000"> sec)
    

    mysql><span style="color: #000000"> show create table auto G
    
 <span style="color: #800080">1. row <span style="color: #000000">
    
Table: auto
    
Create Table: CREATE TABLE auto (
    
<span style="color: #0000ff"&gt;id <span style="color: #0000ff">int(<span style="color: #800080">10<span style="color: #000000">) unsigned NOT NULL AUTO_INCREMENT,UNIQUE KEY uk_k (k)
    
) ENGINE=InnoDB AUTO_INCREMENT=<span style="color: #800080">7 DEFAULT CHARSET=<span style="color: #000000">latin1
    
<span style="color: #800080">1 row <span style="color: #0000ff">in set (<span style="color: #800080">0.00 sec)

    <div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码">

    <img src="https://www.52php.cn/res/2019/01-31/09/51e409b11aa51c150090697429a953ed.gif" alt="复制代码">

      当replace into 记录只与主键冲突的时候，auto_increment不会增加，它会对与主键冲突的那一条记录进行更新，没有指定的列将会被更新为默认值
    1. 当replace into 记录与主键跟唯一索引同时冲突的时候，auto_increment不会增加
      1. 如果冲突的主键和索引在同一行记录，则replace into只做更新，对于没有指定值的其他列，将会被更新为默认值，
      2. 如果冲突的主键和索引分别对应2行数据，则MySQL将会删除唯一索引的那一行记录，更新对应主键的那一行记录。
    2. 当replace into 记录只与唯一索引进行冲突的时候，auto_increment + 1，再对数据进行更新。
      最后我们可以对总结分析下，MySQL对replace into的操作是首先是insert操作，如果insert失败，则对insert失败的这条记录进行update，如果update还是失败，则会进行delete操作之后再update。
    • 具体流程是这样的：insert记录，发现主键冲突，则update这一行，update的时候发现存在唯一键冲突，则delete对应的唯一键的行后再进行update。如果insert成功，auto_increment自然+1了，然后对这条记录进行update，update的时候发现存在唯一键冲突，则delete对应的唯一键的行后再进行update。

    （编辑：李大同）

    【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
      相关内容
        推荐文章
          站长推荐
          热点阅读

            【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

            建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

            Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同