Как да промените кодировката на всички таблици и колони в MariaDB/MySQL база данни с помощта на PHP

Промяната на кодировката и колацията на всички таблици и колони в MySQL база данни може да бъде важна стъпка при миграция на данни, оптимизация на производителността или поддръжка на специфични езикови изисквания. В тази статия ще ви покажем как да извършите тази промяна с помощта на PHP скрипт.

Преди да започнем

Преди да пристъпим към изпълнението на PHP скрипта, е важно да направите резервно копие на базата данни. Промените в колацията могат да имат значителни ефекти върху вашите данни и заявки.

Стъпка по стъпка ръководство

1. Конфигурация на базата данни: Настройте вашите база данни, хост, потребителско име и парола.
2. Създаване на връзка с базата данни: Използвайте PDO (PHP Data Objects) за създаване на връзка с базата данни.
3. Извличане на всички таблици: Използвайте SQL заявката SHOW TABLES, за да получите списък с всички таблици в базата данни.
4. Промяна на колацията на таблиците: За всяка таблица изпълнете SQL заявка ALTER TABLE, за да промените колацията на таблицата.
5. Извличане на всички колони: Използвайте SQL заявката SHOW FULL COLUMNS FROM, за да получите информация за всички колони в таблиците.
6. Промяна на колацията на колоните: За всяка текстова колона (като CHAR, VARCHAR, TEXT) изпълнете SQL заявка ALTER TABLE CHANGE, за да промените колацията на колоната.

Примерен PHP скрипт

<?php

// Database configuration
$host = $_ENV['DB_HOST'];
$db = $_ENV['DB_DATABASE'];
$user = $_ENV['DB_USERNAME'];
$pass = $_ENV['DB_PASSWORD'];
$newCollation = 'utf8mb4_unicode_ci'; // Set the desired collation

try {
    // Create a new PDO instance
    $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Get all tables in the database
    $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);

    foreach ($tables as $table) {
        // Change table collation
        $pdo->exec("ALTER TABLE `$table` CONVERT TO CHARACTER SET utf8mb4 COLLATE $newCollation");

        echo "Collation changed successfully for the table $table.\n";

        // Get all columns in the table
        $columns = $pdo->query("SHOW FULL COLUMNS FROM `$table`")->fetchAll(PDO::FETCH_ASSOC);

        foreach ($columns as $column) {
            $columnName = $column['Field'];
            $columnType = $column['Type'];
            $nullable = ('YES' === $column['Null']) ? 'NULL' : 'NOT NULL';
            $default = (null !== $column['Default']) ? 'DEFAULT '.$pdo->quote($column['Default']) : '';
            $extra = $column['Extra'];

            // Change column collation if it's a text-like column type
            if (false !== strpos($columnType, 'char') || false !== strpos($columnType, 'text')) {
                $pdo->exec("ALTER TABLE `$table` CHANGE `$columnName` `$columnName` $columnType COLLATE $newCollation $nullable $default $extra");
                echo "Collation changed successfully for the column $columnName in the table $table.\n";
            }
        }
    }

    echo 'Collation changed successfully for all tables and columns.';
} catch (PDOException $e) {
    echo 'Error: '.$e->getMessage();
}

Важни бележки

• Скриптът се фокусира върху текстовите колони (като CHAR, VARCHAR, TEXT) за промяна на колацията.
• Уверете се, че вашата база данни поддържа целевата колация и кодировка.
• Тествайте този скрипт в безопасна среда (напр. в тестова среда) преди да го приложите в продукция и винаги имайте резервно копие.