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

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

表中存在联合主键

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

继续执行以下 SQL

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

执行后数据内容如下

表中存在唯一索引

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

单个唯一索引

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

继续执行以下 SQL

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

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

多个唯一索引

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

单个唯一索引和自增主键

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

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

扩展

• 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