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 FROMdb_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 * FROMtbl_name
WHERE 1=0; SELECT COUNT(*) FROMtbl_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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.