1. 背景
向数据库插入数据,希望去重插入。
2. 操作一下
一般去重插入有两种策略:
- 重复不插入
- 重复则替换(更新)
准备表:
CREATE TABLE `user_info` (
`id` int(11) NOT NULL COMMENT 'id',
`username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`idcard` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号',
`nick_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '昵称',
`remark` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idcard`(`idcard`) USING BTREE COMMENT '唯一身份标识'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
其中 id 是主键, idcard是唯一索引。
2.1 重复不插入策略
2.1.1 ignore
-- INSERT IGNORE
INSERT IGNORE INTO user_info (id, username, idcard, nick_name) VALUES (3,'zhangsan', '002', '');
此方案要求有主键或唯一索引,且是主键或唯一索引重复方可触发。
2.1.2 条件判断
-- NOT EXISTS 与ignore类似 重复则不插入
INSERT INTO user_info (id, username, idcard, nick_name) SELECT 1,'张无忌', '001', '-' FROM DUAL WHERE NOT EXISTS (SELECT idcard FROM user_info WHERE idcard = '001') ;
此方案不需要主键或唯一索引,当满足不存在条件时,执行插入
2.2 重复替换策略
2.2.1 ON duplicate KEY UPDATE
-- ON duplicate KEY UPDATE
INSERT INTO user_info (id, username, idcard, nick_name) VALUES (3,'zhangsan', '002', '哈哈') ON DUPLICATE KEY UPDATE username = '王五3',nick_name = '李白';
此方案要求有主键或唯一索引,且是主键或唯一索引重复方可触发。
注意:条件触发后,将是后面update语句生效。
2.2.2 REPLACE INTO
-- REPLACE INTO
REPLACE INTO user_info (id, username, idcard, nick_name) VALUES (1,'张无忌', '001', '-');
此方案要求有主键或唯一索引,且是主键或唯一索引重复方可触发。触发后将替换原有数据。
另外,重复替换策略语句生效后将造成两条数据影响。