JIYIK CN >

This article provides several options to check if a table exists in MySQL. Before discussing it, let us first see what a table is in MySQL and when you need to check its existence.

What is a table in MySQL?

A table is a database object that holds all the database information. A table is similar to a spreadsheet in that the data is logically structured in rows and columns.

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

CREATE TABLE table_name (table_id INT);

We can execute dropthe 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 the table has a unique name, otherwise, CREATE TABLEthe statement will fail.

In the other case, if we drop a table that does not exist, we will get the error again. The solution is simple; we need to check if a table exists and perform the operation we want.

Check if a table exists in MySQL using information mode

One way to check if a table exists is to query INFORMATION_SCHEMA.TABLESthe . To do this, we can INFORMATION_SCHEMA.TABLESget the number of tables with the same name as ours from .

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, if we get a non-zero number, then the table exists. If you have multiple databases, you can WHEREcheck this in the INSERT statement TABLE_SCHEMA = 'database_name'.

If you don't know the database, you can SELECT DATABASE()query it with . A revised version of the above code 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 SHOW TABLESthe command to check whether the table exists in MySQL

The second method is also very simple and is to use SHOW TABLES. Let's create a table in the database sampletableand check if it exists.

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

Output:

Tables_in_db_3xs4qrcrf (sampletable)
sampletable

If there aren't any tables, you may get zero rows. You need to know that this method does not work with temporary tables.

A temporary table in MySQL is a special type of table that allows you to save a temporary result set that you can reuse multiple times in a single session.

Temporary tables are useful when it is difficult or costly to query the data that you need in JOINa single statement with a INSERT clause .SELECT

In this case, you can save the immediate results in a temporary table and process them with another query. We will discuss the method that works with temporary tables in the next method.

Use table_exists()the procedure to check if the table exists in MySQL

MySQL 5.7 and later added a new method to determine whether a table or view exists, including temporary tables. Before talking about this procedure, we must first master this procedure.

Procedures are database stored subroutines (similar to subprograms) in traditional scripting languages. In the case of MySQL, procedures are written in MySQL and are stored in the MySQL database/server.

A MySQL procedure consists of three parts: its name, its parameter list, and its SQL query. Now we can move on to our particular procedure table_exists.

According to the MySQL documentation: 该过程在 OUT 参数中返回表类型。如果具有给定名称的临时表和永久表都存在，则返回 TEMPORARY.

Let's check it out.

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 is no such table in the database, you will get an empty result.

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