MySQL CREATE DATABASE: IF NOT EXISTS, utf8mb4 and First User

The MySQL create database command is one line: CREATE DATABASE harbor_bakery;. Add IF NOT EXISTS so a rerun doesn’t fail, and name the character set so the database doesn’t depend on whatever the server default happens to be.

What surprised me: CREATE DATABASE Harbor_Bakery failed with ERROR 1007 on my Windows server, though I’d only created the lowercase name.

Test setup: MySQL 8.4.9 Community on Windows 11 (port 3307), mysql client, server collation utf8mb4_0900_ai_ci, lower_case_table_names = 1.

Here’s what we’re building: a New England bakery chain’s database, one table and an app login.

Before You Start: CREATE Privilege and the Sample Table

You need a running server (here’s how to install MySQL on Windows) and an account with the CREATE privilege; root has it. The database gets one table:

Column	Type	Notes
store_id	INT AUTO_INCREMENT	Primary key
city	VARCHAR(20)	Portland, Burlington, Salem
state	CHAR(2)	ME, VT, MA

Step 1: Create the Database with CREATE DATABASE

Run this as root. CREATE SCHEMA is a synonym here, unlike in SQL Server:

CREATE DATABASE harbor_bakery
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

SHOW DATABASES LIKE 'harbor%';

Result:

+--------------------+
| Database (harbor%) |
+--------------------+
| harbor_bakery      |
+--------------------+

utf8mb4_0900_ai_ci is the 8.4 default anyway; spelling it out keeps the script predictable on other servers.

Checkpoint: SHOW DATABASES lists harbor_bakery.

Step 2: Make the Script Rerunnable with IF NOT EXISTS

I reran the script with --force so the client kept going after each error:

CREATE DATABASE harbor_bakery;
CREATE DATABASE Harbor_Bakery;
CREATE DATABASE harbor-bakery-archive;

CREATE DATABASE IF NOT EXISTS harbor_bakery;
SHOW WARNINGS;

What MySQL said:

ERROR 1007 (HY000) at line 1: Can't create database 'harbor_bakery'; database exists
ERROR 1007 (HY000) at line 2: Can't create database 'harbor_bakery'; database exists
ERROR 1064 (42000) at line 3: 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 '-bakery-archive' at line 1
+-------+------+--------------------------------------------------------+
| Level | Code | Message                                                |
+-------+------+--------------------------------------------------------+
| Note  | 1007 | Can't create database 'harbor_bakery'; database exists |
+-------+------+--------------------------------------------------------+

What this tells me: with lower_case_table_names = 1 (the Windows default), Harbor_Bakery is folded to harbor_bakery. On Linux the default is 0, so it would have created a second database. A hyphen needs backticks: `harbor-bakery-archive`.

Checkpoint: the IF NOT EXISTS line returns a Note 1007, not an error.

Step 3: Select It with USE and Create the First Table

USE selects the new database for the session and SELECT DATABASE() confirms it:

USE harbor_bakery;
SELECT DATABASE() AS current_db;

CREATE TABLE stores (
  store_id INT AUTO_INCREMENT PRIMARY KEY,
  city VARCHAR(20) NOT NULL,
  state CHAR(2) NOT NULL
);
INSERT INTO stores (city, state)
VALUES ('Portland', 'ME'), ('Burlington', 'VT'), ('Salem', 'MA');
SELECT * FROM stores;

Result:

+---------------+
| current_db    |
+---------------+
| harbor_bakery |
+---------------+
+----------+------------+-------+
| store_id | city       | state |
+----------+------------+-------+
|        1 | Portland   | ME    |
|        2 | Burlington | VT    |
|        3 | Salem      | MA    |
+----------+------------+-------+

Column choices are covered in creating a table in MySQL.

Checkpoint: current_db is harbor_bakery and three stores come back.

Step 4: Create a User and Grant Access to the Database

Give the app its own login, scoped to harbor_bakery.*:

