How to Check if Table Exists in MySQL

When working in MySQL databases, a need may arise where you need to check and verify if a given table exists in a database.

This can help prevent errors for example when creating a new table with similar name or before executing a batch of commands on the said table.

Create Example Table

Before learning how to do this, let us setup an example table as shown in the query below:

CREATE TABLE browser_session (
  user_id INT NOT NULL AUTO_INCREMENT,
  session_id VARCHAR(255) NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  PRIMARY KEY (session_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Once we have the tbale created, can proceed and test the existence of the table.

Method 1 - Usint the table_exists() Procedure

MySQL is an incredible database and you will often some cool procedures and features that make your work easier.

Once such stored procedure is the table_exists(). This procedure allows us to test whether a given table exists as regular table, a view, or a temporary table.

The function syntax is as shown:

table_exists(in_db, in_table, out_exists ENUM)

• in_db VARCHAR(64): The name of the database in which to check for table existence.
• in_table VARCHAR(64): The name of the table to check the existence of.
• out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY'): The return value. This is an OUT parameter, so it must be a variable into which the table type can be stored. When the procedure returns, the variable has one of the following values to indicate whether the table exists:
• '': The table name does not exist as a base table, TEMPORARY table, or view.
• BASE TABLE: The table name exists as a base (permanent) table.
• VIEW: The table name exists as a view.
• TEMPORARY: The table name exists as a TEMPORARY table.

You can use this stored procedure to test whether a table exists on a given database. It is good to note that this function will return a boolean value as true if a table exists or false if the table does not exist.

mysql> CALL sys.table_exists('sample_db', 'browser_session_temp', @exists); SELECT @exists;
Query OK, 0 rows affected (0.01 sec)

+-----------+
| @exists   |
+-----------+
| TEMPORARY |
+-----------+
1 row in set (0.00 sec)

mysql> CALL sys.table_exists('sample_db', 'browser_session', @exists); SELECT @exists;
Query OK, 0 rows affected (0.02 sec)

+------------+
| @exists    |
+------------+
| BASE TABLE |
+------------+
1 row in set (0.00 sec)

mysql> CALL sys.table_exists('sample_db', 'browser_session_v', @exists); SELECT @exists;
Query OK, 0 rows affected (0.02 sec)

+---------+
| @exists |
+---------+
| VIEW    |
+---------+
1 row in set (0.00 sec)

mysql> CALL sys.table_exists('sample_db', 'no_table', @exists); SELECT @exists;
Query OK, 0 rows affected (0.00 sec)

+---------+
| @exists |
+---------+
|         |
+---------+
1 row in set (0.00 sec)

From the output above, you can deduce fairly how we can use the table_exists() procedure to check whether a given table exists on a database.

Method 2 - Using a Custom User-Defined Function

We can also define a function to check whether a given table exists as shown:

CREATE PROCEDURE check_table_exists(IN table_name VARCHAR(255))
BEGIN
  DECLARE table_exists INT DEFAULT 0;
  SELECT COUNT(*) INTO table_exists FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = table_name;
  IF table_exists = 1 THEN
    SELECT 'Table exists';
  ELSE
    SELECT 'Table does not exist';
  END IF;
END;

The function starts by declaring a local variable table_exists and initializes it to 0. It then runs a SELECT statement to check if there is at least one row in the TABLES table of the information_schema database that meets the following conditions:

• The TABLE_SCHEMA column has the same value as the current database.
• The TABLE_NAME column has the same value as the input table_name parameter.

If there is at least one row that meets these conditions, it means that the table exists in the current database, so the value of table_exists is set to 1. Otherwise, it remains 0.

Finally, the function checks the value of table_exists and returns either 'Table exists' or 'Table does not exist' accordingly.

We can call the function as:

CALL check_table_exists('table_name');

Conclusion

In this tutorial, you learned how we can use the table_exists() stored procedure to check if a given table exists on a database. We also learned how we can define a custom function to accomplish the same. This can be very useful if the table_exists() procedure does not exist on the MySQL version.

We hope this tutorial helped. If it did, leave us a comment and share.

Check out our other series including MySQL, PostgreSQL, Cassandra, and more to expand your knowledge.

If you enjoy our content, please consider buying us a coffee to support our work: