MySQL DROP DATABASE Statement

admin

7 years ago

Overview

The DROP DATABASE statement deletes the entire database. A MySQL database is represented by a directory under the data directory. Before dropping the database, MySQL removes the database objects that it contains, such as database tables, views, stored procedures, triggers, etc. When we no longer need a database, we can remove it with the DROP DATABASE command.

DROP Database Syntax

The general syntax of the statement is as follows:

mysql> DROP DATABASE [IF EXISTS] database_name ;

The user must have DROP access administrative privileges to execute this statement. A successful DROP DATABASE command returns a row count that indicates the number of tables dropped. We can check the current databases list to make sure that the database was deleted. Use the SHOW DATABASES statement to list the current databases on the MySQL database server.

Example

For example, to delete a database named testdb and all the tables in that database, we can execute the following command:

mysql> DROP DATABASE testdb;

Note that privileges granted on the database need to be dropped explicitly.

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| testdb |
| world |
+--------------------+
7 rows in set (0.01 sec)

mysql> DROP DATABASE IF EXISTS testdb;
Query OK, 0 rows affected (0.08 sec)

Common Error

Trying to delete a database that doesn’t exist.

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

To display the errors, we can use the following command:

mysql> SHOW ERRORS;

We will get an error if the database does not exist. We can include the IF EXISTS clause to cause a warning instead of error. Any warning generated can be displayed with the SHOW WARNINGS command.

mysql> SHOW WARNINGS;

The server deletes only files and directories that it can identify as having been created by itself (for example, .ibd, .myi, .myd, .sdi files, etc or RAID directories.

It does not delete other files and directories. If the user has put non-mysql files in the database data directory, those files will not be deleted by the DROP DATABASE statement. This results in failure to remove the database directory, and DROP DATABASE fails. In that case, the database will continue to be listed by SHOW DATABASES. To correct this problem, you can manually remove the database directory and any files within it.

CAUTION

It’s recommended to backup the database before executing the statement. This command is destructive
in nature and we need to pay caution when executing this statement. DROP DATABASE is dangerous statement. There is no statement to undo the changes of the DROP DATABASE command. If the database was dropped by mistake, the only option is to recover the database and its objects is from the database backup.

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/