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
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
OUTparameter, 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,
TEMPORARYtable, 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
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:
TABLE_SCHEMAcolumn has the same value as the current database.
TABLE_NAMEcolumn has the same value as the input
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:
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.