下面是我的数据库表。
User
id role type name
1 1 1 John
2 2 1 Doe
以下是我的数据:
role = 1
type = 1
name = HelloWorld
role = 1
type = 2
name = HelloWorld
rule = 3
type = 1
name = HelloWorld
我希望在我的数据库表中出现以下结果。
User
id role type name
1 1 1 HelloWorld // updated name because role = 1 and type = 1 exist.
2 2 1 Doe
3 1 2 HelloWorld // inserted name because role = 1 and type = 2 do not exist.
4 3 1 Helloworld // inserted name because role = 1 and type = 2 do not exist.
如何在不先执行查询的情况下编写 MySQL 查询select
?在我的情况下,没有主键。
>您可以使用MySQLinsert ... on duplicate key update ...
语法:
insert into mytable (role, type, name)
values (1, 1, 'Hello World')
on duplicate key update name = values(name)
为此,您需要在列(role, type)
上设置唯一的键约束。如果尚不存在,请创建它:
alter table mytable add constraint mytable_unique_role_type unique (role, type);
此语法还可用于一次处理多个插入:
insert into mytable (role, type, name)
values (1, 1, 'Hello World'), (1, 2, 'Hello World'), (3, 1, 'Hello World')
on duplicate key update name = values(name)