#################

 

 一、INSERT  INTO 语句:要求是不能违反主键或唯一索引,否则报错

 

 

一次插入一条数据:

 

INSERT  INTO table_name (field1,field2) values (value1,value2);

 

 

一次插入多条数据:

 

INSERT INTO `iphone` VALUES (1,'iphone4','USA',1),(2,'iphone5','USA',1),(3,'iphone6','USA',1),(4,'iphone7','USA',1),(5,'iphone8','USA',1);

 

 

二、replace into语句:检测是否违反主键或唯一索引;

规则:

1)若只违反主键,或只违反唯一索引,或既违反主键也违反唯一索引,则先删掉违反约束的所有数据,再插入新的数据,影响的行数为删除的行数+插入的行数;

2)既不违反主键,也不违反唯一索引,则直接将数据插入数据库,影响的行数为插入的行数,此时与insert into的效果一样。

3)当表中的唯一索引字段可以为NULL的时候,则该字段插入相同NUll的时候被视为不同数据,即被视为不违反唯一索引,此时就是insert into语句,唯一索引没有not null限制的时候,唯一索引字段可以有多个相同NULL值的数据行 场景: 替换现有数据:如果一张表已经有了数据,现在又需要插入的一部分数据,如果新数据与旧的数据存在违反主键或唯一索引的情况,要求保留新数据,即新数据优先级更高,是一个原子操作。

传统做法:在一个事务里面:若存在主键相同或唯一索引字段相同,则先delete删除原有数据,再insert新数据

 

在执行REPLACE后,系统返回了所影响的行数:

1)如果返回1,说明在表中并没有重复的记录,

2)如果返回2,说明只有一行数据违反了主键或唯一约束,系统自动先调用了 DELETE删除这条记录,然后再记录用INSERT来插入这条记录。

3)如果返回的值大于2,说明多行违反了主键或唯一约束,这时候会先删除所有违反主键或唯一索引约束的行,然后再插入新数据,影响的函数则是:删除的行数+插入的行数。有多少个违反主键或唯一索引的行数就会有多少个主键或唯一索引,因为一张表可能有多个唯一索引存在,而插入的数据可能违反了多个唯一索引,这时候删除的数据个数大于插入的数据,会出现插入数据后,数据还变少的的现象。

 

 

 

 一次插入一条数据:

 

REPLACE INTO table_name (field1,field2) values (value1,value2);

 

 一次插入多条数据:

 

REPLACE INTO `iphone` VALUES (1,'iphone4','USA',1),(2,'iphone5','USA',1),(3,'iphone6','USA',1),(4,'iphone7','USA',1),(5,'iphone8','USA',1);

 

 

 三、INSERT IGNORE INTO:检测是否违反主键或唯一索引

规则:

1)若只违反主键,或只违反唯一索引,或既违反主键也违反唯一索引,则会忽略掉插入数据;

2)只有既不违反主键,也不违反唯一索引,才会将数据插入数据库。

3)当表中的唯一索引字段可以为NULL的时候,则该字段插入相同NUll的时候被视为不同数据,即被视为不违反唯一索引,此时就是insert into语句,唯一索引没有not null限制的时候,唯一索引字段可以有多个相同NULL值的数据行

场景:

不影响现有数据:如果一张表已经有了数据,现在又需要插入的一部分数据,但是不要影响已经存在的数据,如果遇到违反主键或唯一索引的约束的数据不要报错,直接忽略即可

 

之前在介绍INSERT INTO命令时,曾经提到,该命令在执行数据插入操作时,会在数据库中对元组的主键进行检测,若没有存在,则执行插入动作,若存在,则会报错。

而INSERT IGNORE INTO命令与其不同的是,INSERT IGNORE会忽略数据库中已经存在的记录:

如果数据库没有数据,就插入新的数据;如果有数据的话就跳过这条数据。

这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。

需要说明的是,INSERT INTO和INSERT IGNORE INTO只根据“主键值”或“unique索引”进行判断,只要主键值已在数据库中存在,则认为即将插入重复记录。

 

一次插入一行数据:

INSERT IGNORE INTO table_name (field1,field2) values (value1,value2);

 

一次插入多行数据:

INSERT IGNORE INTO `iphone` VALUES (1,'iphone4','USA',1),(2,'iphone5','USA',1),(3,'iphone6','USA',1),(4,'iphone7','USA',1),(5,'iphone8','USA',1);

 

 

示例:

mysql> select * from staff_3;
+----------+-------+
| name     | slary |
+----------+-------+
| liding   |  2700 |
| haofugui |  3500 |
| xiaoli   |  3600 |
+----------+-------+
3 rows in set (0.00 sec)

mysql> describe staff_3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(20) | NO   | PRI |         |       |
| slary | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into staff_3 (name,slary) values ('xiaoli',3200);
ERROR 1062 (23000): Duplicate entry 'xiaoli' for key 'PRIMARY'
mysql> insert ignore into staff_3 (name,slary) values ('xiaoli',3200);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from staff_3;
+----------+-------+
| name     | slary |
+----------+-------+
| liding   |  2700 |
| haofugui |  3500 |
| xiaoli   |  3600 |
+----------+-------+
3 rows in set (0.00 sec)

 

 

四、INSERT INTO ....  ON DUPLICATE KEY UPDATE :根据是否违反主键或唯一索引;若违反则更新;若不违反则直接插入;若违反且新数据和旧数据相同则不更新。

 

在实际应用中,经常碰到导入数据的功能,当导入的数据不存在时则进行添加,有修改时则进行更新,在刚碰到的时候,一般思路是将其实现分为两块,分别是判断增加,判断更新,后来发现在mysql中有ON DUPLICATE KEY UPDATE一步就可以完成(Mysql独有的语法)。

ON DUPLICATE KEY UPDATE单个增加更新及批量增加更新的sql

在MySQL数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的惟一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作。

说通俗点就是数据库中存在某个记录时,执行这个语句会更新,而不存在这条记录时,就会插入。

注意点:

  因为这是个插入语句,所以不能加where条件。

  插入规则:

1)如果是插入操作,受到影响行的值为1;

2)如果更新操作,受到影响行的值为2;

3)如果更新的数据和已有的数据一样(就相当于没变,所有值保持不变),受到影响的行的值为0。

 

该语句是基于唯一索引或主键使用,比如一个字段a被加上了unique index,并且表中已经存在了一条记录值为1,

下面两个语句会有相同的效果:

INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=c+1;  
  
UPDATE table SET c=c+1 WHERE a=1;

 

ON DUPLICATE KEY UPDATE后面可以放多个字段,用英文逗号分割。

再现一个例子:

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

 

表中将更改(增加或修改)两条记录。

 

 ###########

 

####################