MySQL Create DB

Creating a Database

In simple terms: A MySQL server can hold many databases — one for your shop, one for your blog, one for testing. Before creating tables, you need a database to put them in. Creating it is a one-time setup step, usually done with a single SQL statement.

Technically: CREATE DATABASE creates a new, empty database (in MySQL, the terms "database" and "schema" are synonyms). Its character set and collation become the defaults for all tables created inside it. From PHP, the statement is sent with mysqli::query(), which returns true on success for statements that do not produce result rows.

The SQL Statement

CREATE DATABASE IF NOT EXISTS gocourse_shop
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

Collations in Practice

A collation decides whether 'Chennai' = 'chennai' is true and how names are sorted:

With a case-insensitive collation, searching for asha@example.com finds Asha@Example.com, and a UNIQUE index treats them as duplicates — usually what you want for emails and usernames.

Creating a Database From PHP

Creating databases requires the CREATE privilege, which the normal application user should not have (see MySQL Database). A setup script therefore connects with an administrative account, typically from the command line during installation or deployment:

<?php
// bin/create-database.php — run with: php bin/create-database.php
$name = 'gocourse_shop';

if (!preg_match('/^[a-zA-Z0-9_]{1,64}$/', $name)) {
    exit("Invalid database name.\n");
}

try {
    $admin = new mysqli(
        getenv('DB_HOST') ?: '127.0.0.1',
        getenv('DB_ADMIN_USER') ?: 'root',
        getenv('DB_ADMIN_PASSWORD') ?: '',
    );
    $admin->set_charset('utf8mb4');

    $admin->query("CREATE DATABASE IF NOT EXISTS `$name` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci");

    $status = $admin->warning_count === 0 ? 'created' : 'already existed';
    echo "Database $name is ready ($status).\n";
} catch (mysqli_sql_exception $e) {
    fwrite(STDERR, "Could not create the database: " . $e->getMessage() . "\n");
    exit(1);
}

Output (first run)

Database gocourse_shop is ready (created).

Output (second run)

Database gocourse_shop is ready (already existed).

How It Works

1. The connection is opened without a database name, because the database does not exist yet.
2. query() sends the SQL; on failure, it throws a mysqli_sql_exception (PHP 8.1+ default).
3. With IF NOT EXISTS, an existing database does not cause an error; MySQL issues a warning instead (code 1007, "database exists"), which warning_count reveals. This makes the script safe to run repeatedly — it is idempotent.
4. Errors go to STDERR, and exit(1) signals failure to deployment tools and shell scripts.

Why the Name Is Validated

Database, table, and column names (identifiers) cannot be passed as prepared-statement parameters — placeholders only work for values. If a name ever comes from configuration or input, validate it against a strict pattern and wrap it in backticks, as shown. MySQL identifiers are limited to 64 characters.

Checking and Selecting Databases

<?php
$admin = new mysqli('127.0.0.1', getenv('DB_ADMIN_USER') ?: 'root', getenv('DB_ADMIN_PASSWORD') ?: '');

$result = $admin->query("SHOW DATABASES LIKE 'gocourse%'");

while ($row = $result->fetch_row()) {
    echo $row[0] . "\n";
}

$admin->select_db('gocourse_shop');
echo "Current database: " . $admin->query('SELECT DATABASE()')->fetch_row()[0] . "\n";

Output (example)

gocourse_shop
gocourse_shop_test
Current database: gocourse_shop

• SHOW DATABASES LIKE '...' lists databases matching a pattern (only those the user may access).
• select_db() switches the default database for subsequent queries, equivalent to the SQL statement USE gocourse_shop.

A separate database for automated tests (gocourse_shop_test) is a common practice, so tests never touch development or production data.

Deleting a Database

DROP DATABASE IF EXISTS gocourse_shop_test;

Warning: DROP DATABASE permanently deletes the database and all its tables and data. There is no undo. Never let a web application user hold the DROP privilege, and double-check the database name in scripts — especially in production. Take a backup (for example, with mysqldump) before any destructive operation.

Where Databases Are Usually Created

Tables inside the database are then created and changed through migrations — versioned scripts kept in the project (see MySQL Create Table).

Common Mistakes

Using the Application User to Create Databases

Effect: The application account needs dangerous privileges.

Fix: Use a separate administrative account for setup.

Forgetting the Character Set

Effect: An older server default (such as latin1) can break non-English text.

Fix: Always specify CHARACTER SET utf8mb4.

Inserting Names From Input Into SQL

Effect: SQL injection through identifiers.

Fix: Validate names strictly and quote them with backticks.

Running Setup Scripts Without IF NOT EXISTS

Effect: The second run fails with "database exists".

Fix: Make setup scripts idempotent.

Best Practices

• Create databases with utf8mb4 and an explicit collation.
• Perform database creation with an administrative account, not the application user.
• Make setup scripts idempotent with IF NOT EXISTS.
• Validate and backtick-quote identifiers that come from configuration.
• Keep separate databases for development, testing, and production.

Official reference: https\://www.php.net/manual/en/mysqli.query.php