以主键和unique索引为依据。
INSERT INTO:表中不存在对应的记录,则插入;若存在对应的记录,则报错;
INSERT IGNORE INTO:表中不存在对应的记录,则插入;若存在对应的记录,则忽略,不进行任何操作;
REPLACE INTO:表中不存在对应的记录,则插入;若存在对应的记录,则删除原有的记录,再 插入新的记录。
需要说明的是,如果插入的表没有主键或唯一索引,上述命令均直接往表中插入新的数据,不再判断记录的重复性。
REPLACE语句会返回一个数,来指示受影响的行的数目,该数是被删除和被插入的行数的和。
这里将介绍REPLACE INTO的用法和示例。
REPLACE INTO 主要有以下三种用法:
语句1:REPLACE INTO table_name (field1,field2...) values (value1,value2...);
语句2:REPLACE INTO table1_name (field1,field2...)select field3,field4... from table2_name;
语句3:REPLACE INTO table_name set field1=value1,field2=value...;
其中,语句2涉及了表复制,后续会进行详解。
mysql> select * fromstaff_3;+----------+-------+
| name | slary |
+----------+-------+
| liding | 2700 |
| haofugui | 3500 |
| xiaoli | 3600 |
+----------+-------+
3 rows in set (0.00sec)
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.00sec)
mysql>replace into staff_3 (name,slary) values ('xiaohua',3000);//在原表中不存在,则直接插入
Query OK,1 row affected (0.00sec)
mysql> select * fromstaff_3;+----------+-------+
| name | slary |
+----------+-------+
| liding | 2700 |
| haofugui | 3500 |
| xiaoli | 3600 |
| xiaohua | 3000 |
+----------+-------+
4 rows in set (0.00sec)
mysql> replace into staff_3 (name,slary) values ('xiaohua',6000);//在原表中存在,则直接更新
Query OK,2 rows affected (0.00sec)
mysql> select * fromstaff_3;+----------+-------+
| name | slary |
+----------+-------+
| liding | 2700 |
| haofugui | 3500 |
| xiaoli | 3600 |
| xiaohua | 6000 |
+----------+-------+
4 rows in set (0.00 sec)
需要说明的是,该命令在操作有重复数据时,会先删除原有数据,在插入新的数据。
示例:
mysql>describe staff_3;+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | UNI | NULL | |
| slary | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00sec)
mysql> select * fromstaff_3;+----+----------+-------+
| ID | name | slary |
+----+----------+-------+
| 1 | liding | 2700 |
| 2 | haofugui | 3500 |
| 3 | xiaoli | 5000 |
| 4 | xiaohua | 6000 |
| 5 | xiaoming | 4000 |
+----+----------+-------+
5 rows in set (0.00sec)
mysql> replace into staff_3 (name,slary) values ('xiaoming','3000'); //插入一个已有记录的记录
Query OK,2 rows affected (0.00sec)
mysql> select * fromstaff_3; //从ID可以看出,ID=5对应的原有记录被删除了+----+----------+-------+
| ID | name | slary |
+----+----------+-------+
| 1 | liding | 2700 |
| 2 | haofugui | 3500 |
| 3 | xiaoli | 5000 |
| 4 | xiaohua | 6000 |
| 6 | xiaoming | 3000 |
+----+----------+-------+
5 rows in set (0.00 sec)