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

最新推荐文章于 2025-06-12 21:07:25 发布

深圳gg

最新推荐文章于 2025-06-12 21:07:25 发布

阅读量764

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/stevendbaguo/article/details/71078018

mysql 专栏收录该内容

54 篇文章

订阅专栏

本文演示了在MySQL 5.6.24版本中如何创建带有唯一键的表，并展示了INSERT、INSERT IGNORE、REPLACE语句及ON DUPLICATE KEY UPDATE子句的具体使用方法。通过实例对比了不同插入方式的效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)