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

Dear 丶 2022-06-16 01:28 170阅读 0赞

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)