如何使用 PHP 检查 MySQL 表是否存在? [英] How can I check if a MySQL table exists with PHP?

问题描述

理论上虽然听起来很简单，但我已经进行了大量研究，但在弄清楚这一点时遇到了麻烦.

As simple in theory as it sounds I've done a fair amount of research and am having trouble figuring this out.

如何检查 MySQL 表是否存在以及它是否执行某些操作.(我想一个简单的 php if/else 语句可以解决这个问题)

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)

有没有办法做到这一点?

Is there a way to do this?

这是我对 cwallenpoole 的回复所做的:

This is what I have done with cwallenpoole's response:

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");
}

推荐答案

// 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...
}

诚然，它比 PHP 习惯用法更 Pythonic，但另一方面，您不必担心处理大量额外数据.

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.

因此，截至我撰写此消息时，此答案已被标记至少两次.假设我犯了一些巨大的错误，我去运行了一些基准，这就是我发现的当表不存在时，我的解决方案比最近的替代方案快 10% 以上，当表存在时，它比最近的方案快 25%:

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:

:::::::::::::::::::::::::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

我尝试对 DESC 运行此程序，但在 276 秒后超时(我的回答为 24 秒，276 秒无法完成对不存在的表的描述).

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).

为了更好的衡量，我正在对一个只有四个表的架构进行基准测试，这是一个几乎全新的 MySQL 安装(这是迄今为止唯一的数据库).要查看导出，请查看此处.

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.

这个特殊的解决方案也更加独立于数据库，因为相同的查询将在 PgSQL 和 Oracle 中工作.

This particular solution is also more database independent as the same query will work in PgSQL and Oracle.

mysql_query() 对于不是此表不存在"的错误返回 FALSE.

mysql_query() returns FALSE for errors that aren't "this table doesn't exist".

如果需要保证表不存在，使用mysql_errno()获取错误码并与相关的MySQL 错误.

If you need to guarantee that the table doesn't exist, use mysql_errno() to get the error code and compare it to the relevant MySQL errors.

这篇关于如何使用 PHP 检查 MySQL 表是否存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！