CREATE USER 'bakery_app'@'localhost' IDENTIFIED BY 'Bake!Pass2026';
GRANT SELECT, INSERT, UPDATE, DELETE
  ON harbor_bakery.* TO 'bakery_app'@'localhost';

SHOW GRANTS FOR 'bakery_app'@'localhost';

SHOW GRANTS:

+---------------------------------------------------------------------------------------+
| Grants for bakery_app@localhost                                                       |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `bakery_app`@`localhost`                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `harbor_bakery`.* TO `bakery_app`@`localhost` |
+---------------------------------------------------------------------------------------+

USAGE means “can connect”. Hosts and revoking are in how to create a new user in MySQL.

Test the New Database

SHOW CREATE DATABASE prints the exact definition; \G keeps the long line readable:

SELECT VERSION() AS version;
SHOW CREATE DATABASE harbor_bakery\G
SHOW TABLES FROM harbor_bakery;

Result:

+---------+
| version |
+---------+
| 8.4.9   |
+---------+
*************************** 1. row ***************************
       Database: harbor_bakery
Create Database: CREATE DATABASE `harbor_bakery` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
+-------------------------+
| Tables_in_harbor_bakery |
+-------------------------+
| stores                  |
+-------------------------+

The /*!40100 ... */ parts are version comments. The PostgreSQL equivalent check is \l in psql.

How Do You Create a Database from the Command Line with mysqladmin?

mysqladmin create does it from Command Prompt or a shell script:

@echo off
mysqladmin -u root -P 3307 create harbor_bakery_reports
mysql -u root -P 3307 -e "SHOW CREATE DATABASE harbor_bakery_reports\G"

Output:

*************************** 1. row ***************************
       Database: harbor_bakery_reports
Create Database: CREATE DATABASE `harbor_bakery_reports` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

There’s no character set option; it took the server default.

Change or Remove a Database: ALTER DATABASE and DROP DATABASE IF EXISTS

I created an archive database as latin1 on purpose, then fixed it:

CREATE DATABASE `harbor-bakery-archive` CHARACTER SET latin1;
ALTER DATABASE `harbor-bakery-archive`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SELECT schema_name, default_collation_name
FROM information_schema.SCHEMATA WHERE schema_name LIKE 'harbor%';

DROP DATABASE IF EXISTS `harbor-bakery-archive`;
DROP DATABASE IF EXISTS `harbor-bakery-archive`;
SHOW COUNT(*) WARNINGS;

Archive database after ALTER:

+-----------------------+------------------------+
| SCHEMA_NAME           | DEFAULT_COLLATION_NAME |
+-----------------------+------------------------+
| harbor-bakery-archive | utf8mb4_0900_ai_ci     |
| harbor_bakery         | utf8mb4_0900_ai_ci     |
+-----------------------+------------------------+
+-------------------------+
| @@session.warning_count |
+-------------------------+
|                       0 |
+-------------------------+

Note: ALTER DATABASE changes the default for new tables only (text data types pick it up). The second DROP DATABASE IF EXISTS left 0 warnings on 8.4.9; DROP TABLE IF EXISTS gives a Note 1051.

Creating a Schema in MySQL Workbench

Workbench isn’t installed here, so no screenshot. In its Navigator, right-click the Schemas tab, choose Create Schema, name it and apply.

My Takeaways on Creating a MySQL Database

1. Write CREATE DATABASE IF NOT EXISTS with an explicit CHARACTER SET and COLLATE in every script.
2. Stick to lowercase names with underscores.
3. Give each app its own user with grants on db.*, not root.

Results would differ on Linux, where database names are case-sensitive. MariaDB’s CREATE DATABASE uses the same syntax.

Where to Take It Next with Your MySQL Database

Next step: fill the database with more tables and queries in the MySQL tutorial.

• Picking an engine: MariaDB vs MySQL vs PostgreSQL
• MariaDB vs MySQL
• Querying several tables: MySQL joins
• Moving engines: MySQL to PostgreSQL migration

Frequently Asked Questions