在用mysql做批量数据同步时, 经常会遇到的一种情况是: 批量获取的数据, 有些需要新增,有些需要修改。 mysql对此提供了两种解决方案
- REPLACE INTO 代替 INSERT INTO
- INSERT ... ON DUPLICATE KEY UPDATE ...
这两种方案, 都需要有主键或唯一索引, 当主键或唯一索引存在冲突时, 会执行修改数据,否则会插入数据。 下面我们具体看一下两种方案。
首先我们先创建表和索引:
show create table person; -- 查看建表语句
注意:这里我们表中既有主键ID,也有唯一索引---id_card , 且主键是自增的。至于为什么这么设计, 主要是为了对比两种方案的区别。
CREATE TABLE `person` (
`id` int NOT NULL AUTO_INCREMENT,
`id_card` bigint DEFAULT NULL,
`name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int DEFAULT NULL,
`high` double DEFAULT NULL,
`place` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`family` json DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_card` (`id_card`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
表结构:
索引:
数据:
1、REPLACE INTO
我们先修改后面两条数据,并插入一条新数据:
REPLACE INTO `zsm`.`person`(`id`, `id_card`, `name`, `age`, `high`, `place`, `family`)
VALUES (2, 2, '王三', 10, 172.1, '山东', NULL),
(3, 3, '王四', 22, 173.1, 'asd', NULL),
(4, 4, '王五', 22, 173.1, 'asd', NULL);
执行结果:
表中的数据:
从表中的数据我们可以看到, 修改了id 为2和3的数据, 新插入了一条数据,一共影响了3条数据, 但是sql执行输出的信息中说影响了5条数据。 这是因为,replace into 的操作过程是:
- 如果没有发现唯一性冲突,直接插入数据
- 如果发现了唯一性冲突,先删除原来的数据,再插入新的数据。
要验证这一点也很简单,我们再次执行上面的SQL语句,但是把主键ID列去掉,因为ID是自增的,如果mysql删除了原数据,再插入新数据,那么之间id会递增。
REPLACE INTO `zsm`.`person`(`id_card`, `name`, `age`, `high`, `place`, `family`)
VALUES (2, '王三', 10, 172.1, '山东', NULL),
(3, '王四', 22, 173.1, 'asd', NULL),
(4, '王五', 22, 173.1, 'asd', NULL);
执行结果:
表中的数据:
这里因为我之前有插入其它数据,所以数据的id在之前的序列上递增。
2、insert or update
insert or update 的语法格式:
INSERT INTO tableName ....
VALUES ....
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1), // 新值替换旧值
column2 = column2 + 1, // 值运算
....
执行SQL语句:
INSERT INTO `zsm`.`person`(`id_card`, `name`, `age`, `high`, `place`, `family`)
VALUES (2, '张三', 10, 172.1, '山东', NULL)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = age + 3;
执行结果:
表中的数据:
可以看到表中受影响的数据只有id_car=2的记录, 且id没有自增。 但是结果中依然显示有两行记录受影响, 这点暂时还没搞明白。如果有了解的朋友,还望指教。
这时如果我们修改一下SQL语句,只是去掉age = age + 3 的操作看一下执行结果:
INSERT INTO `zsm`.`person`(`id_card`, `name`, `age`, `high`, `place`, `family`)
VALUES (2, '张三', 10, 172.1, '山东', NULL)
ON DUPLICATE KEY UPDATE
name = VALUES(name);
执行结果:
没有数据被修改。 看来mysql是对要修改的列进行了对比, 只有当被修改的列发生变化时才会进行修改操作。
总结:
replace into : 存在唯一性冲突时,删除原数据,用新数据替换; 不存在唯一性冲突时,直接插入数据
insert or update : 可以设置存在冲突时可以更改的列,如果存在唯一性冲突,且设置要更改的列的值有变化,才会执行修改操作; 如果不存在唯一性冲突,直接插入数据。