如何使用 PHP 检查 MySQL 表是否存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6432178/
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 can I check if a MySQL table exists with PHP?
提问by John Doe
As simple in theory as it sounds I've done a fair amount of research and am having trouble figuring this out.
理论上虽然听起来很简单，但我已经做了大量的研究，但在弄清楚这一点时遇到了麻烦。
How can I check if a MySQL table exists and if it does do something. (I guess a simple php if/else statement could work for this)
如何检查 MySQL 表是否存在以及它是否执行某些操作。（我想一个简单的 php if/else 语句可以解决这个问题）
Is there a way to do this?
有没有办法做到这一点？
This is what I have done with cwallenpoole's response:
这就是我对 cwallenpoole 的回应所做的：
mysql_connect("SERVER","USERNAME","PASSWORD");
mysql_select_db('DATABASE');
$val = mysql_query('select 1 from `TABLE`');
if($val !== FALSE)
{
print("Exists");
}else{
print("Doesn't exist");
}
回答by cwallenpoole
// Select 1 from table_name will return false if the table does not exist.
$val = mysql_query('select 1 from `table_name` LIMIT 1');
if($val !== FALSE)
{
//DO SOMETHING! IT EXISTS!
}
else
{
//I can't find it...
}
Admittedly, it is more Pythonic than of the PHP idiom, but on the other hand, you don't have to worry about dealing with a copious amount of extra data.
诚然，它比 PHP 习语更 Pythonic，但另一方面，您不必担心处理大量额外数据。
Edit
编辑
So, this answer has been marked down at least twice as of the time I am writing this message. Assuming that I had made some gargantuan error, I went and I ran some benchmarks, and this is what I found that my solution is over 10% faster than the nearest alternative when the table does not exist, and it over 25% faster when the table does exist:
因此，截至我撰写此消息时，此答案已被标记至少两次。假设我犯了一些巨大的错误，我去运行了一些基准测试，这就是我发现当表不存在时我的解决方案比最接近的替代方案快 10% 以上，当表不存在时它快 25% 以上表确实存在：
:::::::::::::::::::::::::BEGINNING NON-EXISTING TABLE::::::::::::::::::::::::::::::
23.35501408577 for bad select
25.408507823944 for select from schema num rows -- calls mysql_num_rows on select... from information_schema.
25.336688995361 for select from schema fetch row -- calls mysql_fetch_row on select... from information_schema result
50.669058799744 for SHOW TABLES FROM test
:::::::::::::::::::::::::BEGINNING EXISTING TABLE::::::::::::::::::::::::::::::
15.293519973755 for good select
20.784908056259 for select from schema num rows
21.038464069366 for select from schema fetch row
50.400309085846 for SHOW TABLES FROM test
I tried running this against DESC, but I had a timeout after 276 seconds (24 seconds for my answer, 276 to fail to complete the description of a non existing table).
我尝试针对 DESC 运行此程序，但在 276 秒后超时（我的答案为 24 秒，276 秒无法完成对非现有表的描述）。
For good measure, I am benchmarking against a schema with only four tables in it and this is an almost fresh MySQL install (this is the only database so far). To see the export, look here.
为了更好的衡量，我正在对一个只有四个表的模式进行基准测试，这是一个几乎全新的 MySQL 安装（这是迄今为止唯一的数据库）。要查看导出，请查看此处。
AND FURTHERMORE
以及更多
This particular solution is also more database independent as the same query will work in PgSQL and Oracle.
这个特殊的解决方案也更加独立于数据库，因为相同的查询将在 PgSQL 和 Oracle 中工作。
FINALLY
最后
mysql_query()returns FALSE for errors that aren't "this table doesn't exist".
mysql_query()对于不是“此表不存在”的错误，返回 FALSE。
If you need to guarantee that the table doesn'texist, use mysql_errno()to get the error code and compare it to the relevant MySQL errors.
如果需要保证该表不存在，可以使用mysql_errno()获取错误码并与相关的MySQL错误进行比较。
回答by Aleksey Korzun
The cleanest way to achieve this in PHP is to simply use DESCRIBE statement.
在 PHP 中实现这一点的最简洁方法是简单地使用 DESCRIBE 语句。
if ( mysql_query( "DESCRIBE `my_table`" ) ) {
// my_table exists
}
I'm not sure why others are posting complicated queries for a such a straight forward problem.
我不确定为什么其他人会针对如此直接的问题发布复杂的查询。
Update
更新
Using PDO
使用 PDO
// assuming you have already setup $pdo
$sh = $pdo->prepare( "DESCRIBE `my_table`");
if ( $sh->execute() ) {
// my_table exists
} else {
// my_table does not exist
}
回答by Nathan Romano
$res = mysql_query("SELECT table_name FROM information_schema.tables WHERE table_schema = '$databasename' AND table_name = '$tablename';");
If no records are returned then it doesn't exist.
如果没有返回记录，则它不存在。
回答by Misunderstood
125 microsecond table exists check
125 微秒表存在检查
.000125 sec. (125μs)
.000125 秒 (125μs)
mysql_unbuffered_query("SET profiling = 1"); // for profiling only
@mysql_unbuffered_query("SELECT 1 FROM `table` LIMIT 1 ");
if (mysql_errno() == 1146){
// NO EXISTING TABLE CODE GOES HERE
}
elseif(mysql_errno() > 0){
echo mysql_error();
}
$results = mysql_query("SHOW PROFILE"); // for profiling only
Execution time, measured using mysql profiling, when a table exists, or not, is about .000125 sec. (125μs)
当表存在与否时，使用 mysql 分析测量的执行时间约为0.000125 秒。(125μs)
The LIMIT 1 is important for speed. This minimizes the Sorting Result and Sending Data query State times. And table size is not a factor.
LIMIT 1 对速度很重要。这最大限度地减少了排序结果和发送数据查询状态时间。桌子大小不是一个因素。
I always use an unbuffered query when no results are required.
当不需要结果时，我总是使用无缓冲查询。
PROFILE RESULTS WHEN TABLE DOES NOT EXIST
表不存在时的分析结果
QUERY STATE SECONDS
-------------------- -------
starting 0.000025
checking permissions 0.000006
Opening tables 0.000065
query end 0.000005
closing tables 0.000003
freeing items 0.000013
logging slow query 0.000003
cleaning up 0.000003
TOTAL 0.000123 <<<<<<<<<<<< 123 microseconds
WHEN TABLE EXISTS
当表存在时
QUERY STATE SECONDS
-------------------- -------
starting 0.000024
checking permissions 0.000005
Opening tables 0.000013
System lock 0.000007
init 0.000006
optimizing 0.000003
statistics 0.000009
preparing 0.000008
executing 0.000003
Sending data 0.000019
end 0.000004
query end 0.000004
closing tables 0.000006
freeing items 0.00001
logging slow query 0.000003
cleaning up 0.000003
TOTAL 0.000127 <<<<<<<<<<<< 127 microseconds
回答by dynamic
mysql_query("SHOW TABLES FROM yourDB");
//> loop thru results and see if it exists
//> in this way with only one query one can check easly more table
or mysql_query("SHOW TABLES LIKE 'tblname'");
或者 mysql_query("SHOW TABLES LIKE 'tblname'");
Don't use mysql_list_tables();because it's deprecated
不要使用，mysql_list_tables();因为它已被弃用
回答by Kevin Nelson
SHOW TABLES LIKE 'TableName'
像'TableName'一样显示表格
If you have ANY results, the table exists.
如果您有任何结果，则该表存在。
To use this approach in PDO:
要在 PDO 中使用这种方法：
$pdo = new \PDO(/*...*/);
$result = $pdo->query("SHOW TABLES LIKE 'tableName'");
$tableExists = $result !== false && $result->rowCount() > 0;
To use this approach with DEPRECATED mysql_query
将此方法与 DEPRECATED mysql_query 一起使用
$result = mysql_query("SHOW TABLES LIKE 'tableName'");
$tableExists = mysql_num_rows($result) > 0;
回答by JasonMichael
Even faster than a bad query:
甚至比错误查询还要快：
SELECT count((1)) as `ct` FROM INFORMATION_SCHEMA.TABLES where table_schema ='yourdatabasename' and table_name='yourtablename';
This way you can just retrieve one field and one value. .016 seconds for my slower system.
这样您就可以只检索一个字段和一个值。我较慢的系统需要 0.016 秒。
回答by I_Minkov
It was already posted but here it is with PDO (same query)...
它已经发布，但这里是 PDO（相同的查询）...
$connection = new PDO ( "mysql:host=host_db; dbname=name_db", user_db, pass_db );
if ($connection->query ("DESCRIBE table_name" )) {
echo "exist";
} else {
echo "doesn't exist";
}
Works like a charm for me....
对我来说就像一个魅力......
And here's another approach (i think it is slower)...
这是另一种方法（我认为它更慢）......
if ($connection->query ( "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' AND table_name ='tbl_name'" )->fetch ()) {
echo "exist";
} else {
echo "doesn't exist";
}
You can also play with this query:
您还可以使用此查询：
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'
I think this was suggested to use in the mysql page.
我认为这被建议在 mysql 页面中使用。
回答by chx
DO NOT USE MYSQL ANY MORE. If you must use mysqli but PDO is best:
不要再使用 MYSQL。如果您必须使用 mysqli 但 PDO 是最好的：
$pdo = new PDO($dsn, $username, $pdo); // proper PDO init string here
if ($pdo->query("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name'")->fetch()) // table exists.
回答by DanielM
Or you could use
或者你可以使用
show tables where Tables_in_{insert_db_name}='tablename';
显示表，其中 Tables_in_{insert_db_name}='tablename';
