Mysql: 数据存在更新，不存在插入, Insert if not exist otherwise update, mysql update or insert if not exists without primary key, replace into

一般有三种方法：

我们用名为 books 的表做演示

mysql> SELECT * FROM books LIMIT 3;
+----+-------------------------+---------------------+----------------+
| id | title                   | author              | year_published |
+----+-------------------------+---------------------+----------------+
|  1 | In Search of Lost Time  | Marcel Proust       |           1913 |
|  2 | Ulysses                 | James Joyce         |           1922 |
|  3 | Don Quixote             | Miguel de Cervantes |           1605 |
+----+-------------------------+---------------------+----------------+
3 rows in set (0.00 sec)

1. INSERT IGNORE INTO

如果已经存在的数据，直接插入会出现错误，如：

mysql> INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

用 INSERT IGNORE 话，插入不成也会忽略错误：

mysql> INSERT IGNORE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
Query OK, 0 rows affected (0.00 sec)

2.REPLACE INTO

这个有几点要说：

1. 必须要有PrimaryKey，或者unique索引。
2. 如果不存在数据（基于PrimaryKey或unique索引），就执行插入，跟 insert into一样。
3. 如果存在数据（基于PrimaryKey或unique索引），它会先删除（跟delete 一样）已有的，然后重新插入（insert into 一样）。注意，如果你是基于unique索引，而非PrimaryKey，那么这个语句将会改变原来PrimaryKey值！
   基于PrimaryKey更新，PrimaryKey不会发生变化

   mysql> REPLACE INTO books
       (id, title, author, year_published)
   VALUES
       (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
   Query OK, 2 rows affected (0.00 sec)
   

   基于unique索引更新（本例为title），PrimaryKey会发生变化

   mysql> REPLACE INTO books
       (title, author, year_published)
   VALUES
       ( 'Green Eggs and Ham', 'Dr. Seuss', 1960);
   Query OK, 2 rows affected (0.00 sec)

   先删除，后添加，id值将发生变化，为自增到最大。

请注意，即使我们只更改了一行，结果也表明两行受到影响，因为我们实际上DELETED是现有行，然后INSERTED是新行来替换它。

3.INSERT … ON DUPLICATE KEY UPDATE

这个有几点要说：

1. 必须要有PrimaryKey，或者unique索引。
2. 如果不存在数据（基于PrimaryKey或unique索引），就执行插入，跟 insert into一样。
3. 如果存在数据（基于PrimaryKey或unique索引），它直接更新数据。注意，如果你是基于unique索引，而非PrimaryKey，那么这个语句将会改变原来最大PrimaryKey的值！
   

   mysql> SET @id = 1,
       @title = 'In Search of Lost Time',
       @author = 'Marcel Proust',
       @year_published = 1913;
   INSERT INTO books
       (id, title, author, year_published)
   VALUES
       (@id, @title, @author, @year_published)
   ON DUPLICATE KEY UPDATE
       title = @title,
       author = @author,
       year_published = @year_published;

   INSERT INTO books
       (id, title, author, year_published)
   VALUES
       (1, 'In Search of Lost Time', 'Marcel Proust', 1913)
   ON DUPLICATE KEY UPDATE
       title = VALUES(title),
       author = VALUES(author),
       year_published =VALUES(year_published),

   原有id值不发生变化，但是整个表的最大id值将发生变化，即自增一次。

   mysql> SELECT * FROM books LIMIT 1;
   +----+------------------------+---------------+----------------+
   | id | title                  | author        | year_published |
   +----+------------------------+---------------+----------------+
   |  1 | In Search of Lost Time | Marcel Proust |           1913 |
   +----+------------------------+---------------+----------------+
   1 row in set (0.00 sec)

    

INSERT ... ON DUPLICATE KEY UPDATE是非破坏性的，因为它仅会发出INSERT或UPDATE声明，但从来没有DELETE。

总结：

insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引，如果数据库已经存在数据，则用新数据替换，方法是先删除后添加，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert on duplicate key update表示，如果中已经存在相同的记录，则更新当前数据，否则将执行插入，跟replace into不一样的地方是，它不执行删除命令；

本文：Mysql: 存在更新，不存在插入, Insert if not exist otherwise update, mysql update or insert if not exists without primary key, replace into