MySQL 几种DML方式：ignore、replace into、DUPLICATE KEY UPDATE、类似merge into

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.24-log |
+------------+

DROP TABLE t;

CREATE TABLE t
(
  id INT NOT NULL PRIMARY KEY,
  id1 INT AUTO_INCREMENT,
  NAME VARCHAR(10),
  UNIQUE KEY(id1)
);

INSERT INTO t(id,NAME) VALUE(1,'gg1');
mysql> select * from t;
+----+-----+------+
| id | id1 | name |
+----+-----+------+
|  1 |   1 | gg1  |
+----+-----+------+
1 row in set (0.00 sec)

mysql> INSERT INTO t(id,NAME) VALUE(1,'gg1');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> INSERT IGNORE INTO t(id,NAME) VALUE(1,'gg1');
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+----+-----+------+
| id | id1 | name |
+----+-----+------+
|  1 |   1 | gg1  |
+----+-----+------+
1 row in set (0.00 sec)

mysql> REPLACE INTO t(id,NAME) VALUES(1,'gg1');
Query OK, 2 rows affected (0.00 sec)
mysql> select * from t;
+----+-----+------+
| id | id1 | name |
+----+-----+------+
|  1 |   4 | gg1  |
+----+-----+------+

1 row in set (0.00 sec)

mysql> INSERT INTO t(id,NAME)
  VALUES(1,'gg10')
  ON DUPLICATE KEY UPDATE NAME='gg10';
 mysql> SELECT * FROM t;
+----+-----+------+
| id | id1 | NAME |
+----+-----+------+
|  1 |   4 | gg10 |
+----+-----+------+

mysql> UPDATE t AS a INNER JOIN
(SELECT 1 id,'gg2' NAME )  b 
USING (id)
SET a.id =  IF(a.id= b.id,b.id,a.id),
    a.name = IF(a.id= b.id,b.name,a.name);
mysql> select * from t;
+----+-----+------+
| id | id1 | name |
+----+-----+------+
|  1 |   4 | gg2  |
+----+-----+------+
1 row in set (0.00 sec)