如何在 MySQL 存储过程中使用 DROP TABLE IF EXISTS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3620602/
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
How to use DROP TABLE IF EXISTS in a MySQL Stored Procedure
提问by Coach John
I want to know how to use DROP TABLE IF EXISTSin a MySQLstored procedure.
I'm writing a rather long mySQL Stored Procedure that will do a bunch of work and then load up a temp table with the results. However, I am having trouble making this work.
我想知道如何DROP TABLE IF EXISTS在 MySQL 存储过程中使用。我正在编写一个相当长的 mySQL 存储过程，它将完成一系列工作，然后加载一个带有结果的临时表。但是，我无法完成这项工作。
I've seen a few ways to do the temp table thing. Basically, you either create the temp table, work on it, and then drop it at the end ... or you drop it if it exists, create it, and then do your work on it.
我已经看到了几种方法来做临时表的事情。基本上，您要么创建临时表，对其进行处理，然后在最后删除它……要么删除它（如果它存在），创建它，然后对其进行处理。
I prefer the second method so that you always start of clean, and it's a built-in check for the table's existence. However, I can't seem to get it to work:
我更喜欢第二种方法，这样你总是从干净开始，它是对表存在的内置检查。但是，我似乎无法让它工作：
Here are my examples:
以下是我的例子：
This Works:
这个作品：
DELIMITER//
DROP PROCEDURE IF EXISTS pTest//
CREATE PROCEDURE pTest()
BEGIN
CREATE TEMPORARY TABLE tblTest (
OrderDate varchar(200)
);
DROP TEMPORARY TABLE tblTest;
END//
DELIMITER ;
CALL pTest();
This Works:
这个作品：
DELIMITER//
DROP PROCEDURE IF EXISTS pTest//
CREATE PROCEDURE pTest()
BEGIN
DROP TEMPORARY TABLE tblTest;
CREATE TEMPORARY TABLE tblTest (
OrderDate varchar(200)
);
END//
DELIMITER ;
CALL pTest();
This does not:
这不会：
DELIMITER//
DROP PROCEDURE IF EXISTS pTest//
CREATE PROCEDURE pTest()
BEGIN
DROP TEMPORARY TABLE IF EXISTS tblTest;
CREATE TEMPORARY TABLE tblTest (
OrderDate varchar(200)
);
END//
DELIMITER ;
CALL pTest();
The first 2 work, but if that table exists (like if the procedure didn't finish or something), it'll obviously end with a "Table tblTest does not exist" error. The non-working example is what I'm looking for -- drop the table if its there and then recreate it so that I can start clean.
前 2 个工作，但如果该表存在（例如程序没有完成或其他事情），它显然会以“表 tblTest 不存在”错误结束。非工作示例是我正在寻找的 - 如果它在那里，则将其删除，然后重新创建它，以便我可以开始清理。
It feels like it's the "IF EXISTS" making this thing fail. I've copied code from all sorts of sites that do things very similar and in no case can I get a "DROP TABLE IF EXISTS..." to work. Ever.
感觉就像是“如果存在”使这件事失败了。我已经从各种网站复制了代码，这些网站做的事情非常相似，在任何情况下我都无法获得“DROP TABLE IF EXISTS ...”来工作。曾经。
Dev Server: mySQL Server version: 5.1.47-community Prod Server: mySQL Server version: 5.0.45-log
开发服务器：mySQL Server 版本：5.1.47-community Prod 服务器：mySQL Server 版本：5.0.45-log
We can't change db versions (DBAs won't allow it), so I'm stuck on what I have. Is this a bug in mySQL or in the Procedure?
我们无法更改 db 版本（DBA 不允许），所以我被困在我拥有的版本上。这是 mySQL 或过程中的错误吗？
Thanks.
谢谢。
回答by IvanD
It's an old question but it came up as I was looking for DROP TABLE IF EXISTS.
这是一个老问题，但在我寻找 DROP TABLE IF EXISTS 时出现了。
Your non-working code did not work on my MySQL 5.1.70 server.
您的非工作代码在我的 MySQL 5.1.70 服务器上不起作用。
All I had to do was add a space between DELIMITER and // on the first line, and everything worked fine.
我所要做的就是在第一行的 DELIMITER 和 // 之间添加一个空格，一切正常。
Working code:
工作代码：
DELIMITER //
DROP PROCEDURE IF EXISTS pTest//
CREATE PROCEDURE pTest()
BEGIN
DROP TEMPORARY TABLE IF EXISTS tblTest;
CREATE TEMPORARY TABLE tblTest (
OrderDate varchar(200)
);
END//
DELIMITER ;
回答by Ike Walker
I don't know why this is not working for you,but you should be able to work around the issue using a continue handler. If you put the DROP TABLEstatement into it's own BEGIN...ENDblock you can use a continue handler to ignore the error if the table does not exist.
我不知道为什么这对您不起作用，但是您应该能够使用 continue 处理程序解决该问题。如果将DROP TABLE语句放入它自己的BEGIN...END块中，则可以使用 continue 处理程序在表不存在的情况下忽略错误。
Try this:
尝试这个：
DELIMITER //
DROP PROCEDURE IF EXISTS pTest //
CREATE PROCEDURE pTest()
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' BEGIN END;
DROP TEMPORARY TABLE tblTest;
END;
CREATE TEMPORARY TABLE tblTest (
OrderDate varchar(200)
);
END //
DELIMITER ;
CALL pTest();
回答by Abraham Brookes
I also had the same problem. It seems MySQL doesn't like to check if the table exists on some versions or something. I worked around the issue by querying the database first, and if I found a table I dropped it. Using PHP:
我也有同样的问题。似乎 MySQL 不喜欢检查该表是否存在于某些版本或其他东西上。我通过首先查询数据库解决了这个问题，如果我找到了一个表，我就把它删除了。使用 PHP：
$q = @mysql_query("SELECT * FROM `$name`");
if ($q){
$q = mysql_query("DROP TABLE `$name`");
if(!$q) die('e: Could not drop the table '.mysql_error());
}
You suppress the error in the first query with the @ symbol, so you don't have an interfering error, and then drop the table when the query returns false.
您在第一个查询中使用 @ 符号抑制错误，因此您没有干扰错误，然后在查询返回 false 时删除该表。
回答by Abraham Brookes
I recommend to add new line
我建议添加新行
SET sql_notes = 0// before DROP PROCEDURE IF EXISTS get_table //
Otherwise it will show warning PROCEDURE does not exists.
否则会显示警告 PROCEDURE 不存在。
