| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
Renate1621
5年前发布

MySQL避免插入重复记录的方法

   <p>mysql在存在主键冲突或者唯一键冲突的情况下,根据插入策略不同,一般有以下三种避免方法。</p>    <p>1、insert ignore</p>    <p>2、replace into</p>    <p>3、insert on duplicate key update</p>    <p>注意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用以上三个语句没有意义,与使用单纯的INSERT INTO相同。</p>    <h2><strong>一、insert ignore</strong></h2>    <p>insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断),如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据.</p>    <h2><strong>Case:</strong></h2>    <p>表结构如下:</p>    <pre>  <code class="language-sql">root:test> show create table t3\G  *************************** 1. row ***************************         Table: t3  Create Table: CREATE TABLE `t3` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `c1` int(11) DEFAULT NULL,    `c2` varchar(20) DEFAULT NULL,    `c3` int(11) DEFAULT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `uidx_c1` (`c1`)  ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8  1 row in set (0.00 sec)    root:test> select * from t3;      +----+------+------+------+      | id | c1   | c2   | c3   |      +----+------+------+------+      |  1 |    1 | a    |    1 |      |  2 |    2 | a    |    1 |      |  8 | NULL | NULL |    1 |      | 14 |    4 | bb   | NULL |      | 17 |    5 | cc   |    4 |      +----+------+------+------+      5 rows in set (0.00 sec)  </code></pre>    <p>测试插入唯一键冲突的数据</p>    <pre>  <code class="language-sql">root:test> insert ignore into t3 (c1,c2,c3) values(5,'cc',4),(6,'dd',5);     Query OK, 1 row affected, 1 warning (0.01 sec)  Records: 2  Duplicates: 1  Warnings: 1  </code></pre>    <p>如下,可以看到只插入了(6,'dd',5)这条,同时有一条warning提示有重复的值。</p>    <pre>  <code class="language-sql">root:test> show warnings;  +---------+------+---------------------------------------+  | Level   | Code | Message                               |  +---------+------+---------------------------------------+  | Warning | 1062 | Duplicate entry '5' for key 'uidx_c1' |  +---------+------+---------------------------------------+  1 row in set (0.00 sec)    root:test> select * from t3;  +----+------+------+------+  | id | c1   | c2   | c3   |  +----+------+------+------+  |  1 |    1 | a    |    1 |  |  2 |    2 | a    |    1 |  |  8 | NULL | NULL |    1 |  | 14 |    4 | bb   | NULL |  | 17 |    5 | cc   |    4 |  | 18 |    6 | dd   |    5 |  +----+------+------+------+  6 rows in set (0.00 sec)  </code></pre>    <p>重新查询表结构,发现虽然只增加了一条记录,但是AUTO_INCREMENT还是增加了2个(18变成20)</p>    <pre>  <code class="language-sql">root:test> show create table t3\G      *************************** 1. row ***************************         Table: t3         Create Table: CREATE TABLE `t3` (        `id` int(11) NOT NULL AUTO_INCREMENT,        `c1` int(11) DEFAULT NULL,        `c2` varchar(20) DEFAULT NULL,        `c3` int(11) DEFAULT NULL,      PRIMARY KEY (`id`),      UNIQUE KEY `uidx_c1` (`c1`)      ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8      1 row in set (0.00 sec)  </code></pre>    <h2><strong>二、replace into</strong></h2>    <ul>     <li>replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则,直接插入新数据。</li>     <li>使用replace into,你必须具有delete和insert权限</li>    </ul>    <h2>Case:</h2>    <pre>  <code class="language-sql">root:test> show create table t3\G  *************************** 1. row ***************************         Table: t3  Create Table: CREATE TABLE `t3` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `c1` int(11) DEFAULT NULL,    `c2` varchar(20) DEFAULT NULL,    `c3` int(11) DEFAULT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `uidx_c1` (`c1`)  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8  1 row in set (0.00 sec)    root:test> select * from t3;  +----+------+--------+------+  | id | c1   | c2     | c3   |  +----+------+--------+------+  |  1 |    1 | cc     |    4 |  |  2 |    2 | dd     |    5 |  |  3 |    3 | qwewqe |    3 |  +----+------+--------+------+  3 rows in set (0.00 sec)  </code></pre>    <p>插入一条与记录id=3存在唯一键(列c1)冲突的数据</p>    <pre>  <code class="language-sql">root:test> replace into t3 (c1,c2,c3) values(3,'new',8);  Query OK, 2 rows affected (0.02 sec)    root:test> select * from t3;  +----+------+------+------+  | id | c1   | c2   | c3   |  +----+------+------+------+  |  1 |    1 | cc   |    4 |  |  2 |    2 | dd   |    5 |  |  4 |    3 | new  |    8 |  +----+------+------+------+  3 rows in set (0.00 sec)  </code></pre>    <p>可以看到原有id=3,c1=3的记录不见了,新增了一条id=4,c1=3的记录.</p>    <p>replace into语句执行完会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和,上面的例子中2 rows affected .</p>    <h2><strong>三、insert on duplicate key update</strong></h2>    <ul>     <li> <p>如果在insert into 语句末尾指定了on duplicate key update,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致重复的问题,则插入新行,跟普通的insert into一样。</p> </li>     <li> <p>使用insert into,你必须具有insert和update权限</p> </li>     <li> <p>如果有新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2;如果记录被更新前后值是一样的,则受影响行数的值显示0</p> </li>    </ul>    <h2><strong>Case:</strong></h2>    <pre>  <code class="language-sql">root:test> show create table t3\G  *************************** 1. row ***************************         Table: t3  Create Table: CREATE TABLE `t3` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `c1` int(11) DEFAULT NULL,    `c2` varchar(20) DEFAULT NULL,    `c3` int(11) DEFAULT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `uidx_c1` (`c1`)  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8  1 row in set (0.00 sec)    root:test> select * from t3;   +----+------+------+------+  | id | c1   | c2   | c3   |  +----+------+------+------+  |  1 |    1 | fds  |    4 |  |  2 |    2 | ytu  |    3 |  |  3 |    3 | czx  |    5 |  +----+------+------+------+  3 rows in set (0.00 sec)  </code></pre>    <p>插入一条与记录id=3存在唯一键(列c1)冲突的数据</p>    <pre>  <code class="language-sql">root:test> insert into t3(c1,c2,c3) values (3,'new',5) on duplicate key update c1=c1+3;     Query OK, 2 rows affected (0.01 sec)    root:test> select * from t3;  +----+------+------+------+  | id | c1   | c2   | c3   |  +----+------+------+------+  |  1 |    1 | fds  |    4 |  |  2 |    2 | ytu  |    3 |  |  3 |    6 | czx  |    5 |  +----+------+------+------+  3 rows in set (0.00 sec)  </code></pre>    <p>可以看到,id=3的记录发生了改变,c1=原有的c1+3,其他列没有改变。</p>    <h2><strong>结论:</strong></h2>    <ul>     <li> <p>这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。</p> </li>     <li> <p>insert ignore能忽略重复数据,只插入不重复的数据。</p> </li>     <li> <p>replace into和insert ... on duplicate key update,都是替换原有的重复数据,区别在于replace into是删除原有的行后,在插入新行,如有自增id,这个会造成自增id的改变;insert ... on duplicate key update在遇到重复行时,会直接更新原有的行,具体更新哪些字段怎么更新,取决于update后的语句。</p> </li>    </ul>    <p> </p>    <p>来自:http://www.cnblogs.com/prayer21/p/6018864.html</p>    <p> </p>    
 本文由用户 Renate1621 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1477985303611.html
MySQL 数据库服务器