MySQL insert ignore into、replace into、insert into、on duplicate key update 详解

创建测试表

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)