MySQL 的“IF EXISTS”的用法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5528854/
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
Usage of MySQL's "IF EXISTS"
提问by C. E.
Here are two statements that I'd like to work, but which return error messages:
这是我想要使用的两个语句，但它们返回错误消息：
IF EXISTS (SELECT * FROM gdata_calendars WHERE `group` = ? AND id = ?) SELECT 1 ELSE SELECT 0
and
和
IF ((SELECT COUNT(*) FROM gdata_calendars WHERE `group` = ? AND id = ?) > 0) SELECT 1 ELSE SELECT 0;
The question marks are there because I use parametrized, prepared, statements with PHP's PDO. However, I have also tried executing this with data manually, and it really does not work.
有问号是因为我在 PHP 的 PDO 中使用了参数化的、准备好的语句。但是，我也尝试过手动使用数据执行此操作，但确实不起作用。
While I'd like to know why each of them doesn't work, I would prefer to use the first query if it can be made to work.
虽然我想知道为什么它们每个都不起作用，但如果可以使其工作，我更愿意使用第一个查询。
回答by RichardTheKiwi
You cannot use IF control block OUTSIDE of functions. So that affects both of your queries.
您不能在函数之外使用 IF 控制块。所以这会影响您的两个查询。
Turn the EXISTS clause into a subquery instead within an IF function
将 EXISTS 子句转换为子查询，而不是在 IF 函数中
SELECT IF( EXISTS(
SELECT *
FROM gdata_calendars
WHERE `group` = ? AND id = ?), 1, 0)
In fact, booleans are returned as 1 or 0
实际上，布尔值返回为 1 或 0
SELECT EXISTS(
SELECT *
FROM gdata_calendars
WHERE `group` = ? AND id = ?)
回答by SnowyR
I found the example RichardTheKiwiquite informative.
我发现RichardTheKiwi的例子非常有用。
Just to offer another approach if you're looking for something like IF EXISTS (SELECT 1 ..) THEN ...
如果您正在寻找类似的东西，请提供另一种方法 IF EXISTS (SELECT 1 ..) THEN ...
-- what I might write in MSSQL
-- 我可能会用 MSSQL 写什么
IF EXISTS (SELECT 1 FROM Table WHERE FieldValue='')
BEGIN
SELECT TableID FROM Table WHERE FieldValue=''
END
ELSE
BEGIN
INSERT INTO TABLE(FieldValue) VALUES('')
SELECT SCOPE_IDENTITY() AS TableID
END
-- rewritten for MySQL
-- 为 MySQL 重写
IF (SELECT 1 = 1 FROM Table WHERE FieldValue='') THEN
BEGIN
SELECT TableID FROM Table WHERE FieldValue='';
END;
ELSE
BEGIN
INSERT INTO Table (FieldValue) VALUES('');
SELECT LAST_INSERT_ID() AS TableID;
END;
END IF;
回答by Dilraj Singh
回答by user6096790
The accepted answer works well and one can also just use the
接受的答案效果很好，人们也可以只使用
If Exists (...) Then ... End If;
syntax in Mysql procedures (if acceptable for circumstance) and it will behave as desired/expected. Here's a link to a more thorough source/description: https://dba.stackexchange.com/questions/99120/if-exists-then-update-else-insert
Mysql 过程中的语法（如果情况可以接受），它将按照需要/预期的方式运行。这是更详尽的来源/描述的链接：https: //dba.stackexchange.com/questions/99120/if-exists-then-update-else-insert
One problem with the solution by @SnowyR is that it does not really behave like "If Exists" in that the (Select 1 = 1 ...) subquery could return more than one row in some circumstances and so it gives an error. I don't have permissions to respond to that answer directly so I thought I'd mention it here in case it saves someone else the trouble I experienced and so others might know that it is not an equivalent solution to MSSQLServer "if exists"!
@SnowyR 的解决方案的一个问题是它的行为不像“If Exists”，因为 (Select 1 = 1 ...) 子查询在某些情况下可能返回多于一行，因此它会给出错误。我没有直接回复那个答案的权限，所以我想我会在这里提到它，以防它为其他人节省我遇到的麻烦，所以其他人可能知道它不是 MSSQLServer“如果存在”的等效解决方案！
回答by Kader Khan
if exists(select * from db1.table1 where sno=1 )
begin
select * from db1.table1 where sno=1
end
else if (select * from db2.table1 where sno=1 )
begin
select * from db2.table1 where sno=1
end
else
begin
print 'the record does not exits'
end
