MySQL on duplicate key update 使用说明

本贴最后更新于 926 天前,其中的信息可能已经渤澥桑田

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)1values(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语句时,由于id1的数据已存在,所以更新id1的行,phonea1111111
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为张三为行数据sexphone更新
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,同时虽然phone2222222的行也存在,但是只会执行一次,类似于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唯一索引来更新旧行数据,所以更新的是id1的那行数据,将phone更新为a1111111
# !!!! 同时自增ID值增加了1,当下次插入数据时,可以看出来
insert into t4 (name, sex, phone)
VALUES ('张三', '女', 'a1111111')
on duplicate KEY UPDATE phone = values(phone);

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

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

id name sex phone
1 张三 a1111111
2 李四 2222222
3 老王 3333333
5 赵四 4444444

参考文章

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

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

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    676 引用 • 535 回帖 • 1 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...