In MariaDB, 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 before creating it.

The table will only be created if there isn’t already one with the same name.

Example

Here’s an example to demonstrate:

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

Here, 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 query the information_schema.tables table to check to see if the table now exists:

SELECT EXISTS (
    SELECT 
        TABLE_NAME
    FROM 
    information_schema.tables 
    WHERE 
    TABLE_SCHEMA LIKE 'zap' AND 
        TABLE_TYPE LIKE 'BASE TABLE' AND
        TABLE_NAME = 't1'
    );

Result:

1

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

The 1 means that the table exists. If it didn’t exist, we’d get 0.

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.002 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

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.