MySQL/MariaDB-Integration

MySQL und sein Fork MariaDB sind die am weitesten verbreiteten relationalen Datenbanksysteme im PHP-Umfeld. In diesem Abschnitt werden wir auf die spezifischen Aspekte der Integration von MySQL/MariaDB in PHP-Anwendungen eingehen, die über die grundlegende PDO-Verwendung hinausgehen.

33.1 Unterschiede zwischen MySQL und MariaDB

33.2 Verbindungsherstellung

Obwohl wir im vorherigen Abschnitt die PDO-Schnittstelle behandelt haben, gibt es auch die mysqli-Erweiterung speziell für MySQL/MariaDB:

<?php
// Verbindung mit mysqli
$mysqli = new mysqli('localhost', 'benutzername', 'passwort', 'meine_datenbank');

// Fehlerprüfung
if ($mysqli->connect_errno) {
    die("Verbindungsfehler: " . $mysqli->connect_error);
}

// Zeichensatz setzen
$mysqli->set_charset('utf8mb4');

<?php
try {
    $pdo = new PDO(
        'mysql:host=localhost;dbname=meine_datenbank;charset=utf8mb4',
        'benutzername',
        'passwort',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false
        ]
    );
} catch (PDOException $e) {
    die("Verbindungsfehler: " . $e->getMessage());
}

33.3 Wichtige MySQL/MariaDB-spezifische Einstellungen

Bei der Arbeit mit MySQL/MariaDB sind einige spezifische Einstellungen besonders wichtig:

<?php
// Mit mysqli
$mysqli->set_charset('utf8mb4'); // Wichtig für volle UTF-8-Unterstützung inklusive Emojis

// Mit PDO - charset in der DSN oder mit exec
$pdo = new PDO('mysql:host=localhost;dbname=meine_datenbank;charset=utf8mb4', 'user', 'pass');
// oder
$pdo->exec("SET NAMES utf8mb4");

33.4 MySQL-spezifische SQL-Syntax

MySQL/MariaDB haben einige spezifische SQL-Syntaxerweiterungen, die oft genutzt werden:

