How to Check if Table Exists in MySQL

In this tutorial, we will learn how we can determine if a given table exists within a selected database using MySQL.

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:

Table of Contents
Great! Next, complete checkout for full access to GeekBits.
Welcome back! You've successfully signed in.
You've successfully subscribed to GeekBits.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info has been updated.
Your billing was not updated.