MySQL 在不使用“select from”的情况下检查表是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8829102/
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
Check if table exists without using "select from"
提问by Ben
Is there a way to check if a table exists withoutselecting and checking values from it?
有没有办法在不选择和检查表中的值的情况下检查表是否存在？
That is, I know I can go SELECT testcol FROM testtableand check the count of fields returned, but it seems there must be a more direct / elegant way to do it.
也就是说，我知道我可以去SELECT testcol FROM testtable检查返回的字段数，但似乎必须有一种更直接/优雅的方法来做到这一点。
回答by Sergio Tulentsev
If you want to be correct, use INFORMATION_SCHEMA.
如果您想正确，请使用INFORMATION_SCHEMA。
SELECT *
FROM information_schema.tables
WHERE table_schema = 'yourdb'
AND table_name = 'testtable'
LIMIT 1;
Alternatively, you can use SHOW TABLES
或者，您可以使用 SHOW TABLES
SHOW TABLES LIKE 'yourtable';
If there is a row in the resultset, table exists.
如果结果集中有一行，则表存在。
回答by Marc B
SELECT count(*)
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = 'your_db_name') AND (TABLE_NAME = 'name_of_table')
if you get a non-zero count, the table exists.
如果您得到非零计数，则该表存在。
回答by Ken Fricklas
A performance comparison:
性能对比：
- MySQL 5.0.77, on a db that has about 11,000 tables.
- Selecting a non-recently-used table so it's not cached.
- Averaged over 10 tries each. (Note: done with different tables to avoid caching).
- MySQL 5.0.77，在一个有大约 11,000 个表的数据库上。
- 选择一个最近未使用的表，使其不被缓存。
- 平均每次尝试超过 10 次。（注意：使用不同的表完成以避免缓存）。
322ms: show tables like 'table201608';
322 毫秒： show tables like 'table201608';
691ms: select 1 from table201608 limit 1;
691 毫秒： select 1 from table201608 limit 1;
319ms: SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'mydb') AND (TABLE_NAME = 'table201608');
319 毫秒： SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'mydb') AND (TABLE_NAME = 'table201608');
Note if you're running this a lot -- like over many HTML requests in a short period -- the 2nd will be way faster since it'll be cached an average 200 ms or faster.
请注意，如果您经常运行它——就像在短时间内处理许多 HTML 请求一样——第二个会更快，因为它平均会被缓存 200 毫秒或更快。
回答by doogle
You can query the INFORMATION_SCHEMA tablessystem view:
您可以查询 INFORMATION_SCHEMAtables系统视图：
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'testtable';
If no rows returned, then the table doesn't exist.
如果没有返回行，则表不存在。
回答by ta.speot.is
Rather than relying on errors, you can query INFORMATION_SCHEMA.TABLESto see if the table exists. If there's a record, it exists. If there's no record, it doesn't exist.
您可以查询INFORMATION_SCHEMA.TABLES以查看该表是否存在，而不是依赖于错误。如果有记录，它就存在。如果没有记录，它就不存在。
回答by csukcc
Here is a table that is not a SELECT * FROM
这是一个不是 SELECT * FROM 的表
SHOW TABLES FROM `db` LIKE 'tablename'; //zero rows = not exist
Got this from a database pro, here is what I was told:
从数据库专家那里得到了这个，这是我被告知的：
select 1 from `tablename`; //avoids a function call
select * from IMFORMATION_SCHEMA.tables where schema = 'db' and table = 'table' // slow. Field names not accurate
SHOW TABLES FROM `db` LIKE 'tablename'; //zero rows = not exist
回答by StationaryTraveller
After reading all of the above, I prefer the following statement:
阅读完以上所有内容后，我更喜欢以下陈述：
SELECT EXISTS(
SELECT * FROM information_schema.tables
WHERE table_schema = 'db'
AND table_name = 'table'
);
It indicates exactly what you want to do and it actually returns a 'boolean'.
它准确地指示您想要做什么，并且它实际上返回一个“布尔值”。
回答by Martin
This modified solution from above does not require explicit knowledge of the current database. It is then more flexible.
上面的这个修改后的解决方案不需要对当前数据库的明确了解。然后它更灵活。
SELECT count(*) FROM information_schema.TABLES WHERE TABLE_NAME = 'yourtable'
AND TABLE_SCHEMA in (SELECT DATABASE());
回答by Stefan
show tables like 'table_name'
显示像“table_name”这样的表格
if this returns rows > 0 the table exists
如果这返回行 > 0 表存在
回答by eracuna
Just to add an extra way to do it, and dependingon what you need it for you could use a handlerfor er_no_such_tableerror:1146 like this:
只是添加一种额外的方法来做到这一点，根据您的需要，您可以使用处理程序来处理er_no_such_table错误：1146，如下所示：
DELIMITER ;;
CREATE PROCEDURE `insert_in_my_table`(in my_var INT)
BEGIN
-- Error number for table not found
DECLARE CONTINUE HANDLER FOR 1146
BEGIN
-- table doesn't exists, do something...
CREATE TABLE my_table(n INT);
INSERT INTO my_table (n) values(my_var);
END;
-- table does exists, do something...
INSERT INTO my_table (n) values(my_var);
END ;;
DELIMITER ;
