MYSQL 4种插入数据的方式比较

4种插入数据的方式

 

第一种：insert into

 

insert into是最常用的插入数据的方式，可以单条插入，也可以多条，还可以指定从其他表中select然后插入。 详细可以参考：insert语法参考。

 

第二种：insert ignore into

 

加上ignore，当表中有相同键的时候，忽略插入。因此从效果上来说，就是以旧数据为准。它和下面将要介绍的第三种插入方式正好相反。

 

第三种：replace into

 

使用replace，如果表中有冲突的键，那么先删除这样的行，然后插入。从效果上来说，就是以新数据为准。 前面的两种方式，要么全旧，要么全新，那么有没有折中的方式呢？这就是第四种：有选择的替换。

 

第四种：insert into on duplicate key update

 

当发现冲突键的时候，有选择的更新某些列的值。这里有个特别的values函数，当遇到数据冲突时，可以引用values中的值来更新相关的数据：
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

 

 

 

 

举个例子，字段a被定义为UNIQUE，并且原数据库表table中已存在记录(2,2,9)和(3,2,1)，如果插入记录的a值与原有记录重复，则更新原有记录，否则插入新行： 

复制代码代码如下:

INSERT INTO TABLE (a,b,c) VALUES 
(1,2,3), 
(2,5,7), 
(3,3,6), 
(4,8,2) 
ON DUPLICATE KEY UPDATE b=VALUES(b); 

以上SQL语句的执行，发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突，则执行ON DUPLICATE KEY UPDATE，将原有记录(2,2,9)更新成(2,5,9)，将(3,2,1)更新成(3,3,1)，插入新记录(1,2,3)和(4,8,2) 

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！

 

 

 

使用场景

 

有了上面的介绍，使用场景已很明了。特别说明几点：

 

1. 1. 如果表没有主键，或主键是没有意义的伪列，而且没有其他唯一约束，那么请不要使用replace into，因为这种情况下使用insert into同样可以做到，你画蛇添足的效果可能就是像上面一样，触发bug。

   2. 如果表上有自增的主键，还有其他唯一键，那么使用replace into要小心了：其他唯一键一旦冲突，旧数据会被删除，然后插入新的数据行，结果就是自增主键的值越来越大，尽管表的数据量不大。长此以往，即使表的数据只有区区几千行，但是自增列的值可能非常大，最后越界。这种情况下可以采用insert into on duplicate key update的方式。当然也可以去掉自增的列，但是innodb的聚簇索引结构情况下，修改主键是”比较愚蠢的做法”。

   3. 关于返回值的判断：一条replace into语句可能会影响多行。如果一个表有多个唯一键，如果发生键冲突，必须先删除一行，再试图插入数据的时候，可能与第二个唯一键又冲突，又需要删除…，因此有可能删除了多行数据最后才成功插入一条数据。因此，被删除的行数是受影响的行数减1。

   4.  

转载于:https://www.cnblogs.com/T8881/p/6424284.html