<?php
// INSERT mit ON DUPLICATE KEY UPDATE
$stmt = $pdo->prepare("
    INSERT INTO produkte (artikel_nr, name, preis) 
    VALUES (:artikel_nr, :name, :preis)
    ON DUPLICATE KEY UPDATE preis = VALUES(preis)
");
$stmt->execute([
    'artikel_nr' => 'A12345',
    'name' => 'Beispielprodukt',
    'preis' => 19.99
]);

// INSERT IGNORE
$stmt = $pdo->prepare("
    INSERT IGNORE INTO produkte (artikel_nr, name, preis) 
    VALUES (:artikel_nr, :name, :preis)
");

// REPLACE INTO (löscht vorhandenen Datensatz und fügt neuen ein)
$stmt = $pdo->prepare("
    REPLACE INTO produkte (artikel_nr, name, preis) 
    VALUES (:artikel_nr, :name, :preis)
");

// LIMIT in DELETE und UPDATE
$stmt = $pdo->prepare("
    DELETE FROM log_eintraege
    WHERE erstellt_am < :datum
    ORDER BY erstellt_am
    LIMIT 1000
");

// Multi-INSERT (mehrere Datensätze in einer Abfrage einfügen)
$stmt = $pdo->prepare("
    INSERT INTO benutzer (name, email) VALUES 
    (:name1, :email1),
    (:name2, :email2),
    (:name3, :email3)
");
$stmt->execute([
    'name1' => 'Max Mustermann', 'email1' => 'max@example.com',
    'name2' => 'Anna Schmidt', 'email2' => 'anna@example.com',
    'name3' => 'Tom Müller', 'email3' => 'tom@example.com'
]);

33.5 Arbeiten mit AUTO_INCREMENT

<?php
// Einfügen mit AUTO_INCREMENT
$stmt = $pdo->prepare("
    INSERT INTO benutzer (name, email) 
    VALUES (:name, :email)
");
$stmt->execute(['name' => 'Max Mustermann', 'email' => 'max@example.com']);

// Abrufen der letzten ID
$neueId = $pdo->lastInsertId();
echo "Neuer Benutzer mit ID $neueId erstellt!";

33.6 MySQL/MariaDB-Datentypen in PHP

Bei der Arbeit mit MySQL/MariaDB ist es wichtig, die Datentypen korrekt zu handhaben:

<?php
// DATETIME/TIMESTAMP in MySQL 
$stmt = $pdo->prepare("
    INSERT INTO termine (titel, datum_uhrzeit) 
    VALUES (:titel, :datum_uhrzeit)
");
$stmt->execute([
    'titel' => 'Besprechung',
    'datum_uhrzeit' => date('Y-m-d H:i:s') // Format: 'YYYY-MM-DD HH:MM:SS'
]);

// Date-Objekte mit benutzerdefiniertem Format
$datum = new DateTime('next Monday 15:00');
$stmt = $pdo->prepare("
    INSERT INTO termine (titel, datum_uhrzeit) 
    VALUES (:titel, :datum_uhrzeit)
");
$stmt->execute([
    'titel' => 'Wöchentliche Besprechung',
    'datum_uhrzeit' => $datum->format('Y-m-d H:i:s')
]);

// DECIMAL/NUMERIC Datentypen korrekt behandeln
$stmt = $pdo->prepare("
    UPDATE produkte 
    SET preis = :preis 
    WHERE id = :id
");
$stmt->execute([
    'preis' => '24.99', // Als String übergeben für präzise Dezimalzahlen
    'id' => 123
]);

// BOOL/TINYINT(1) Datentypen
$stmt = $pdo->prepare("
    UPDATE benutzer 
    SET aktiv = :aktiv 
    WHERE id = :id
");
$stmt->execute([
    'aktiv' => true, // In MySQL/MariaDB als 1 gespeichert
    'id' => 123
]);

// JSON-Daten (in MySQL 5.7+ und MariaDB 10.2+)
$daten = [
    'präferenzen' => [
        'benachrichtigungen' => true,
        'sprache' => 'de'
    ],
    'letzte_aktivität' => date('Y-m-d')
];

$stmt = $pdo->prepare("
    UPDATE benutzer 
    SET meta_daten = :meta_daten 
    WHERE id = :id
");
$stmt->execute([
    'meta_daten' => json_encode($daten),
    'id' => 123
]);

// JSON-Daten abfragen
$stmt = $pdo->prepare("
    SELECT id, name, meta_daten
    FROM benutzer
    WHERE JSON_EXTRACT(meta_daten, '$.präferenzen.sprache') = :sprache
");
$stmt->execute(['sprache' => 'de']);

33.7 Optimierte Abfragen für große Datensätze

<?php
// Cursor-basierte Verarbeitung großer Ergebnismengen
$stmt = $pdo->prepare("
    SELECT id, name, email 
    FROM benutzer 
    WHERE status = :status
");
$stmt->execute(['status' => 'aktiv']);

// PDO::FETCH_LAZY ermöglicht speichereffizienten Zugriff
$stmt->setFetchMode(PDO::FETCH_LAZY);
while ($row = $stmt->fetch()) {
    // Verarbeite jede Zeile einzeln, ohne alle im Speicher zu halten
    echo "Verarbeite Benutzer: {$row->name} ({$row->email})\n";
}

// Pagination mit LIMIT und OFFSET
$seite = isset($_GET['seite']) ? (int)$_GET['seite'] : 1;
$einträgeProSeite = 20;
$offset = ($seite - 1) * $einträgeProSeite;

$stmt = $pdo->prepare("
    SELECT id, titel, erstellt_am
    FROM blog_artikel
    ORDER BY erstellt_am DESC
    LIMIT :limit OFFSET :offset
");
$stmt->bindValue(':limit', $einträgeProSeite, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$artikel = $stmt->fetchAll();

// Keyset Pagination (effizienter als OFFSET)
$letzteId = isset($_GET['letzte_id']) ? (int)$_GET['letzte_id'] : 0;

$stmt = $pdo->prepare("
    SELECT id, titel, erstellt_am
    FROM blog_artikel
    WHERE id > :letzte_id
    ORDER BY id ASC
    LIMIT :limit
");
$stmt->bindValue(':letzte_id', $letzteId, PDO::PARAM_INT);
$stmt->bindValue(':limit', $einträgeProSeite, PDO::PARAM_INT);
$stmt->execute();
$artikel = $stmt->fetchAll();

33.8 Volltextsuche in MySQL/MariaDB

<?php
// FULLTEXT-Index definieren (in der Tabellendefinition oder mit ALTER TABLE)
/*
CREATE TABLE artikel (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titel VARCHAR(255),
    inhalt TEXT,
    FULLTEXT(titel, inhalt)
);
*/

// Natürliche Sprach-Suche
$suchbegriff = 'php entwicklung';
$stmt = $pdo->prepare("
    SELECT id, titel, inhalt,
           MATCH(titel, inhalt) AGAINST(:suchbegriff IN NATURAL LANGUAGE MODE) AS relevanz
    FROM artikel
    WHERE MATCH(titel, inhalt) AGAINST(:suchbegriff IN NATURAL LANGUAGE MODE)
    ORDER BY relevanz DESC
");
$stmt->execute(['suchbegriff' => $suchbegriff]);
$ergebnisse = $stmt->fetchAll();

// Boolesche Suche mit Operatoren (+muss vorhanden sein, -darf nicht vorhanden sein)
$suchbegriff = '+php -wordpress';
$stmt = $pdo->prepare("
    SELECT id, titel, inhalt
    FROM artikel
    WHERE MATCH(titel, inhalt) AGAINST(:suchbegriff IN BOOLEAN MODE)
");
$stmt->execute(['suchbegriff' => $suchbegriff]);

33.9 Transaktionen und Sperren

<?php
try {
    // Transaktion mit explizitem Lock starten
    $pdo->beginTransaction();
    
    // SELECT ... FOR UPDATE sperrt die ausgewählten Zeilen
    $stmt = $pdo->prepare("
        SELECT guthaben 
        FROM konten 
        WHERE id = :id 
        FOR UPDATE
    ");
    $stmt->execute(['id' => 1]);
    $konto = $stmt->fetch();
    
    if ($konto['guthaben'] >= 100) {
        // Guthaben aktualisieren
        $stmt = $pdo->prepare("
            UPDATE konten 
            SET guthaben = guthaben - 100 
            WHERE id = :id
        ");
        $stmt->execute(['id' => 1]);
        
        // Transaktion in eine andere Tabelle
        $stmt = $pdo->prepare("
            INSERT INTO transaktionen (konto_id, betrag, beschreibung) 
            VALUES (:konto_id, :betrag, :beschreibung)
        ");
        $stmt->execute([
            'konto_id' => 1,
            'betrag' => -100,
            'beschreibung' => 'Abhebung'
        ]);
        
        // Transaktion bestätigen
        $pdo->commit();
        echo "Transaktion erfolgreich!";
    } else {
        // Nicht genug Guthaben
        $pdo->rollBack();
        echo "Nicht genug Guthaben vorhanden!";
    }
} catch (Exception $e) {
    // Bei jedem Fehler Transaktion zurückrollen
    $pdo->rollBack();
    echo "Fehler: " . $e->getMessage();
}

33.10 Optimierungen für MySQL/MariaDB

<?php
// Indexe nutzen
$stmt = $pdo->prepare("
    SELECT * FROM benutzer
    WHERE email = :email
");
$stmt->execute(['email' => 'max@example.com']);

// JOIN mit indexierten Spalten
$stmt = $pdo->prepare("
    SELECT a.titel, k.name as kategorie_name
    FROM artikel a
    JOIN kategorien k ON a.kategorie_id = k.id
    WHERE a.status = :status
");
$stmt->execute(['status' => 'veröffentlicht']);

// GROUP BY mit Indizes
$stmt = $pdo->prepare("
    SELECT kategorie_id, COUNT(*) as anzahl
    FROM artikel
    WHERE status = :status
    GROUP BY kategorie_id
");
$stmt->execute(['status' => 'veröffentlicht']);

// Vermeidung von SELECT *
$stmt = $pdo->prepare("
    SELECT id, titel, erstellt_am
    FROM artikel
    WHERE status = :status
");
$stmt->execute(['status' => 'veröffentlicht']);

// EXPLAIN für Abfrageanalyse
$stmt = $pdo->prepare("
    EXPLAIN SELECT id, titel, inhalt
    FROM artikel
    WHERE kategorie_id = :kategorie_id AND status = :status
");
$stmt->execute(['kategorie_id' => 5, 'status' => 'veröffentlicht']);
$erklärung = $stmt->fetchAll();

33.11 Datenbankverbindungen und Connection Pooling

<?php
// Persistente Verbindungen
$pdo = new PDO(
    'mysql:host=localhost;dbname=meine_datenbank;charset=utf8mb4',
    'benutzername',
    'passwort',
    [
        PDO::ATTR_PERSISTENT => true, // Persistente Verbindung
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]
);

// Verbindungstimeout und Warte-Timeout konfigurieren
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 5); // 5 Sekunden Verbindungs-Timeout

// Auf MySQL/MariaDB-Ebene
$pdo->exec("SET SESSION wait_timeout = 600"); // 10 Minuten
$pdo->exec("SET SESSION interactive_timeout = 600");

33.12 MySQL/MariaDB Replikation und Load Balancing

<?php
// Konfiguration für Master und Slaves
$dbConfig = [
    'master' => [
        'dsn' => 'mysql:host=master-db;dbname=meine_datenbank;charset=utf8mb4',
        'username' => 'master_user',
        'password' => 'master_pass'
    ],
    'slaves' => [
        [
            'dsn' => 'mysql:host=slave1-db;dbname=meine_datenbank;charset=utf8mb4',
            'username' => 'slave_user',
            'password' => 'slave_pass'
        ],
        [
            'dsn' => 'mysql:host=slave2-db;dbname=meine_datenbank;charset=utf8mb4',
            'username' => 'slave_user',
            'password' => 'slave_pass'
        ]
    ]
];

// Einfacher Verbindungsmanager für Master/Slave-Setup
class DBManager {
    private $master = null;
    private $slaves = [];
    private $config;
    
    public function __construct(array $config) {
        $this->config = $config;
    }
    
    public function getMaster() {
        if ($this->master === null) {
            $this->master = new PDO(
                $this->config['master']['dsn'],
                $this->config['master']['username'],
                $this->config['master']['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        }
        return $this->master;
    }
    
    public function getSlave() {
        // Round-Robin oder zufällige Auswahl
        $slaveIndex = array_rand($this->config['slaves']);
        
        if (!isset($this->slaves[$slaveIndex])) {
            $slaveConfig = $this->config['slaves'][$slaveIndex];
            $this->slaves[$slaveIndex] = new PDO(
                $slaveConfig['dsn'],
                $slaveConfig['username'],
                $slaveConfig['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        }
        
        return $this->slaves[$slaveIndex];
    }
    
    public function query($sql, $params = []) {
        // Einfache Erkennung für Lese-/Schreiboperationen
        $isReadOperation = stripos(trim($sql), 'SELECT') === 0;
        
        $pdo = $isReadOperation ? $this->getSlave() : $this->getMaster();
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        
        return $stmt;
    }
}

// Beispielverwendung
$dbManager = new DBManager($dbConfig);

// Leseoperationen werden auf Slaves ausgeführt
$stmt = $dbManager->query("SELECT * FROM artikel WHERE id = :id", ['id' => 5]);
$artikel = $stmt->fetch();

// Schreiboperationen werden auf dem Master ausgeführt
$dbManager->query(
    "UPDATE artikel SET ansichten = ansichten + 1 WHERE id = :id", 
    ['id' => 5]
);

33.13 Migrationen und Schemaverwaltung

Ein wichtiger Aspekt der Arbeit mit MySQL/MariaDB ist die Verwaltung des Datenbankschemas:

<?php
// Einfaches Migration-System
class Migration {
    private $pdo;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        
        // Migrations-Tabelle erstellen, falls nicht vorhanden
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS migrations (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(255) NOT NULL,
                applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ");
    }
    
    public function getAppliedMigrations() {
        $stmt = $this->pdo->query("SELECT name FROM migrations");
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }
    
    public function applyMigration($name, $sql) {
        try {
            // Transaktion starten
            $this->pdo->beginTransaction();
            
            // Migration ausführen
            $this->pdo->exec($sql);
            
            // Migration als angewendet markieren
            $stmt = $this->pdo->prepare("INSERT INTO migrations (name) VALUES (:name)");
            $stmt->execute(['name' => $name]);
            
            // Transaktion bestätigen
            $this->pdo->commit();
            
            echo "Migration '$name' erfolgreich angewendet.\n";
            return true;
        } catch (Exception $e) {
            // Bei Fehler zurückrollen
            $this->pdo->rollBack();
            
            echo "Fehler bei Migration '$name': " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    public function run() {
        // Angewendete Migrationen abrufen
        $appliedMigrations = $this->getAppliedMigrations();
        
        // Migrationsdateien im Verzeichnis suchen
        $migrationFiles = glob(__DIR__ . '/migrations/*.sql');
        sort($migrationFiles);
        
        foreach ($migrationFiles as $file) {
            $name = basename($file);
            
            // Überspringen, wenn bereits angewendet
            if (in_array($name, $appliedMigrations)) {
                continue;
            }
            
            // SQL aus Datei lesen
            $sql = file_get_contents($file);
            
            // Migration anwenden
            $this->applyMigration($name, $sql);
        }
    }
}

// Beispielverwendung
$pdo = new PDO('mysql:host=localhost;dbname=meine_datenbank', 'user', 'pass');
$migration = new Migration($pdo);
$migration->run();

33 MySQL/MariaDB-Integration