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?

Your Common Sense · Accepted Answer · 2022-05-09 10:07:51Z

202

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

edited May 9, 2022 at 10:07

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Oct 6, 2009 at 14:04

nickf

548k199 gold badges660 silver badges727 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

clops Over a year ago

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

Reactgular Over a year ago

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

zPuls3 Over a year ago

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

datasn.io Over a year ago

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

TRiG Over a year ago

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.

Michael Todd · Accepted Answer · 2009-10-06 14:03:49Z

40

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/

answered Oct 6, 2009 at 14:03

Michael Todd

17.2k4 gold badges52 silver badges70 bronze badges

7 Comments

nickf Over a year ago

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

troelskn Over a year ago

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

Powerlord Over a year ago

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

Talvi Watia Over a year ago

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

Warren Rumak Over a year ago

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

|

Your Common Sense · Accepted Answer · 2022-07-04 04:46:41Z

8

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.

edited Jul 4, 2022 at 4:46

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Oct 18, 2012 at 22:10

Falk

4971 gold badge7 silver badges19 bronze badges

8 Comments

Falk Over a year ago

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 Over a year ago

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 Over a year ago

How about adding a real_escape_string?

jcaron Over a year ago

Use parameterised queries and avoid the horror stories.

Your Common Sense Over a year ago

I beg my pardon, I confused this answer with another one. You are right, in this particular case real_escape_string could be used.

|

Esoterica · Accepted Answer · 2013-11-19 17:04:04Z

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.

erandac · Accepted Answer · 2013-10-10 06:15:23Z

2

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

answered Oct 10, 2013 at 6:15

erandac

5755 silver badges8 bronze badges

Comments

John Green · Accepted Answer · 2016-01-29 07:22:08Z

2

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

edited Jan 29, 2016 at 7:22

John Green

13.5k3 gold badges31 silver badges52 bronze badges

answered Jul 24, 2015 at 9:04

Martin Lisicki

213 bronze badges

1 Comment

Esoterica Over a year ago

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

Zohaib Ahmad · Accepted Answer · 2013-09-18 14:33:19Z

-1

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

answered Sep 18, 2013 at 14:33

Zohaib Ahmad

174 bronze badges

2 Comments

Jakub Matczak Over a year ago

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 Over a year ago

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?

gilcierweb · Accepted Answer · 2016-04-18 16:56:05Z

-1

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;

    }

answered Apr 18, 2016 at 16:56

gilcierweb

2,7621 gold badge18 silver badges16 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:03:01Z

-1

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

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered May 31, 2016 at 6:47

Robin Andrews

3,84212 gold badges57 silver badges129 bronze badges

Comments

XHtml · Accepted Answer · 2024-05-28 02:00:57Z

-1

the problem with

CALL sys.table_exists(...

is you will need high privileges, and using OTHERS options (as say the title) maybe launch alert/warning/errors

Therefore after of you connect to your objective DB, you can run:

$db = 'database';
$Q = "SHOW TABLES WHERE `Tables_in_{$db}` LIKE 'the_table';";

the best with this QUERY is:

very simple
zero warnings (if table not exists)
zero errors (if table not exists)

answered May 28, 2024 at 2:00

XHtml

591 silver badge7 bronze badges

Comments

RocketDonkey · Accepted Answer · 2012-10-24 05:20:53Z

-9

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

edited Oct 24, 2012 at 5:20

RocketDonkey

37.4k8 gold badges83 silver badges84 bronze badges

answered Oct 24, 2012 at 4:55

Hamed

3

Collectives™ on Stack Overflow

MySQL check if a table exists without throwing an exception

11 Answers 11

5 Comments

7 Comments

8 Comments

Comments

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

5 Comments

7 Comments

8 Comments

Comments

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related