MySQL Create Database

Last updated: October 19, 2026Oct 19, 2026

A database in MySQL is a named container for tables, views and stored programs. Before you can create tables you need a database to put them in. The CREATE DATABASE statement creates one.

1. What CREATE DATABASE Does

• It registers a new database name in MySQL's data dictionary.
• It creates a directory with the same name inside the server's data folder.
• The new database is empty; you add tables with CREATE TABLE.
• In MySQL, CREATE SCHEMA is an exact synonym of CREATE DATABASE.

2. Syntax

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name
  [ [DEFAULT] CHARACTER SET charset_name ]
  [ [DEFAULT] COLLATE collation_name ]
  [ [DEFAULT] ENCRYPTION = {'Y' | 'N'} ];

3. Creating a Simple Database

CREATE DATABASE college;

Query OK, 1 row affected

"1 row affected" is simply how MySQL reports a successful CREATE DATABASE. Check that it exists:

SHOW DATABASES LIKE 'college';

Result: 1 row

4. Avoiding Errors with IF NOT EXISTS

Creating a database that already exists is an error:

CREATE DATABASE college;

ERROR 1007 (HY000): Can't create database 'college'; database exists

Add IF NOT EXISTS so scripts can be run many times safely. MySQL just records a warning:

CREATE DATABASE IF NOT EXISTS college;
SHOW WARNINGS;

Query OK, 1 row affected, 1 warning

Result: 1 row

5. Character Set and Collation

The character set decides which characters can be stored; the collation decides how they are compared and sorted. MySQL 8+ defaults to utf8mb4, which supports every language and emoji. You can set them explicitly:

CREATE DATABASE library_app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name IN ('college', 'library_app');

Result: 2 rows

The collation really changes results. Compare a case-insensitive and a case-sensitive collation:

SELECT 'Ravi' = 'RAVI' COLLATE utf8mb4_0900_ai_ci AS ai_ci,
       'Ravi' = 'RAVI' COLLATE utf8mb4_0900_as_cs AS as_cs;

Result: 1 row

6. Seeing How a Database Was Created

SHOW CREATE DATABASE library_app;

Result: 1 row

The /*!40100 ... */ parts are version comments: older MySQL versions skip them, newer ones run them.

7. Database Naming Rules

• Up to 64 characters.
• Letters, digits, _ and $ can be used without quoting. Other characters (spaces, hyphens) need back ticks: `my-app`.
• Names can't end with a space and can't contain /, \ or ..
• On Linux, database names are case-sensitive by default (they are directories); on Windows and macOS they aren't. Use lowercase to stay portable.
• Avoid reserved words such as order, select or (since MySQL 9) library.

CREATE DATABASE my-shop;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-shop' at line 1

CREATE DATABASE `my-shop`;   -- works, but you must quote it every time
CREATE DATABASE my_shop;     -- better

Query OK, 1 row affected
Query OK, 1 row affected

8. Using the New Database

After creating a database, select it with USE and create tables in it:

USE college;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(40));
INSERT INTO departments VALUES (10, 'Computer Science'), (20, 'Mechanical');
SELECT DATABASE() AS current_db, COUNT(*) AS departments FROM departments;

Result: 1 row

See MySQL Select Database for more about USE.

9. Who Can Create Databases?

You need the CREATE privilege. An administrator can let a user create only databases whose names start with a prefix, using a wildcard:

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'Dev#Pass2026';
GRANT ALL PRIVILEGES ON `dev1\_%`.* TO 'dev1'@'localhost';
SHOW GRANTS FOR 'dev1'@'localhost';

Result: 2 rows

Now dev1 may create and manage dev1_test or dev1_blog, but nothing else.

10. Creating a Database from Java

With JDBC, connect without choosing a database, then run the statement:

import java.sql.*;

public class CreateDatabaseDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/";          // no database in the URL
        try (Connection con = DriverManager.getConnection(url, "root", "your-password");
             Statement st = con.createStatement()) {
            st.executeUpdate("CREATE DATABASE IF NOT EXISTS inventory CHARACTER SET utf8mb4");
            System.out.println("Database ready");
        }
    }
}

11. Common Mistakes

• Forgetting USE and then getting ERROR 1046 (3D000): No database selected.
• Using hyphens or reserved words in names without back ticks.
• Relying on case: College and college are different databases on Linux.
• Creating the database with an old character set (latin1, utf8mb3) and later failing to store emoji or other scripts.

12. Practice Exercises

CREATE DATABASE IF NOT EXISTS hospital
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SHOW CREATE DATABASE hospital;

SHOW DATABASES LIKE '%shop%';

13. Summary

• CREATE DATABASE name; (or CREATE SCHEMA) creates an empty database.
• Use IF NOT EXISTS to make scripts safe to re-run.
• Set CHARACTER SET utf8mb4 and a suitable COLLATE if you need something other than the default.
• Follow the naming rules: ≤ 64 characters, lowercase, letters/digits/underscores.
• Check with SHOW DATABASES or SHOW CREATE DATABASE, then USE it.