MySQL And MariaDB For Application Projects

MySQL and MariaDB are common production databases for PHP applications. Many Laravel, Symfony, WordPress, Magento, Craft CMS, and custom PHP projects use one of them to store application data.

For junior PHP work, the important skill is not database administration. It is knowing the defaults that affect application correctness: connection settings, character sets, storage engines, SQL modes, indexes, migrations, backups, and safe credentials.

Connection Shape

PHP usually connects through PDO or a framework database layer. A MySQL DSN should include the host, database name, and utf8mb4 character set.

<?php

declare(strict_types=1);

function mysqlDsn(string $host, string $database): string
{
    return sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', $host, $database);
}

echo mysqlDsn('127.0.0.1', 'app') . PHP_EOL;

// Prints:
// mysql:host=127.0.0.1;dbname=app;charset=utf8mb4

utf8mb4 matters because MySQL's older utf8 character set does not support every Unicode character. Modern PHP apps should normally use utf8mb4.

PDO Options

A typical PDO setup enables exceptions and associative array fetches.

<?php

declare(strict_types=1);

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

print_r($options);

// Prints:
// [3] => 2
// [19] => 2
// [20] =>

The numeric keys are PDO constants. In real code, keep the constants rather than replacing them with numbers.

InnoDB And Foreign Keys

Most application tables should use InnoDB because it supports transactions, row-level locking, and foreign keys.

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    total_cents INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT orders_user_id_fk FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Foreign keys help the database protect relationships. Application validation is still needed, but the database should reject impossible relationships too.

Auto-Increment IDs

MySQL and MariaDB commonly use AUTO_INCREMENT integer primary keys.

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL
);

Use BIGINT for tables that may grow large. Be consistent across related tables: if users.id is BIGINT UNSIGNED, then orders.user_id should match.

SQL Modes And Strictness

MySQL can be configured to accept questionable data, such as truncating strings or converting invalid dates, depending on SQL mode. Production applications should prefer strict behaviour so bad data fails early.

From a PHP developer's point of view, this means:

• Validate inputs before writing.
• Do not rely on silent database conversion.
• Be aware that local, CI, and production database settings should match.
• Check errors instead of assuming writes succeeded.

Indexes

Indexes make common lookups fast, but they are not automatic for every useful query. Common examples include unique email addresses, foreign keys, status filters, and timestamp sorting.

CREATE INDEX orders_user_id_created_at_idx
ON orders (user_id, created_at);

Indexes also have a cost: they take space and slow down writes. Add indexes for real query patterns, not every column.

Credentials And Least Privilege

Application credentials should not be hard-coded. Put them in environment-specific configuration or a secrets manager.

A production application user should have only the permissions it needs. For example, the web app may need SELECT, INSERT, UPDATE, and DELETE, but not permission to create users or drop databases.

MySQL And MariaDB Differences

MySQL and MariaDB share history and many features, but they are not identical. Version differences affect JSON functions, indexes, generated columns, replication, optimiser behaviour, and operational tooling.

When a project says "MySQL-compatible", check the exact engine and version used in development, CI, and production.

What To Check

Before moving on, make sure you can:

• Build a MySQL PDO DSN with utf8mb4.
• Explain why exceptions and prepared statements matter.
• Recognise InnoDB, foreign keys, and auto-increment IDs.
• Explain why local and production SQL modes should match.
• Identify common places where indexes are needed.
• Keep database credentials out of source code.