In MySQL, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table of the same name already exists in the database.

If the table doesn’t exist, it will be created. If it already exists, it won’t be created.

Example

It goes like this:

CREATE TABLE IF NOT EXISTS t1 (
  c1 INT,
  c2 VARCHAR(10)
);

Where t1 is the table name, and everything between the parentheses is the table definition (i.e. columns, etc).

In that case, the table will only be created if there isn’t already one called t1.

Check that the Table Now Exists

We can use the sys.table_exists() procedure to check to see if the table now exists:

CALL sys.table_exists('test', 't1', @table_type); 
SELECT @table_type;

Result:

+-------------+
| @table_type |
+-------------+
| BASE TABLE  |
+-------------+

Here, test is the name of the database, and t1 is the name of the table that I’m checking the existence of.

In this case, the result is BASE TABLE which means that the table does exist.

This is just one of many ways to check whether a table exists in MySQL.

Try to Create the Table Again

Now if we attempt to create that table again:

CREATE TABLE IF NOT EXISTS t1 (
  c1 INT,
  c2 VARCHAR(10)
);

We don’t get an error:

Query OK, 0 rows affected, 1 warning (0.00 sec)

But we do get a warning.

So let’s check the warning:

SHOW WARNINGS;

Result:

+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Note  | 1050 | Table 't1' already exists |
+-------+------+---------------------------+

As expected, it tells us that the table already exists.

Without the IF NOT EXISTS Clause

Here’s what happens when we don’t use the IF NOT EXISTS clause when trying to create a table that already exists:

CREATE TABLE t1 (
  c1 INT,
  c2 VARCHAR(10)
);

This time we get an error:

ERROR 1050 (42S01): Table 't1' already exists

It’s important to note that the IF NOT EXISTS clause does not check the table structure/definition. It simply checks that there’s no existing table with the same name that we’re trying to give to the table that we’re creating.

In other words, just because a table of that name already exists, it doesn’t mean it has the correct definition.