DROP DATABASE

DROP DATABASE deletes a database and every table, row, view and stored program inside it. There is no recycle bin and no confirmation.

1
2
3
4
5

CREATE DATABASE old_project;
CREATE TABLE old_project.users (id INT);
SHOW DATABASES LIKE 'old_project';
DROP DATABASE old_project;
SHOW DATABASES LIKE 'old_project';

1
2
3
4
5
6
7
8

+------------------------+
| Database (old_project) |
+------------------------+
| old_project            |
+------------------------+
1 row in set

Empty set

The first SHOW lists old_project. After the drop, it returns nothing.

IF EXISTS

Dropping something that is not there is an error, unless you add IF EXISTS:

1

DROP DATABASE missing_db;

1

ERROR 1008 (HY000): Can't drop database 'missing_db'; database doesn't exist

1
2

DROP DATABASE IF EXISTS missing_db;
SHOW WARNINGS;

1

Empty set

IF EXISTS makes scripts that reset a test environment safe to repeat.

What is deleted

• Every table and its data
• Views, stored procedures, functions, triggers and events in that database
• The folder on disk

What is not deleted: user accounts, and privileges are not removed automatically (grants that name the database stay until you revoke them).

It cannot be rolled back

DROP DATABASE is DDL, and DDL commits at once (DDL, DML, DQL, DCL and TCL). The only way back is a backup (Backup and Restore).

A safety checklist

1. Check the name. SELECT DATABASE();
2. Look at what is inside. SHOW TABLES FROM name;
3. Take a backup. Even if you “know” you do not need it.
4. Use accounts with least privilege. Your web application’s database user must not have DROP rights.
5. Do not run it in production by hand without a second person checking the name.

1
2
3
4

CREATE DATABASE temp_check;
CREATE TABLE temp_check.t (id INT);
SELECT table_name FROM information_schema.tables WHERE table_schema = 'temp_check';
DROP DATABASE IF EXISTS temp_check;

1
2
3
4
5
6

+------------+
| TABLE_NAME |
+------------+
| t          |
+------------+
1 row in set