MySQL Drop Database

Key Concepts

DROP DATABASE testdb;

DROP DATABASE IF EXISTS testdb;

-- Everything inside is deleted
DROP DATABASE shop;

-- No "Are you sure?" prompt
DROP DATABASE olddb;

🔹 Basic DROP DATABASE Syntax

The DROP DATABASE statement removes a database completely from the MySQL server. Once executed, all tables, views, stored procedures, and data within the database are permanently deleted and cannot be recovered without a backup.

-- Basic syntax
DROP DATABASE database_name;

-- Example: Delete a test database
DROP DATABASE test_db;

-- Example: Delete an old project database
DROP DATABASE old_project;

🔹 DROP DATABASE IF EXISTS

Using IF EXISTS prevents errors when attempting to drop a database that doesn't exist. This is particularly useful in automated scripts, migrations, or cleanup operations where you want the script to continue running even if the database is already gone.

-- Safe database deletion
DROP DATABASE IF EXISTS temp_db;

-- This won't cause an error even if database doesn't exist
DROP DATABASE IF EXISTS non_existent_db;

-- Example in a cleanup script
DROP DATABASE IF EXISTS old_backup;
DROP DATABASE IF EXISTS staging_test;

🔹 Checking Before Dropping

Before dropping a database, it's good practice to verify which databases exist and check if the target database is currently in use. This helps prevent accidental deletion of important data and ensures you're dropping the correct database.

-- Step 1: List all databases
SHOW DATABASES;

-- Step 2: Check current database
SELECT DATABASE();

-- Step 3: View database size before dropping
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'mydb'
GROUP BY table_schema;

🔹 Alternative: DROP SCHEMA

DROP SCHEMA is a synonym for DROP DATABASE in MySQL. Both commands perform exactly the same operation and can be used interchangeably. Some developers prefer DROP SCHEMA for consistency with SQL standards used in other database systems.

-- DROP SCHEMA works the same as DROP DATABASE
DROP SCHEMA test_schema;

-- With IF EXISTS clause
DROP SCHEMA IF EXISTS old_schema;

-- Both commands are identical
DROP DATABASE mydb;
DROP SCHEMA mydb;  -- Same result

🔹 Complete Example with Safety Checks

This example demonstrates a safe approach to dropping a database with proper verification steps. Always backup important data before dropping databases, and verify you're not currently using the database you intend to delete to avoid connection errors.

-- Step 1: Show all databases
SHOW DATABASES;

-- Step 2: Make sure you're not using the database
SELECT DATABASE();

-- Step 3: Switch to a different database if needed
USE mysql;

-- Step 4: Safely drop the database
DROP DATABASE IF EXISTS old_project;

-- Step 5: Verify deletion
SHOW DATABASES LIKE 'old_project';