当前位置： 文江博客话题详情

如何使用 PHP 检查 MySQL 表是否存在？

发布于 2024-11-16 13:21:10 字数 389 浏览 3 评论 0原文

理论上听起来很简单，我已经做了相当多的研究，但很难弄清楚这一点。

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

有办法做到这一点吗？

这就是我对 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");
}
原文

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)

Is there a way to do this?

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（12）

你的背包 2024-11-23 13:21:10
// 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，但另一方面，您不必担心处理大量额外数据。

编辑

因此，截至我撰写此消息时，该答案已至少被标记两次。假设我犯了一些巨大的错误，我跑了一些基准，这就是我发现我的当表不存在时，解决方案比最接近的替代方案快 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

我尝试针对 DESC 运行此解决方案，但在 276 秒后超时（我的回答需要 24 秒，无法完成对不存在表的描述需要 276 秒）。

为了更好地衡量，我正在对一个只有四个表的模式进行基准测试，这是一个几乎全新的 MySQL 安装（这是迄今为止唯一的数据库）。要查看导出内容，请查看此处。

此外，

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

最后，

对于不是“此表不存在”的错误，mysql_query() 返回 FALSE。

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

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

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:

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

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.

AND FURTHERMORE

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

FINALLY

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

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.

回复 原文
一身骄傲 2024-11-23 13:21:10

在 PHP 中实现此目的最简洁的方法是简单地使用 DESCRIBE 语句。

if ( mysql_query( "DESCRIBE `my_table`" ) ) {
    // my_table exists
}

我不确定为什么其他人要针对如此简单的问题发布复杂的查询。

使用 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    
}

The cleanest way to achieve this in PHP is to simply use DESCRIBE statement.

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

// assuming you have already setup $pdo
$sh = $pdo->prepare( "DESCRIBE `my_table`");
if ( $sh->execute() ) {
    // my_table exists
} else {
    // my_table does not exist    
}
回复 原文
爺獨霸怡葒院 2024-11-23 13:21:10
$res = mysql_query("SELECT table_name FROM information_schema.tables WHERE table_schema = '$databasename' AND table_name = '$tablename';");

如果没有返回记录，则该记录不存在。

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

回复 原文
残花月 2024-11-23 13:21:10

SHOW TABLES LIKE 'TableName'

如果您有任何结果，则该表存在。

要在 PDO 中使用此方法：

$pdo         = new \PDO(/*...*/);
$result      = $pdo->query("SHOW TABLES LIKE 'tableName'");
$tableExists = $result !== false && $result->rowCount() > 0;

要将此方法与已弃用的 mysql_query 结合使用

$result      = mysql_query("SHOW TABLES LIKE 'tableName'");
$tableExists = mysql_num_rows($result) > 0;

SHOW TABLES LIKE 'TableName'

If you have ANY results, the table exists.

To use this approach in 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

$result      = mysql_query("SHOW TABLES LIKE 'tableName'");
$tableExists = mysql_num_rows($result) > 0;
回复 原文
抽个烟儿 2024-11-23 13:21:10

125 微秒表存在检查

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

使用 mysql 分析测量的执行时间，无论表是否存在，大约为 0.000125 秒。 (125μs)

LIMIT 1 对于速度很重要。这最大限度地减少了排序结果和发送数据查询状态的时间。桌子的大小不是一个因素。

当不需要结果时，我总是使用无缓冲查询。

分析结果
当表不存在时

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

当表存在时

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

125 microsecond table exists check

.000125 sec. (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)

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.

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
回复 原文
失去的东西太少 2024-11-23 13:21:10
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

或 mysql_query("SHOW TABLES LIKE 'tblname'");

不要使用 mysql_list_tables(); 因为它已被弃用

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

Don't use mysql_list_tables(); because it's deprecated

回复 原文
生生漫 2024-11-23 13:21:10

甚至比糟糕的查询还要快：

