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.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net