How to Efficiently Check for Table Existence in MySQL without Exceptions?

Checking Table Existence in MySQL without Exceptions

Problem

In MySQL, how can we determine the existence of a table without encountering an exception, particularly when using PHP with PDO? Parsing the results of "SHOW TABLES LIKE" is not the most efficient method. Is there a boolean query option available?

Solution

The most dependable and secure approach is to query the information_schema database using a prepared statement.

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

This approach provides the following advantages:

• Avoids parsing errors by directly querying the information_schema database.
• Utilizes a prepared statement for enhanced security against SQL injection attacks.
• Returns a boolean value (TRUE/FALSE) indicating the table's existence.

The above is the detailed content of How to Efficiently Check for Table Existence in MySQL without Exceptions?. For more information, please follow other related articles on the PHP Chinese website!