MySQL 选择是否存在，否则插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18286532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select if exist else insert?
提问by cheshirecatalyst
IF EXISTS (select * from users where username = 'something') THEN
select id from users where username = 'something';
ELSE
insert into users (username) values ('something');
END IF;
回答by fancyPants
Your statement is fine as it is. Only problem is, you can't use it like a normal query. Control structures like IFor WHILEare only allowed in stored procedures or functions.
你的陈述没有问题。唯一的问题是，您不能像普通查询一样使用它。像IF或WHILE仅允许在存储过程或函数中使用的控制结构。
Just create a procedure like this:
只需创建一个这样的过程：
delimiter $$
create procedure select_or_insert()
begin
IF EXISTS (select * from users where username = 'something') THEN
select id from users where username = 'something';
ELSE
insert into users (username) values ('something');
END IF;
end $$
and call it like this:
并这样称呼它：
call select_or_insert();
That's it.
就是这样。
回答by Rahul Tripathi
You can try like this:-
你可以这样试试：-
if exists( select * from mytable where field = 'something')
begin
select somefield from mytable where field = 'something';
end
else
begin
insert into users (username) values ('something');
end
or
或者
if not exists( select * from mytable where field = 'something')
begin
insert into users (username) values ('something');
end
else
begin
select somefield from mytable where field = 'something';
end
Although both the above queries are same.
尽管上述两个查询是相同的。
or try this using if:
或尝试使用如果：
IF EXISTS (select * from mytable where users = 'something')
select field from mytable where users = 'something'
else
into mytable (users) values ('something')
Here is the SQLFIDDLEDEMO
回答by firetiger77
Building upon the accepted answer, but only requiring one SELECT call (written for MySQL):
基于已接受的答案，但只需要一个 SELECT 调用（为 MySQL 编写）：
delimiter $$
create procedure select_or_insert()
begin
SET @myId := (SELECT id FROM users WHERE username = 'something');
IF (ISNULL(@myId))
THEN
INSERT INTO users (username) VALUES ('something');
END IF;
end $$
If desired, selecting newly-inserted elements can then be efficiently selected by using the LAST_INSERT_ID()function:
如果需要，可以使用以下LAST_INSERT_ID()功能有效地选择新插入的元素：
SELECT @myId := LAST_INSERT_ID();
