MySQL check if a table exists without throwing an exception

小开

If you're using MySQL 5.0 and later, you could try:

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = '[database name]'
AND table_name = '[table name]';

Any results indicate the table exists.

From: http://www.electrictoolbox.com/check-if-mysql-table-exists/

小开

最佳答案

Querying the information_schema database using prepared statement looks like the most reliable and secure solution.

$sql = "SELECT 1 FROM information_schema.tables
WHERE table_schema = database() AND table_name = ?";
$stmt =  $pdo->prepare($sql);
$stmt->execute([$tableName]);
$exists = (bool)$stmt->fetchColumn();

小开

Using mysqli I've created following function. Assuming you have an mysqli instance called $con.

function table_exist($con, $table){
$table = $con->real_escape_string($table);
$sql = "show tables like '".$table."'";
$res = $con->query($sql);
return ($res->num_rows > 0);
}

Hope it helps.

Warning: as sugested by @jcaron this function could be vulnerable to sqlinjection attacs, so make sure your $table var is clean or even better use parameterised queries.

小开

Why you make it so hard to understand?

function table_exist($table){
$pTableExist = mysql_query("show tables like '".$table."'");
if ($rTableExist = mysql_fetch_array($pTableExist)) {
return "Yes";
}else{
return "No";
}
}

小开

$q = "SHOW TABLES";
$res = mysql_query($q, $con);
if ($res)
while ( $row = mysql_fetch_array($res, MYSQL_ASSOC) )
{
foreach( $row as $key => $value )
{
if ( $value = BTABLE )  // BTABLE IS A DEFINED NAME OF TABLE
echo "exist";
else
echo "not exist";
}
}

小开

Here is the my solution that I prefer when using stored procedures. Custom mysql function for check the table exists in current database.

delimiter $$


CREATE FUNCTION TABLE_EXISTS(_table_name VARCHAR(45))
RETURNS BOOLEAN
DETERMINISTIC READS SQL DATA
BEGIN
DECLARE _exists  TINYINT(1) DEFAULT 0;


SELECT COUNT(*) INTO _exists
FROM information_schema.tables
WHERE table_schema =  DATABASE()
AND table_name =  _table_name;


RETURN _exists;


END$$


SELECT TABLE_EXISTS('you_table_name') as _exists

小开

This is posted simply if anyone comes looking for this question. Even though its been answered a bit. Some of the replies make it more complex than it needed to be.

For mysql* I used :

if (mysqli_num_rows(
mysqli_query(
$con,"SHOW TABLES LIKE '" . $table . "'")
) > 0
or die ("No table set")
){

In PDO I used:

if ($con->query(
"SHOW TABLES LIKE '" . $table . "'"
)->rowCount() > 0
or die("No table set")
){

With this I just push the else condition into or. And for my needs I only simply need die. Though you can set or to other things. Some might prefer the if/ else if/else. Which is then to remove or and then supply if/else if/else.

小开

As a "Show tables" might be slow on larger databases, I recommend using "DESCRIBE " and check if you get true/false as a result

$tableExists = mysqli_query("DESCRIBE `myTable`");

小开

Zend framework

public function verifyTablesExists($tablesName)
{
$db = $this->getDefaultAdapter();
$config_db = $db->getConfig();


$sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{$config_db['dbname']}'  AND table_name = '{$tablesName}'";


$result = $db->fetchRow($sql);
return $result;


}

小开

If the reason for wanting to do this is is conditional table creation, then 'CREATE TABLE IF NOT EXISTS' seems ideal for the job. Until I discovered this, I used the 'DESCRIBE' method above. More info here: MySQL "CREATE TABLE IF NOT EXISTS" -> Error 1050