mysql replace into 的使用情况

<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 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,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.jb51.cc/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.jb51.cc/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.jb51.cc/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)