How to Check if Table Exists in MySQL

1. What Is a Table in MySQL
2. Use the Information Schema to Check if Table Exists in MySQL
3. Use the SHOW TABLES Command to Check if Table Exists in MySQL
4. Use the table_exists() Procedure to Check if Table Exists in MySQL

This article offers several options to check table existence in MySQL. Before discussing it, let’s first see what a table is in MySQL and when we need to check its existence.

What Is a Table in MySQL

Tables are database objects that hold all of a database’s information. Tables are similar to spreadsheets in that data is logically structured in a row-and-column manner.

Each column shows a field in the record, and each row represents a distinct record. We can create a table in MySQL by simply calling the create statement.

CREATE TABLE table_name (table_id INT);

We can execute a drop statement to permanently delete a table.

DROP TABLE table_name;

Sometimes when we want to deploy a table to a database, we need to confirm that table has a unique name because otherwise, the CREATE TABLE statement will error.

In another case, if we drop a table that doesn’t exist, we will get an error again. The solution is simple; we need to check if a table exists and perform the operation we want.

Use the Information Schema to Check if Table Exists in MySQL

One way to check a table existence is to query the INFORMATION_SCHEMA.TABLES view. For this, we can get the count of tables that have the same name as our table from the INFORMATION_SCHEMA.TABLES.

CREATE TABLE table_name (table_id INT);
SELECT count(*)
FROM information_schema.TABLES
WHERE TABLE_NAME = 'table_name';

Output:

+----------+
| count(*) |
+----------+
|        1 |
+----------+

If we get zero, then there is no such table in our database, and if we get a non-zero number, then the table exists. You can check TABLE_SCHEMA = 'database_name' in the WHERE statement if you have several databases.

If you don’t know the database, you can query it with SELECT DATABASE(). A revised version of the code above with the database is below.

CREATE TABLE EMPLOYEE (table_id INT);
SELECT count(*) FROM information_schema.TABLES WHERE TABLE_NAME = 'EMPLOYEE'
AND TABLE_SCHEMA in (SELECT DATABASE());

Output:

+----------+
| count(*) |
+----------+
|        1 |
+----------+

Use the SHOW TABLES Command to Check if Table Exists in MySQL

The second way and pretty easy one is to use SHOW TABLES. Let’s create a table sampletable in the database and check if it exists.

CREATE TABLE sampletable (myId INT);
SHOW TABLES LIKE 'sampletable';

Output:

Tables_in_db_3xs4qrcrf (sampletable)
sampletable

If there is not any table, you will probably get zero rows. You need to know that this method doesn’t work on a temporary table.

A temporary table in MySQL is a particular sort of table that lets you save a temporary result set that you may reuse several times in a single session.

When querying data that needs a single SELECT statement with the JOIN clauses is difficult or costly, a temporary table is useful.

You may save the instant result in a temporary table and process it with another query in this scenario. We will discuss the method that works on the temporary table in the next method.

Use the table_exists() Procedure to Check if Table Exists in MySQL

After MySQL 5.7 new way was added to determine if a table or a view exists, including temporary tables. Before talking about this procedure, we first need to grasp the procedure.

A procedure is a database-stored subroutine (similar to a subprogram) in a conventional scripting language. Procedures are written in MySQL and saved in the MySQL database/server in the case of MySQL.

A MySQL process has three parts: a name, a list of parameters, and a SQL query(s). Now we can continue on our specific procedure, table_exists.

According to the MySQL documentation: “The procedure returns the table type in an OUT parameter. If both a temporary and a permanent table exist with the given name, TEMPORARY is returned”.

Let’s check it.

CREATE TABLE mytable (id INT PRIMARY KEY);
CALL sys.table_exists('mydatabase', 'mytable', @exists); SELECT @exists;

Output:

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

If there’s no such table in the database, you will get an empty result.

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