使用 PHP/PDO 检查数据库表是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1717495/
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 a database table exists using PHP/PDO
提问by Andre
I want to check if a table with a specific name exists in a database I've connected to using PHP and PDO.
我想检查我使用 PHP 和 PDO 连接到的数据库中是否存在具有特定名称的表。
It has to work on all database backends, like MySQL, SQLite, etc.
它必须适用于所有数据库后端，如 MySQL、SQLite 等。
采纳答案by Milan Babu?kov
Do:
做：
select 1 from your_table
and then catch the error. If you don't get any error, but resultset with one column containing "1", then the table exists.
然后捕获错误。如果您没有收到任何错误，但结果集有一列包含“1”，则该表存在。
回答by exstral
Here's a complete function for checking if a table exists.
这是检查表是否存在的完整函数。
/**
* Check if a table exists in the current database.
*
* @param PDO $pdo PDO instance connected to a database.
* @param string $table Table to search for.
* @return bool TRUE if table exists, FALSE if no table found.
*/
function tableExists($pdo, $table) {
// Try a select statement against the table
// Run it in try/catch in case PDO is in ERRMODE_EXCEPTION.
try {
$result = $pdo->query("SELECT 1 FROM $table LIMIT 1");
} catch (Exception $e) {
// We got an exception == table not found
return FALSE;
}
// Result is either boolean FALSE (no table found) or PDOStatement Object (table found)
return $result !== FALSE;
}
Note: PDO will only throw exceptions if it is told to, by default it is silent and throws no exceptions. Thats why we need to check the result as well. See PDO error handling at php.net
注意：PDO 只会在被告知时抛出异常，默认情况下它是静默的并且不抛出异常。这就是为什么我们也需要检查结果。请参阅 php.net 上的 PDO 错误处理
回答by Nathan Crause
Before I go on, I do realise this is a MySQL-specific solution.
在我继续之前，我确实意识到这是一个特定于 MySQL 的解决方案。
While all the solutions mentioned here may work, I (personally) like to keep PDO from throwing exceptions (personal preference, that's all).
虽然这里提到的所有解决方案都可能有效，但我（个人）喜欢防止 PDO 抛出异常（个人偏好，仅此而已）。
As such, I use the following to test for table creation instead:
因此，我使用以下内容来测试表创建：
SHOW TABLES LIKE 'some_table_of_mine';
There's no error state generated if the table doesn't exist, you simply get a zero resultset. Works fast and consistently for me.
如果表不存在，则不会生成错误状态，您只会得到零结果集。对我来说快速且始终如一地工作。
回答by Equistatic
As part of your project, create a schema view.
作为项目的一部分，创建架构视图。
For Oracle it would be something like
对于 Oracle，它将类似于
SELECT TABLE_NAME FROM ALL_TABLES
For Mysql:
对于 MySQL：
SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = 'mydbname'
ETC..
等等..
And then run a query in your code against the view.
然后在您的代码中针对视图运行查询。
回答by targnation
Once you have your database handle via PDO, you can do this:
通过 PDO 获得数据库句柄后，您可以执行以下操作：
$tableExists = gettype($dbh->exec("SELECT count(*) FROM $table")) == 'integer';
Or wrap it in a function.
或者将它包装在一个函数中。
I tried messing around with try/catch at first, but even if the table did Not exist, there was no exception. Finally ended up with checking for the data type of the returned value from the dbh exec call. It's either an integer, if there is a match on the select count (even if there count is 0, or a boolean of false if there were no results.
一开始我试着用try/catch搞乱，但即使表不存在，也不例外。最后以检查 dbh exec 调用返回值的数据类型结束。如果选择计数匹配（即使计数为 0，则它是一个整数，或者如果没有结果则为 false 布尔值。
I think this should work with all the database types that PDO supports, since the syntax is really simple.
我认为这应该适用于 PDO 支持的所有数据库类型，因为语法非常简单。
回答by Yuri
At first, I was using the accepted answer, but then I noticed it was failing with empty tables. Here is the code I'm using right now:
起初，我使用了接受的答案，但后来我注意到它因空表而失败。这是我现在使用的代码：
function DB_table_exists($db, $table){
GLOBAL $db;
try{
$db->query("SELECT 1 FROM $db.$table");
} catch (PDOException $e){
return false;
}
return true;
}
This code is an extract of my extension class for PDO. It will produce an error (and return false) if the table doesn't exist, but will succeed if the table exists and/or is empty.
此代码是我的 PDO 扩展类的摘录。如果表不存在，它将产生错误（并返回 false），但如果表存在和/或为空，则会成功。
回答by Mat Barnett
You might be able to avoid having to rely on an error by using a query along the lines of "SHOW TABLES LIKE 'your_table'" and then counting the rows. I've been using this method successfully with MySQL and PDO but have yet to test it with other DBs
您可以通过使用类似“SHOW TABLES LIKE 'your_table'”的查询然后计算行数来避免依赖错误。我已经在 MySQL 和 PDO 上成功地使用了这种方法，但还没有用其他数据库进行测试
回答by RightHandedMonkey
This complete function is very similar to esbite's answer, but includes code to protect from SQL injection. Also, you may not get consistent results from the accepted answer when the table in question is empty.
这个完整的功能与 esbite 的答案非常相似，但包括防止 SQL 注入的代码。此外，当相关表格为空时，您可能无法从接受的答案中获得一致的结果。
/**
* This function checks if the table exists in the passed PDO database connection
* @param PDO $pdo - connection to PDO database table
* @param type $tableName
* @return boolean - true if table was found, false if not
*/
function tableExists(PDO $pdo, $tableName) {
$mrSql = "SHOW TABLES LIKE :table_name";
$mrStmt = $pdo->prepare($mrSql);
//protect from injection attacks
$mrStmt->bindParam(":table_name", $tableName, PDO::PARAM_STR);
$sqlResult = $mrStmt->execute();
if ($sqlResult) {
$row = $mrStmt->fetch(PDO::FETCH_NUM);
if ($row[0]) {
//table was found
return true;
} else {
//table was not found
return false;
}
} else {
//some PDO error occurred
echo("Could not check if table exists, Error: ".var_export($pdo->errorInfo(), true));
return false;
}
}
回答by Sairam
You could do a "select count(*) from table" query from php. If it returns an error or exception, the table doesn't exist. This could be the last possible resort and I am sure this works.
您可以从 php 执行“select count(*) from table”查询。如果它返回错误或异常，则该表不存在。这可能是最后的手段，我相信这行得通。
Or, you could check the schema table directly (probably requires additional permissions to be set by admin)
或者，您可以直接检查架构表（可能需要管理员设置额外的权限）
回答by Bruce Alderson
I do a few things in my web apps with CodeIgniter to check that the database exists (and is useful), any of these can work:
我使用 CodeIgniter 在我的网络应用程序中做了一些事情来检查数据库是否存在（并且有用），其中任何一个都可以工作：
@$this->load->database();
$v = @$this->db->version()
$tables = @$this->db->list_tables();
Adding the @will suppress errors if you have them enabled in your PHP setup, and checking the results of version()and list_tables()can be used to not only determine if your DB is around (but that it's sane too).
添加@将抑制错误，如果你有他们在你的PHP安装启用，检查的结果version()，并list_tables()可以用来不仅决定，如果你的数据库是左右（但它的理智太）。
