A database is the container that holds your tables. You create one with CREATE DATABASE.
CREATE DATABASE shop;
SHOW DATABASES LIKE 'shop';+-----------------+
| Database (shop) |
+-----------------+
| shop |
+-----------------+
1 row in setSyntax
CREATE DATABASE [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name;In MySQL, CREATE SCHEMA is a synonym: a “schema” and a “database” are the same thing here (see Schema vs Database).
IF NOT EXISTS
Creating a database that already exists is an error:
CREATE DATABASE shop;ERROR 1007 (HY000): Can't create database 'shop'; database existsAdd IF NOT EXISTS to make a script safe to run twice. MySQL then gives a note instead of an error.
CREATE DATABASE IF NOT EXISTS shop;
SHOW WARNINGS;+-------+------+-----------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------+
| Note | 1007 | Can't create database 'shop'; database exists |
+-------+------+-----------------------------------------------+
1 row in setChoose the character set and collation
CREATE DATABASE blog
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
SHOW CREATE DATABASE blog;+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| blog | CREATE DATABASE `blog` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in setutf8mb4is the character set to use for new work. It stores every Unicode character, including emoji.utf8mb4_0900_ai_ciis the default collation in MySQL 8: accent-insensitive and case-insensitive.
If you leave them out, the database uses the server defaults, which are utf8mb4 and utf8mb4_0900_ai_ci in MySQL 8. Being explicit in your scripts makes them behave the same on every server, because hosting providers sometimes change the defaults. Character Sets and Collations explains the choices.
MariaDB
utf8mb4_0900_ai_ci. A script that names it will fail there. utf8mb4_unicode_ci or utf8mb4_general_ci work on both.What /!80016 DEFAULT ENCRYPTION='N' / means
In the output you saw a comment that starts with /*!80016. It is an executable comment: MySQL 8.0.16 and later run the part inside it (the DEFAULT ENCRYPTION option) and other servers ignore it.
Names and rules
- Use lowercase letters, digits and underscores:
shop,blog_2026. The name length limit is 64 characters. - The name becomes a folder on disk, so the operating system’s case rules apply (SQL Case Sensitivity).
- Avoid reserved words and spaces.
Permissions
You need the CREATE privilege. A normal application user usually should not have it. Create databases from a deployment or admin account.
CREATE DATABASE IF NOT EXISTS app_v1 CHARACTER SET utf8mb4;