If you drop databases, tables, and views without inserting any extra commands, error messages will likely pop up. This issue, fortunately, can be solved easily with MYSQL IF EXISTS. These guidelines will provide more examples and analyses.

Examples of MySQL IF EXISTS Usages

1. IF EXISTS Commands for Dropping Database

Syntax:

DROP DATABASE [IF EXISTS] name_of_database;

“Name_of_Database” helps specify database names that you want to delete, including contents like triggers, stored procedures, and tables.

When the system executes the “Drop Database” commands with no database called “Name_of_Database” available in the MySQL server, it will send back an error to announce no such databases are present during the command execution.

In such cases, if you have already used the clause “IF EXISTS” in your command, one notice will raise in place of the error, saying a database with specified names is unavailable, meaning the Drop Database execution will terminate by itself. The If Exists is adopted to prevent error issues when those databases do not exist.

First of all, you must open your MySQL connection via the command:

mysql -u root -p

By executing that command, you will receive this output:

^[[Aa@acme4sudo mysql -u root -p 
[sudo] password for a: 
Enter password: 
Welcome to the MySQL monitor. Commands end with; or \g. 
Your MySQL connection id is 2 
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu) 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Suppose you like to log in using root as your username; the system will ask for passwords (given that they have been set).

Next, it’s a must to execute the next command that lists all present databases in the MySQL server.

SHOW DATABASES;

That will send you this output:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------|

Now, we will try to erase the non-present database in the server and observe its output.

DROP DATABASE ittutoria;

The execution will send back this output:

mysql> DROP DATABASE ittutoria;
ERROR 1008 (HY000): Can’t drop database ‘ittutoria’; database doesn’t exist mysql > 

As you can see, the system sends back the error 1008, which claims that dropping the ‘Ittutoria’ database is not possible because there exist no such databases.

2. IF EXISTS Commands for Dropping Table

Syntax:

DROP TABLE [IF EXISTS] name_of_table;

When you execute the Drop Table with no table called “name_of_table” available in the MySQL database, the system will send an error claiming no tables like that are available during the command execution.

In such cases, if you have already used the clause “If Exists” within the command, a warning will pop up instead of errors, saying that table is unavailable, and Drop Table executions will terminate by themselves. The “If Exists” is adopted to prevent error issues whenever such tables do not exist.

Let’s try to drop an “itdemo” table that never exists in the “Ittutoria” database by using Drop commands:

DROP TABLE IF EXISTS itdemo;

The output will say that our query has successfully been executed without influencing any row. (though there is still a warning):

Output:

mysql> DROP TABLE IF EXISTS itdemo;
Query OK, 0 rows affected, 1 warning (0. 00 sec)
mysql>

3. IF EXISTS Commands for Dropping View

Syntax:

DROP VIEW [IF EXISTS] name_of_view;

Here, the “Name_of_view” specifies view names that you want to erase entirely (including entailed contents).

Similar to the previous two cases, if the Drop View commands are executed with no view called “name_of_view” available in the MySQL database, the system will return an error claiming no views like that have been available during the command execution.

Hence, it’s a must to add “IF EXISTS” to the command so that a warning will be returned instead of an error message.

DROP VIEW IF EXISTS viewTrial;

Output:

mysql> DROP VIEW IF EXISTS viewTrial;
Query OK, 0 rows affected, 1 warning (0. 00 sec)

Want to see what the warning is? Execute:

SHOW WARNINGS;

which gives you this output:

mysql> SHOW WARNINGS;
+----------+--------+----------------------------+
|  Level   | Code   | Message                    |
|  Note    | 1051   | Unknown table ‘Ittutoria. viewTrial |
1 row in set (0.00 sec)

Conclusion

This article has given clear examples of MYSQL IF EXISTS in many cases. For similar MYSQL issues (such as performing the “Inserted If Not Exists” command), feel free to browse our website.