Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

Testing Whether a Table Exists

Problem

You want to know whether a table exists.

Solution

Use SHOW TABLES to see if the table is listed.

Discussion

You can use the SHOW TABLES statement to test whether a specific table exists by adding a LIKE clause that matches the name of the table:

SHOW TABLES LIKE 'tbl_name';
SHOW TABLES FROM db_name LIKE 'tbl_name';

If you get a row back, the table exists. If not, it doesn’t. Here’s a Perl function that performs an existence test for a table:

sub table_exists
{
my ($dbh, $tbl_name) = @_;
my $db_clause = "";

    ($db_clause, $tbl_name) = (" FROM $1", $2) if $tbl_name =~ /(.*)\.(.*)/;
    $tbl_name =~ s/([%_])/\\$1/g;   # escape any special characters
    return ($dbh->selectrow_array ("SHOW TABLES $db_clause LIKE '$tbl_name'"));
}

The function checks the table name argument to see if it’s in db_name.tbl_name form. If so, it strips off the database name and uses it to add a FROM clause to the statement. Otherwise, the test is against the current database. Note that the function returns false if the table exists but you have no privileges for accessing it.

There are other ways to check whether or not a table exists besides SHOW TABLES. Either of the following SELECT statements will execute successfully if the table exists, and fail if it does not:

SELECT * FROM tbl_name WHERE 1=0;
SELECT COUNT(*) FROM tbl_name;

To use these statements within a program, first set your API’s error trapping not to terminate your program on an error. Then attempt to execute the statement ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata