MySQL——插入时主键重复则更新

MySQL on duplicate key update 使用说明

MySQL on duplicate key update

语法说明

# 在INSERT语句后使用
insert ... on duplicate key update

# on duplicate key update语句后可以跟列名赋值语句,多个用逗号分隔
insert into t1 (a,b,c) values (1,2,3) on duplicate key update a=1
insert into t1 (a,b,c) values (1,2,3) on duplicate key update a=1,b=2

# 赋值语句可以赋值为原本字段内容,但没有什么实际意义(更新通过主键或者唯一索引匹配上的行)
insert into t1 (a,b,c) values (1,2,3) on duplicate key update a=a,b=b

# 赋值语句可以赋值为新增语句时的值,例如下面的values(a)是1,values(b)是2(更新通过主键或者唯一索引匹配上的行)
insert into t1 (a,b,c) values (1,2,3) on duplicate key update a=values(a),b=values(b)
​

语法作用

当执行 insert 语句时,由于 主键唯一索引 导致的重复时,将根据 on duplicate key update 更新旧行数据

换句话说,on duplicate key update 子句中的 values(col_name) 是指在没有发生重复键冲突的情况下将插入的 col_name 的值,发生重复键冲突的情况下更新重复键的旧数据

使用方法

生效条件

  • 存在主键,且主键重复时

  • 存在自增主键,且主键重复时

  • 存在联合主键,且主键重复时

  • 存在唯一索引,且唯一索引重复时

不同条件下的使用方式

表中存在主键或自增主键

为了演示,创建一个表,并插入一些数据,SQL 语句如下

# 建表语句
create table t1
(
    id    bigint auto_increment
        primary key,
    name  varchar(50) null,
    sex   varchar(1)  null,
    phone varchar(20) null,
);

# 插入一些基础数据
insert into t1 (name, sex, phone)
values ('张三', '女', '1111111'),
       ('李四', '男', '2222222'),
       ('老王', '男', '3333333');
​

SQL 语句执行完毕后数据内容如下

id

name

sex

phone

1

张三

1111111

2

李四

2222222

3

老王

3333333

继续执行以下 SQL

# 执行insert语句时,由于id为1的数据已存在,所以更新id为1的行,phone为a1111111
insert into t1 (id, name, sex, phone)
VALUES (1, '张三', '女', 'a1111111')
on duplicate KEY UPDATE phone = values(phone);
​

SQL 语句执行后表中数据如下

id

name

sex

phone

1

张三

a1111111

2

李四

2222222

3

老王

3333333

表中存在联合主键

为了演示,创建一个表,并插入一些数据,SQL 语句如下

# 建表语句
create table t2
(
    id_1  bigint      not null,
    id_2  bigint      not null,
    value varchar(50) null,
    primary key (id_1, id_2)
);
# 插入一些基础数据
insert into t2
values (1, 1, 'a'),
       (1, 2, 'b'),
       (1, 3, 'c');
​

SQL 语句执行完毕后数据内容如下

id_1

id_2

value

1

1

a

1

2

b

1

3

c

继续执行以下 SQL

insert into t2 values (1,1,'a1') on duplicate key update value = values(value)
​

执行后数据内容如下

id_1

id_2

value

1

1

a1

1

2

b

1

3

c

表中存在唯一索引

表中存在唯一索引时,根据当前表的唯一索引数量或是否存在主键等会有不同的情况

单个唯一索引

为了演示,创建一个表,并插入一些数据,SQL 语句如下

# 建表语句
create table t3
(
    name  varchar(50) null,
    sex   varchar(1)  null,
    phone varchar(20) null,
    constraint t3_name_uindex
        unique (name)
);
# 插入一些基础数据
INSERT INTO t3 (name, sex, phone)
VALUES ('张三', '女', '1111111'),
       ('李四', '男', '2222222'),
       ('老王', '男', '3333333');
​

SQL 语句执行完毕后数据内容如下

name

sex

phone

张三

1111111

李四

2222222

老王

3333333

继续执行以下 SQL

# 由于name为张三的列已经存在,所以将name为张三为行数据sex和phone更新
insert into t3
values ('张三', '男', 'a1111111')
on duplicate key update sex  = values(sex),
                        phone = values(phone);
​

SQL 语句执行完毕后数据内容如下

name

sex

phone

张三

a1111111

李四

2222222

老王

3333333

多个唯一索引

继续使用 t3 表,t3 表增加一个唯一索引,SQL 如下

# 增加phone列的唯一索引
create unique index t3_phone_uindex
    on t3 (phone);
​

继续执行以下 SQL

# 由于name为张三的数据已存在,所以更新张三那行的sex,同时虽然phone为2222222的行也存在,但是只会执行一次,类似于update ... limit 1,所以只有张三变成了女,而李四还是男
insert into t3 values ('张三', '女', '2222222') on duplicate key update sex = values(sex);
​

SQL 语句执行完毕后数据内容如下

name

sex

phone

张三

a1111111

李四

2222222

老王

3333333

单个唯一索引和自增主键

为了演示,创建一个表,并插入一些数据,SQL 语句如下

# 建表语句
create table t4
(
    id    bigint auto_increment
        primary key,
    name  varchar(50) null,
    sex   varchar(1)  null,
    phone varchar(20) null,
    constraint t4_name_sex_uindex
        unique (name, sex)
);
# 插入一些基础数据
INSERT INTO t4 (name, sex, phone)
VALUES ('张三', '女', '1111111'),
       ('李四', '男', '2222222'),
       ('老王', '男', '3333333');
​

SQL 语句执行完毕后数据内容如下

id

name

sex

phone

1

张三

1111111

2

李四

2222222

3

老王

3333333

继续执行一些 SQL

# 当前SQL通过t4_name_sex_uindex唯一索引来更新旧行数据,所以更新的是id为1的那行数据,将phone更新为a1111111
# !!!! 同时自增ID值增加了1,当下次插入数据时,可以看出来
insert into t4 (name, sex, phone)
VALUES ('张三', '女', 'a1111111')
on duplicate KEY UPDATE phone = values(phone);

# 由于执行了上一条SQL导致自增ID增加1,所以这条数据插入后id为5
insert into t4 (name, sex, phone) value ('赵四', '男', '4444444');
​

SQL 语句执行完毕后数据内容如下

id

name

sex

phone

1

张三

a1111111

2

李四

2222222

3

老王

3333333

5

赵四

4444444

扩展

  • 1.insert ignore into:当数据库中没有要插入的这条数据时会和普通的insert into语句一样,如果已经存在了,则会忽略掉这条插入。例子如下:

insert ignore into admin(name, password, email)
values ("mcj", "234567", "")
  • 2.replace into:当数据库中已经存在要插入的数据时,会把数据库中已存在的数据进行删除,然后再重新插入,如果不存在的话,则直接插入。例子如下:

replace into admin(name, password, email)
values
("mcj", "123456", "")

参考文章

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

https://blog.csdn.net/qq_22771739/article/details/84668620


MySQL——插入时主键重复则更新
https://shikai.info/archives/mysql-on-duplicate-key-update
作者
石 凯
发布于
2023年07月30日
许可协议