MySQL - Create Database

MySQL - Creating a Database

This tutorial guides you through creating a new database in MySQL. We'll cover the `CREATE DATABASE` statement, essential best practices like specifying character sets, using the `IF NOT EXISTS` clause to prevent errors, and how to create databases from both the command line and client applications.

Before you can store and manage data in MySQL, you need a database to act as a container for your tables, views, and other objects. Creating a database is typically the first step in setting up a new project or application.

Note: You need appropriate administrative privileges (like the CREATE privilege) to create a database. If you are using a managed database service, you might create databases through a web console instead of SQL.

The CREATE DATABASE Statement

The CREATE DATABASE statement is a DDL (Data Definition Language) command used to create a new, empty database within the MySQL server.

Basic Syntax

CREATE DATABASE database_name;

While this works, it relies on server defaults for character set and collation, which may not be ideal. A much better practice is to be explicit.

Best Practice: Specifying Character Set and Collation

To ensure your database can correctly store and sort a wide range of characters (including international text and emojis), it’s highly recommended to use the utf8mb4 character set and a suitable collation like utf8mb4_unicode_ci.

CREATE DATABASE my_new_app
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

This command creates a database named my_new_app configured for modern, multilingual applications.

Verification

After creating the database, you can verify its existence by listing all databases on the server.

SHOW DATABASES;

Database
information_schema
my_new_app
mysql
performance_schema
sys

The default databases (information_schema, mysql, performance_schema, sys) store metadata, user information, and performance metrics for the MySQL server itself.

Preventing Errors with IF NOT EXISTS

If you try to create a database with a name that already exists, MySQL will return an error.

CREATE DATABASE my_new_app;
-- ERROR 1007 (HY000): Can't create database 'my_new_app'; database exists

To avoid this, especially in automated scripts, use the IF NOT EXISTS clause. This tells MySQL to create the database only if it’s missing, otherwise, it does nothing and issues a warning instead of an error.

CREATE DATABASE IF NOT EXISTS my_new_app;

Creating a Database with mysqladmin

For system administrators, the mysqladmin command-line utility provides a quick way to create a database from the shell without logging into the MySQL client.

# The -p flag will prompt you for the password
mysqladmin -u root -p create my_cli_app

Enter password: ********

This is useful for scripting and server management tasks.

Creating a Database Using a Client Program

Application setup scripts often need to create a database programmatically. Here’s how you can do it safely in different languages. Note that database names cannot be parameterized, so you don’t use prepared statements here, but robust error handling is still vital.

Node.js (with mysql2/promise)

require('dotenv').config();
const mysql = require('mysql2/promise');

async function createDatabase() {
  // Connect without a specific database first
  let connection;
  try {
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'password'
    });

    // Use backticks for the database name to prevent SQL injection if the name is dynamic, though it's better to use a fixed name.
    await connection.query(`CREATE DATABASE IF NOT EXISTS \`my_node_db\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    console.log('Database my_node_db created or already exists.');

  } catch (error) {
    console.error('Failed to create database:', error.message);
  } finally {
    if (connection) await connection.end();
  }
}

createDatabase();

Python (with mysql-connector-python)

import mysql.connector
import os

connection = None
try:
    # Connect to the server, not a specific database
    connection = mysql.connector.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        user=os.getenv('DB_USER', 'root'),
        password=os.getenv('DB_PASSWORD', 'password')
    )
    cursor = connection.cursor()

    db_name = "my_python_db"
    # It's safest to validate the db_name against a whitelist if it's from user input.
    cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    print(f"Database '{db_name}' created or already exists.")

except mysql.connector.Error as err:
    print(f"Database error: {err}")
finally:
    if connection and connection.is_connected():
        cursor.close()
        connection.close()

PHP (with PDO)

<?php
$host = $_ENV['DB_HOST'] ?? 'localhost';
$user = $_ENV['DB_USER'] ?? 'root';
$pass = $_ENV['DB_PASSWORD'] ?? 'password';
$charset = 'utf8mb4';
$db_name = "my_php_db";

// Connection string without a dbname
$dsn = "mysql:host=$host;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    // Using backticks is important for database names
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `{$db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;");

    echo "Database '{$db_name}' created or already exists.\n";

} catch (\PDOException $e) {
    error_log($e->getMessage());
    throw new \RuntimeException("Could not connect to or create the database.");
}
?>