tlbcc 的个人博客 tlbcc 的个人博客

记录精彩的程序人生

目录
MySQL on duplicate key update使用说明
/  

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语句执行完毕后数据内容如下

idnamesexphone
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语句执行后表中数据如下

idnamesexphone
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_1id_2value
11a
12b
13c

继续执行以下SQL

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

执行后数据内容如下

id_1id_2value
11a1
12b
13c

表中存在唯一索引

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

单个唯一索引

为了演示,创建一个表,并插入一些数据,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语句执行完毕后数据内容如下

namesexphone
张三1111111
李四2222222
老王3333333

继续执行以下SQL

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

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

namesexphone
张三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语句执行完毕后数据内容如下

namesexphone
张三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语句执行完毕后数据内容如下

idnamesexphone
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语句执行完毕后数据内容如下

idnamesexphone
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 on duplicate key update使用说明
作者:tlbcc
地址:http://blog.tlbcc.cc/articles/2022/04/02/1648844531943.html