MySQL 如果表存在则删除行 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9803679/
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
Delete row if table exists SQL
提问by Alex Edwards
I have a script that drops a load of tables using DROP TABLE IF EXISTS, this works.
我有一个脚本，它使用 DROP TABLE IF EXISTS 删除大量表，这是有效的。
There is also a delete in this script to DELETE a row from another table that I do not manage. This table may or may not exist.Is there any to check the table exists before attempting to delete a row?
此脚本中还有一个删除功能，用于从我不管理的另一个表中删除一行。这个表可能存在也可能不存在。在尝试删除一行之前是否有任何检查表是否存在？
this needs to work for MYSQL and SQLServer
这需要适用于 MYSQL 和 SQLServer
thanks Alex
谢谢亚历克斯
采纳答案by Alex Edwards
To check in SQL SERVER,
要签入 SQL SERVER，
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TheSchema' AND TABLE_NAME = 'TheTable'))
BEGIN
--Do Stuff
END
To check in mysql:
要检查 mysql：
You simply count:
您只需计算：
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = '[database name]'
AND table_name = '[table name]';
回答by Alex Edwards
This one deletes the row and does not complain if it can't.
这将删除该行，如果不能，则不会抱怨。
DELETE IGNORE FROM table WHERE id=1
回答by Diego
I dont think you'll find a common syntax between SQL server and my SQL. I mean, you can check if the table exsits on SQL Server using something like:
我认为您不会在 SQL 服务器和我的 SQL 之间找到通用语法。我的意思是，您可以使用以下方法检查表是否存在于 SQL Server 上：
if exists(select * from sys.objects where name like 'table_name')
but mySql would have its own catalog.
但是 mySql 会有自己的目录。
Unless you write a script like:
除非你写一个像这样的脚本：
if (sql_server) then
if exists(select * from sys.objects where name like 'table_name')
else --mySQl
--execute the mysql script
回答by Darren
For SQL Server: You could use:
对于 SQL Server：您可以使用：
IF OBJECT_ID('tablename','U') IS NOT NULL
回答by Chris Gessler
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABLE_NAME]') AND type in (N'U'))
回答by Martijn
A question you want to ask yourself (in terms of database design): Why are you trying to delete rows from a table you are not sure exists? If it doesn't, but you expect it does, wouldn't you rather create the table than not delete it?
您想问自己一个问题（在数据库设计方面）：为什么要尝试从不确定的表中删除行？如果没有，但您希望它有，您是否宁愿创建表而不是不删除它？
Anyway, Chris Gesslers answer does exactly what you are asking in SQL Server, but there is some smell here.
无论如何，Chris Gesslers 的回答完全符合您在 SQL Server 中的要求，但这里有一些味道。
The construct in MySQL you can use is
您可以使用的 MySQL 中的构造是
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename'
and check for results
并检查结果
回答by Chetter Hummin
For MySQL
对于 MySQL
show tables like "test1";
For SQL Server
对于 SQL Server
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'testSchema' AND TABLE_NAME = 'test1'
回答by MrTJ
It seems to me right the first item in the "Related" column on the right side answers your question.... Check if table exists in SQL Server
在我看来，右侧“相关”列中的第一项回答了您的问题....检查 SQL Server 中是否存在表
回答by Reza Jenabi
you can use bellow code:
您可以使用以下代码：
DECLARE @TABLENAME VARCHAR(20)='TableName';
IF (OBJECT_ID(@TABLENAME) IS NOT NULL )
BEGIN
execute(N'TRUNCATE TABLE ' + @TABLENAME + '' );
END
ELSE
BEGIN
PRINT 'Table NOT Exists'
END
