创建测试表
mysql> CREATE TABLE books(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) UNIQUE,remark VARCHAR(100));
mysql> INSERT INTO books(name,remark) VALUES('MySQL of actual combat','MySQL of actual combat information');
mysql> SELECT * FROM books;
+----+------------------------+------------------------------------+
| id | name | remark |
+----+------------------------+------------------------------------+
| 1 | MySQL of actual combat | MySQL of actual combat information |
+----+------------------------+------------------------------------+
1 row in set (0.00 sec)
insert into : 插入数据,如果数据库进行唯一性检查出现重复会报错
mysql> INSERT INTO books(name,remark) VALUES('MySQL of actual combat','MySQL of actual combat information');
ERROR 1062 (23000): Duplicate entry 'MySQL of actual combat' for key 'name'
replace into : 插入替换数据,如果数据库进行唯一性检查出现重复,则用新数据替换,如果没有数据效果则和 insert into 一样
mysql> REPLACE INTO books(name,remark) VALUES('MySQL of actual combat','replace into');
Query OK, 2 rows affected (0.02 sec)
mysql> SELECT * FROM books;
+----+------------------------+--------------+
| id | name | remark |
+----+------------------------+--------------+
| 3 | MySQL of actual combat | replace into |
+----+------------------------+--------------+
1 row in set (0.00 sec)
insert ignore into : 当插入数据时,如果数据库进行唯一性检查出现重复,将不返回错误,只以警告形式返回。所以使用 ignore 要确保语句本身没有问题,否则也会被忽略掉
mysql> INSERT IGNORE INTO books(name,remark) VALUES('MySQL of actual combat','insert ignore into');
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> SELECT * FROM books;
+----+------------------------+--------------+
| id | name | remark |
+----+------------------------+--------------+
| 3 | MySQL of actual combat | replace into |
+----+------------------------+--------------+
1 row in set (0.00 sec)
on duplicate key update : 如果数据库进行唯一性检查出现重复执行 update语句,执行 update语句 后如出现重复还是会报错
mysql> INSERT INTO books(name,remark) VALUES('MySQL of actual combat','on duplicate key update') ON DUPLICATE KEY UPDATE name=CONCAT(CONCAT(name,'_'),NOW());
Query OK, 2 rows affected (0.02 sec)
mysql> SELECT * FROM books;
+----+--------------------------------------------+--------------+
| id | name | remark |
+----+--------------------------------------------+--------------+
| 4 | MySQL of actual combat_2018-02-08 09:17:24 | replace into |
+----+--------------------------------------------+--------------+
1 row in set (0.00 sec)