MySQL check if a table exists without throwing an exception

Question

What is the best way to check if a table exists in MySQL (preferably via PDO in PHP) without throwing an exception. I do not feel like parsing the results of "SHOW TABLES LIKE" et cetera. There must be some sort of boolean query?

Solution 1

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();

Solution 2

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/

Solution 3

Using mysqli i've created following function. Asuming you have an mysqli instance called $con.

function table_exist($table){
    global $con;
    $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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[nickf]

maybe I'm missing something, but why would you use this method over SHOW TABLES?

[clops]

thanks, totally forgot that SHOW TABLES LIKE could be limited to one exact table only

[troelskn]

@nickf It's part of the ansi standard, so it's portable between different rdbms'es.

[Powerlord]

@nickf: It also works on databases other than MySQL. This includes PostgreSQL and SQL Server as far as I can tell.

[Talvi Watia]

wondering if this is a security exploit you can query information from databases you aren't connected to...

[Warren Rumak]

There is no security risk -- Queries to the information_schema database will only show tables that the connected user has privileges to.

[Reactgular]

PDO: $tableExists = $db->query("SHOW TABLES LIKE 'myTable'")->rowCount() > 0;

[Bill Karwin]

FWIW, MySQL, Microsoft, and PostgreSQL are the only RDBMS brands that implement INFORMATION_SCHEMA according to the standard. Most other databases have system tables, but of their own design.

[Jakub Matczak]

Please add accurate comment for the code to provide best quality of answer. Simply paste some code doesn't tell much to author of question.

[Rohit Chopra]

This is actually horrible. So if there are 50,000 tables, you would load all tables, loop through each one to find if the correct table exists?

[zPuls3]

mysqli : if ($db->query("SHOW TABLES LIKE 'myTable'")->num_rows==0) { // create table }

[datasn.io]

@MathewFoscarini, rowCount() may not be reliable in this case, see PHP doc.

[Falk]

Only if you let someone fill the $table var, not every var inside a sql statment is dangerous, only if you get the data from untrusted sources. Of course you are responsible of how you use the function and do the filtering. there is no need to downvote this answer.

[jcaron]

If you publish code like this, someone will end up using it in a place where the data has not been properly checked, and will end up with an SQL injection. Just use parameterised requests, and you will avoid any issue, whether the data has been checked or not. There is no reason whatsoever to not do so here, it's just bad practice.

[Falk]

How about adding a real_escape_string?

[jcaron]

Use parameterised queries and avoid the horror stories.

[TRiG]

There is no more support for mysql_* functions, they are officially deprecated, no longer maintained and will be removed in the future. You should update your code with PDO or MySQLi to ensure the functionality of your project in the future.

[ConstantineK]

This is generally the correct answer unless you have a corrupt database (like myself), querying this is currently reporting different values than the tables I can actually query from, so its not a 100% guarantee in that situation.

[Esoterica]

From what I read if 'SHOW' becomes inefficient then 'information_schema' is more preferred over 'DESCRIBE'.

[Your Common Sense]

How real_escape_string is even remotely related to table names?