MySql避免数据重复的3种方式

MySql中避免数据重复的三种方式

在项目开发过程中，我们有时候总会遇到数据重复的问题。而选择在新增唯一约束也是其中的一种方式。但是在新增了唯一约束之后，如果存在错误数据，通常会抛出异常，这样不是很友好。下面是避免数据重复抛出异常的3种方式

1. ignore
2. replace
3. on duplicate key update

首先我们建一张具有唯一约束的表

create table `user`
(
    `id`          int(11) unsigned not null auto_increment comment '主键id',
    `avatar_url`  varchar(255) default null comment '头像',
    `user_name`   varchar(125) default null comment '用户名',
    `mobile`      varchar(15)  default null comment '手机号码',
    `password`    varchar(125) default null comment '密码',
    `update_time` datetime     default current_timestamp on update current_timestamp comment '更新时间',
    `create_time` datetime     default current_timestamp comment '创建时间',
    primary key (`id`),
    unique key `unique_mobile` (`mobile`) using btree 
) engine = innodb
  default charset = utf8mb4
  collate = utf8mb4_general_ci comment ='用户表';
insert into user (id, mobile) values(1,'1234567890')

1、ignore 语法

使用主键primary或者唯一索引unique区分记录的唯一性,避免重复插入记录可以使用

# 如果数据存在重复，执行结果将会返回 0, 即忽略该条数据，不会录入到DB
insert ignore into `user` (`id`, `mobile`) values('', '');

复制表,避免重复记录

insert ignore into `user` (`mobile`) select `mobile` from `user_copy`;

2、replace 语法

语法格式

replace into `user`(`id`, `mobile`, ...) values (...);
replace into `user` (`id`, `mobile`, ...) select ...;
replace into `user` set `mobile` = 'value';

说明

replace 与 insert 很相似，replace执行过程如下：

1. 执行sql, 尝试将数据插入DB
2. 当数据存在重复，因为存在主键或唯一约束报错时
3. 将表中含有重复数据导致报错的行删除
4. 再尝试将数据插入DB

数据存在重复的判断标准: 表中是否存在primary key或unique索引, 如果不存在，那么replace语句毫无意义，与insert一致。

返回值

replace只会返回一个数，来指示受影响的行数。即：返回值=被删除的行数+被插入的行数，通过返回值，我们可以确定是否存在重复数据

3、on duplicate key update语法

说明

在insert into ...后面加上 on duplicate key update方法来实现。如果指定了on duplicate key update，并且插入行后会导致在一个unique索引或primary key中出现重复值，则执行update。

示例

-- 如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2
insert into user (id, mobile) values(1,'1234567890')
on duplicate key update update_time = now();