9.11 Testing Whether a Table Exists
9.11.1 Problem
You want to know whether a table exists.
9.11.2 Solution
Use SHOW
TABLES to see if the table is listed.
9.11.3 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 and test whether
it succeed or fails.
 |
The SELECT * statement is
preferable to SELECT COUNT(*)
for some table types such as BDB or InnoDB, which require a full
table scan to evaluate COUNT(*). For ISAM and
MyISAM tables, the COUNT(*) statement is optimized
to use the record count stored in the table.
|
|
|
