mysql replace into 的使用情况

发布时间：2020-09-01 19:18:18 所属栏目：MySql 来源：互联网

导读：replace into的存在的几种情况当表存在主键并且存在唯一键的时候如果只是主键冲突 mysql select * from auto; + + + + + | id | k | v

<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)

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)

如果主键跟唯一键都冲突并且在同一行里

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)

如果主键跟唯一键都冲突不在同一行，对应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)
                        （编辑：商洛站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!