mariadb-corporation CREATE DATABASE in MariaDB
Last updated: 2026-06-24
This skill covers the delta between standard SQL and MariaDB for creating and managing databases (schemas). It assumes the agent knows the basic CREATE DATABASE db; form.
Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.
What LLMs Often Miss
| If the agent writes / assumes… | …prefer the MariaDB form |
|---|---|
DROP DATABASE x; CREATE DATABASE x … to reset a database |
CREATE OR REPLACE DATABASE x … does exactly that in one statement |
Treats CREATE OR REPLACE DATABASE as a safe "create if missing" |
It drops the existing database first — every table in it is gone, irreversibly. Use CREATE DATABASE IF NOT EXISTS x when you only want create-if-absent. OR REPLACE and IF NOT EXISTS express opposite intents — never both |
Thinks CREATE SCHEMA differs from CREATE DATABASE |
They are exact synonyms (same for ALTER/DROP, and SCHEMAS/DATABASES) |
Reaches for a RENAME DATABASE / RENAME SCHEMA statement |
There is none (it was removed as unsafe). Rename per-table with RENAME TABLE old_db.t TO new_db.t, or dump and reload |
CHARACTER SET utf8 (the 3-byte legacy alias) |
Use utf8mb4 for full Unicode. On fresh 11.6+ installs utf8mb4 is already the default charset (was latin1), with default collation uca1400_ai_ci (since 11.5/11.6) — see mariadb-features |
Expects ALTER DATABASE to rename or relocate a database |
It can only change CHARACTER SET, COLLATE, or COMMENT (or run UPGRADE DATA DIRECTORY NAME for a legacy directory). No rename, no move |
| Assumes changing a database's charset re-encodes existing data or updates existing routines | It changes only the database default for newly created objects. Existing tables keep their charset; existing stored routines must be dropped and recreated to pick up the new default |
Wraps CREATE/DROP DATABASE in a transaction to roll back |
DDL does an implicit commit — it can't be rolled back. (DROP DATABASE is crash-safe per-table, but that's not the same as transactional) |
DROP DATABASE x without realizing the blast radius |
It drops the database and all its tables. Privileges granted on the database are not automatically removed |
Bare CREATE DATABASE x in a script that may re-run |
Errors (1007 … database exists) the second time. Use IF NOT EXISTS to downgrade to a note |
Syntax
CREATE [OR REPLACE] {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[ [DEFAULT] CHARACTER SET [=] charset
| [DEFAULT] COLLATE [=] collation
| COMMENT [=] 'string' ] …;
CREATE DATABASE app
CHARACTER SET = 'utf8mb4'
COLLATE = 'uca1400_ai_ci'
COMMENT = 'application schema';
CREATE OR REPLACE DATABASEreports 2 rows affected (the drop, then the create).COMMENTis stored inINFORMATION_SCHEMA.SCHEMATA; max 1024 bytes.
ALTER DATABASE
ALTER {DATABASE | SCHEMA} [db_name]
{CHARACTER SET = charset | COLLATE = collation | COMMENT = 'string'};
ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME;
db_name may be omitted to target the current database. UPGRADE DATA DIRECTORY NAME re-encodes a legacy #mysql50#-prefixed directory name and is normally driven by mariadb-upgrade, not hand-written.
DROP DATABASE
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
Without IF EXISTS, a missing database errors (1008); with it, a note. Drops every table in the database — there is no RESTRICT/CASCADE and no confirmation.
See Also
mariadb-features(topical) — thelatin1→utf8mb4default-charset change (since 11.6) and theuca1400_ai_cidefault-collation change (since 11.5); the canonical home for that storymariadb-create-table— the table-level analogs ofCREATE OR REPLACEandIF NOT EXISTS, theutf8/utf8mb4trap, and per-object charset/collation- Canonical references on
mariadb.com/docs(consult only for edge cases not covered here):- https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-database
- https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-database
- https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-database
- https://mariadb.com/docs/server/reference/sql-statements/data-definition/renaming-databases
This page is: Copyright © 2026 MariaDB. All rights reserved.