Create Database

Creating a MySQL Database in PHP with PDO

PHP setup scripts and installers can create a new MySQL database programmatically by connecting to the MySQL server without specifying a dbname in the DSN, then executing a CREATE DATABASE SQL statement. Proper character sets and identifier validation keep setup scripts safe and idempotent.

Connecting Without dbname and Setting utf8mb4

Because the target database does not exist yet, omit dbname= from your initial PDO DSN string. Specify CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci so every table created inside the database inherits full Unicode support.

flowchart TD
    A["Connect PDO without dbname"] --> B["Validate Database Name Allowlist"]
    B --> C["Run CREATE DATABASE IF NOT EXISTS"]
    C --> D["Open App Connection with dbname=app_demo"]

<?php
declare(strict_types=1);

function ensureDatabaseExists(string $host, string $user, string $pass, string $dbName): void
{
    // Prepared parameters cannot bind database identifiers, so validate strictly
    if (preg_match('/^[a-zA-Z0-9_]{1,64}$/', $dbName) !== 1) {
        throw new InvalidArgumentException('Invalid database name.');
    }

    $pdo = new PDO("mysql:host={$host};charset=utf8mb4", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);

    $sql = "CREATE DATABASE IF NOT EXISTS `{$dbName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
    $pdo->exec($sql);
}

Why Prepared Statements Cannot Bind Database Names

SQL CREATE DATABASE ? is invalid syntax in MySQL because prepared parameters only work for values inside WHERE, INSERT, or UPDATE clauses—not table or database names. Whenever a setup script accepts a dynamic database name, validate it with a strict regex (/^[a-zA-Z0-9_]{1,64}$/) and wrap it in backticks.

Common Mistakes to Avoid

• Passing dbname= before the database exists: Connecting with dbname=shop_demo before creating shop_demo throws SQLSTATE[HY000] [1049] Unknown database.
• Running CREATE DATABASE without IF NOT EXISTS: Re-running your setup script will crash with a "database exists" error unless you include IF NOT EXISTS.
• Granting CREATE DATABASE permissions to web app users in production: Your runtime web app user should only have SELECT, INSERT, UPDATE, and DELETE rights on its own database—keep schema creation in migration tools or admin setup scripts.

Frequently Asked Questions

What happens if exec() returns 0 on CREATE DATABASE?

PDO::exec() returns the number of affected rows (1 or 0 for DDL statements), not a failure boolean. With PDO::ERRMODE_EXCEPTION, any failure throws a PDOException.

Should a web request create databases on the fly?

Usually no. Create databases once during provisioning or via versioned migration tools rather than inside user-facing HTTP endpoints.

Why use utf8mb4_unicode_ci instead of latin1?

utf8mb4_unicode_ci supports every Unicode character—including accents, Bengali, Arabic, and emojis—and sorts case-insensitively across languages.