[数据库]---mysql 插入sql之 INSERT INTO和INSERT IGNORE INTO和REPLACE INTO和ON DUPLICATE KEY UPDATE比较应用

mysql插入一条数据方式有四种,接下来逐个说明验证:

前提:

有一个表,作为本次测试:

CREATE TABLE `zs_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_id_index` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

表名是zs_test;
包括主键id,唯一索引order_id和普通列name;

INSERT INTO

最普通,最常用的插入是:
INSERT INTO zs_test (id,name,order_id) VALUES (1,‘aa’,11);
此时库中有了一条数据,id=1;name=‘aa’;order_id=11

id	name	order_id
1	aa	11

---

INSERT IGNORE INTO

• 1.用这个插入一条,主键和唯一索引都是新的:

INSERT IGNORE INTO zs_test (id,name,order_id) VALUES (2,‘bb’,12);
也成功,受影响的行数是1,此时库中有两条数据,

id	name	order_id
1	aa	11
2	bb	12

当本次要插入的主键id和唯一索引order_id在库中都没有重复时,INSERT IGNORE INTO相当于INSERT INTO;

• 2.再插入一条,主键用了1,库中已存在的:

INSERT IGNORE INTO zs_test (id,name,order_id) VALUES (1,‘bb’,13);
没有报错,受影响的行数是0,但是数据库中的数据没有变化,

id	name	order_id
1	aa	11
2	bb	12

• 3.再插入一条,唯一索引用了11,库中已存在的:

INSERT IGNORE INTO zs_test (id,name,order_id) VALUES (3,‘bb’,11);
没有报错,受影响的行数是0,但是数据库中的数据没有变化,

id	name	order_id
1	aa	11
2	bb	12

当本次要插入的主键id或者唯一索引order_id在库中有任何一个重复时,INSERT IGNORE INTO相当于不执行任何操作;

---

REPLACE INTO

• 1.用这个插入一条,主键和唯一索引都是新的:

REPLACE INTO zs_test (id,name,order_id) VALUES (3,‘cc’,13);
也成功,受影响的行数是1,此时库中有三条数据,

id	name	order_id
1	aa	11
2	bb	12
3	cc	13

当本次要插入的主键id和唯一索引order_id在库中都没有重复时,REPLACE INTO相当于INSERT INTO;

• 2.再插入一条,主键用了1,库中已存在的:

REPLACE INTO zs_test (id,name,order_id) VALUES (1,‘bb’,14);
执行成功,受影响的行数是2,此时库中有三条数据,但是主键为1的那条数据,他的order_id变成了14,name也变成bb

id	name	order_id
1	bb	14
2	bb	12
3	cc	13

当本次要插入的主键id和唯一索引order_id在库中有任何一个重复时,REPLACE INTO相当于先删除原数据,再INSERT INTO本条数据,所以受影响行数是2;

---

ON DUPLICATE KEY UPDATE

• 1.用这个插入一条,主键和唯一索引都是新的:
  INSERT INTO zs_test (id,name,order_id) VALUES (5,‘aa’,15) ON DUPLICATE KEY UPDATE name=‘aabb’;
  成功,受影响的行数是1,此时库中有四条数据,

id	name	order_id
1	bb	14
2	bb	12
3	cc	13
5	aa	15

当本次要插入的主键id和唯一索引order_id在库中都没有重复时,ON DUPLICATE KEY UPDATE后面的部分相当于没写,只是简单的前面INSERT INTO的效果;

• 2.再插入一条,主键用了1,库中已存在的,唯一索引随意:
  INSERT INTO zs_test (id,name,order_id) VALUES (1,‘aa’,12) ON DUPLICATE KEY UPDATE name=‘aabb’;
  成功,受影响的行数是2,此时库中有四条数据,但主键为1的那条数据的name值变成了aabb

id	name	order_id
1	aabb	14
2	bb	12
3	cc	13
5	aa	15

当本次要插入的主键id库中有重复时,ON DUPLICATE KEY UPDATE相当于不改变数据,将后面update的值做修改;