SELECT count((1)) as `ct`  FROM INFORMATION_SCHEMA.TABLES where table_schema ='yourdatabasename' and table_name='yourtablename';

这样您就可以只检索一个字段和一个值。对于我较慢的系统，需要 0.016 秒。

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.

回复 原文
二货你真萌 2024-11-23 13:21:10

它已经发布了，但这里是 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";
}

对我来说就像一个魅力...

这是另一种方法（我认为它更慢）...

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

您也可以使用这个查询：

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'

我认为这是建议在 mysql 页面中使用的。

It was already posted but here it is with PDO (same query)...

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

回复 原文
桃扇骨 2024-11-23 13:21:10

不要再使用 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.

DO NOT USE MYSQL ANY MORE. If you must use mysqli but PDO is best:

$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.
回复 原文
穿透光 2024-11-23 13:21:10
<?php 
$connection = mysqli_connect("localhost","root","","php_sample_login_register"); 

if ($connection){
        echo "DB is Connected <br>";
        $sql = "CREATE TABLE user(
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
            name VARCHAR(255)NOT NULL,
            email VARCHAR(255)NOT NULL,
            password VARCHAR(255) NOT NULL
            );";
        if(mysqli_query($connection,$sql)) {
            echo "Created user table";
        } else{
            echo "User table already exists";
        }
    } else {
        echo "error : DB isnot connected";
    } ?>

<?php 
$connection = mysqli_connect("localhost","root","","php_sample_login_register"); 

if ($connection){
        echo "DB is Connected <br>";
        $sql = "CREATE TABLE user(
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
            name VARCHAR(255)NOT NULL,
            email VARCHAR(255)NOT NULL,
            password VARCHAR(255) NOT NULL
            );";
        if(mysqli_query($connection,$sql)) {
            echo "Created user table";
        } else{
            echo "User table already exists";
        }
    } else {
        echo "error : DB isnot connected";
    } ?>
回复 原文
最舍不得你 2024-11-23 13:21:10

或者您可以使用

show table where Tables_in_{insert_db_name}='tablename';

Or you could use

show tables where Tables_in_{insert_db_name}='tablename';

回复 原文
生活了然无味 2024-11-23 13:21:10

您可以使用许多不同的查询来检查表是否存在。以下是几个之间的比较：

mysql_query('select 1 from `table_name` group by 1'); or  
mysql_query('select count(*) from `table_name`');

mysql_query("DESCRIBE `table_name`");  
70000   rows: 24ms  
1000000 rows: 24ms  
5000000 rows: 24ms

mysql_query('select 1 from `table_name`');  
70000   rows: 19ms  
1000000 rows: 23ms  
5000000 rows: 29ms

mysql_query('select 1 from `table_name` group by 1'); or  
mysql_query('select count(*) from `table_name`');  
70000   rows: 18ms  
1000000 rows: 18ms  
5000000 rows: 18ms

这些基准只是平均值：

You can use many different queries to check if a table exists. Below is a comparison between several:

mysql_query('select 1 from `table_name` group by 1'); or  
mysql_query('select count(*) from `table_name`');

mysql_query("DESCRIBE `table_name`");  
70000   rows: 24ms  
1000000 rows: 24ms  
5000000 rows: 24ms

mysql_query('select 1 from `table_name`');  
70000   rows: 19ms  
1000000 rows: 23ms  
5000000 rows: 29ms

mysql_query('select 1 from `table_name` group by 1'); or  
mysql_query('select count(*) from `table_name`');  
70000   rows: 18ms  
1000000 rows: 18ms  
5000000 rows: 18ms

These benchmarks are only averages:

回复 原文
~没有更多了~

关于作者

吃兔兔

暂无简介

文章
评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

梦里南柯

文章 0 评论 0

不将就、

文章 0 评论 0

alipaysp_ZRaVhH1Dn

文章 0 评论 0

青衫儰鉨ミ守葔

文章 0 评论 0

故事未完

文章 0 评论 0

梦晓ヶ微光ヅ倾城

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文