首页 MySql mysql replace into 的使用情况

mysql replace into 的使用情况

dawei 2020年9月1日 13:02

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

本文来自网络，不代表云浮站长网立场。转载请注明出处： https://www.0766zz.com/html/shujuku/mysql/20200901/9108.html

标签:into mysql replace 的使用情况

作者: dawei

【声明】：云浮站长网内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。