Jak łatwo przekonwertować tabele utf8 na utf8mb4 w MySQL 5.5

Mam bazę danych, która teraz musi obsługiwać 4 bajty znaków (chiński). Na szczęście mam już MySQL 5.5 w produkcji.

Chciałbym więc po prostu utworzyć wszystkie zestawienia, które są utf8_bin do utf8mb4_bin.

Uważam, że przy tej zmianie nie ma utraty / przyrostu wydajności poza odrobiną narzutu pamięci.

Z mojego przewodnika Jak obsługiwać pełny Unicode w bazach danych MySQL , oto zapytania, które możesz uruchomić, aby zaktualizować zestaw znaków i zestawienie bazy danych, tabeli lub kolumny:

Dla każdej bazy danych:

ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

Dla każdej tabeli:

ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Dla każdej kolumny:

ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

(Nie ślepo kopiuj-wklej to! Dokładna instrukcja zależy od typu kolumny, maksymalnej długości i innych właściwości. Powyższy wiersz jest tylko przykładem VARCHARkolumny.)

Należy jednak pamiętać, że nie można w pełni zautomatyzować konwersji z utf8na utf8mb4. Jak opisano w kroku 4 powyższego przewodnika , musisz sprawdzić maksymalną długość kolumn i kluczy indeksowych, ponieważ podana liczba ma inne znaczenie, gdy utf8mb4jest używana zamiast utf8.

Sekcja 10.1.11 podręcznika MySQL 5.5 zawiera więcej informacji na ten temat.

Mam rozwiązanie, które przekonwertuje bazy danych i tabele, uruchamiając kilka poleceń. To również konwertuje wszystkie kolumny typu varchar, text, tinytext, mediumtext, longtext, char. Należy również wykonać kopię zapasową bazy danych na wypadek, gdyby coś się zepsuło.

Skopiuj następujący kod do pliku o nazwie preAlterTables.sql:

use information_schema;
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql 
FROM `TABLES` where table_schema like "yourDbName" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql  
FROM `TABLES` where table_schema like "yourDbName" group by table_schema, table_name;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") as _sql 
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('varchar','char');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") as _sql 
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('text','tinytext','mediumtext','longtext');

Zamień wszystkie wystąpienia „twojaDbName” na bazę danych, którą chcesz przekonwertować. Następnie uruchomić:

mysql -uroot < preAlterTables.sql | egrep '^ALTER' > alterTables.sql

Spowoduje to wygenerowanie nowego pliku alterTables.sql ze wszystkimi zapytaniami potrzebnymi do konwersji bazy danych. Uruchom następujące polecenie, aby rozpocząć konwersję:

mysql -uroot < alterTables.sql

Można to również dostosować, aby działało w wielu bazach danych, zmieniając warunek dla tabeli_schema. Na przykład table_schema like "wiki_%"skonwertuje wszystkie bazy danych z prefiksem nazwy wiki_. Aby przekonwertować wszystkie bazy danych, zamień warunek na table_type!='SYSTEM VIEW'.

Problem, który może się pojawić. Miałem trochę kolumn varchar (255) w kluczach mysql. To powoduje błąd:

ERROR 1071 (42000) at line 2229: Specified key was too long; max key length is 767 bytes

Jeśli tak się stanie, możesz po prostu zmienić kolumnę na mniejszą, jak varchar (150), i ponownie uruchomić polecenie.

Uwaga : Ta odpowiedź konwertuje bazę danych na utf8mb4_unicode_cizamiast utf8mb4_bin, zadane w pytaniu. Ale możesz to po prostu zastąpić.

Użyłem następującego skryptu powłoki. Pobiera nazwę bazy danych jako parametr i konwertuje wszystkie tabele na inny zestaw znaków i zestawianie (podane przez inne parametry lub wartość domyślną zdefiniowaną w skrypcie).

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
CHARSET="$2"
COLL="$3"

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"

echo $DB
echo "ALTER DATABASE \`$DB\` CHARACTER SET $CHARSET COLLATE $COLL;" | mysql

echo "USE \`$DB\`; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE \`$TABLE\` CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
    done
)

Napisałbym skrypt (w Perlu lub cokolwiek innego), aby użyć schematu informacji (TABLES i COLUMNS) do przejścia przez wszystkie tabele, i wykonać MODYFIKACJĘ KOLUMNY na każdym polu CHAR / VARCHAR / TEXT. Zebrałbym wszystkie MODYFIKACJE w jedną ZMIANĘ dla każdego stołu; będzie to bardziej wydajne.

Myślę (ale nie jestem pewien), że sugestia Raihan zmienia tylko domyślną tabelę.

Wpadłem na tę sytuację; oto podejście zastosowane do konwersji mojej bazy danych:

1. Najpierw musisz dokonać edycji, my.cnfaby domyślne połączenie z bazą danych (między aplikacjami a MYSQL) było zgodne z utf8mb4_unicode_ci. Bez tych znaków, takich jak emotikony i podobne przesłane przez aplikacje, nie trafią one do tabel w prawidłowych bajtach / kodowaniu (chyba że parametry DBN CN aplikacji określają połączenie utf8mb4).

   Instrukcje podane tutaj .
   
   

2. Wykonaj następujący SQL (nie trzeba przygotowywać SQL do zmiany poszczególnych kolumn, ALTER TABLEinstrukcje to zrobią).

   Przed wykonaniem poniższego kodu zamień „DbName” na rzeczywistą nazwę DB.

   USE information_schema;
   
   SELECT concat("ALTER DATABASE `",table_schema,
                 "` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql
     FROM `TABLES`
    WHERE table_schema like "DbName"
    GROUP BY table_schema;
   
   SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,
                 "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql
     FROM `TABLES`
    WHERE table_schema like "DbName"
    GROUP BY table_schema, table_name;

3. Zbierz i zapisz dane wyjściowe powyższego kodu SQL w pliku dot sql i uruchom je.
   
   

4. Jeśli pojawi się błąd podobny #1071 - Specified key was too long; max key length is 1000 bytes.do problematycznej nazwy tabeli, oznacza to, że klucz indeksu w pewnej kolumnie tej tabeli (która miała zostać przekonwertowana na ciąg znaków MB4) będzie bardzo duży, dlatego kolumna Varchar powinna mieć wartość <= 250, aby jej klucz indeksu będzie miał maksymalnie 1000 bajtów. Sprawdź kolumny, w których masz indeksy, a jeśli jeden z nich to varchar> 250 (najprawdopodobniej 255), to

   • Krok 1: sprawdź dane w tej kolumnie, aby upewnić się, że maksymalny rozmiar ciągu w tej kolumnie wynosi <= 250.

     Przykładowe zapytanie:

     select `id`,`username`, `email`,
            length(`username`) as l1,
            char_length(`username`) as l2,
            length(`email`) as l3,
            char_length(`email`) as l4
       from jos_users
      order by l4 Desc;

   • Krok 2: Jeśli maksymalna długość danych indeksowanych kolumn <= 250, zmień długość kolumny na 250. Jeśli nie jest to możliwe, usuń indeks z tej kolumny

   • Krok 3: Następnie ponownie uruchom zapytanie o tabelę zmian dla tej tabeli i tabela powinna teraz zostać pomyślnie przekonwertowana na utf8mb4.

Twoje zdrowie!

Napisałem ten przewodnik: http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4

Z mojej pracy widziałem, że ZMIENIAJ bazę danych i tabele to za mało. Musiałem wejść do każdej tabeli i ZMIEŃ każdą z kolumn text / mediumtext / varchar.

Na szczęście udało mi się napisać skrypt wykrywający metadane baz danych MySQL, dzięki czemu mógł on przeglądać tabele i kolumny i zmieniać je automatycznie.

Długi indeks dla MySQL 5.6:

Jest jedna rzecz, do której musisz mieć uprawnienia DBA / SUPER USER: ustawienie parametrów bazy danych:

innodb_large_prefix: ON
innodb_file_format: Barracuda 
innodb_file_format_max: Barracuda

W odpowiedziach na to pytanie znajduje się instrukcja ustawiania powyższych parametrów powyżej: https://stackoverflow.com/questions/35847015/mysql-change-innodb-large-prefix

Oczywiście w moim artykule są też instrukcje, aby to zrobić.

W MySQL w wersji 5.7 lub nowszej domyślnie przedrostek innodb_large_prefix jest domyślnie WŁĄCZONY, a format pliku innodb_file_format jest domyślnie także Barracuda.

Dla osób, które mogą mieć ten problem, najlepszym rozwiązaniem jest zmodyfikowanie najpierw kolumn na typ binarny, zgodnie z poniższą tabelą:

1. CHAR => BINARY
2. TEKST => BLOB
3. TINYTEXT => TINYBLOB
4. MEDIUMTEXT => MEDIUMBLOB
5. LONGTEXT => LONGBLOB
6. VARCHAR => VARBINARY

Następnie zmodyfikuj kolumnę z powrotem do jej poprzedniego typu i pożądanym zestawem znaków.

Na przykład.:

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] LONGBLOB;
ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARCHAR(140) CHARACTER SET utf8mb4;

Próbowałem na kilku stołach Latin1 i zachowałem wszystkie znaki diakrytyczne.

Możesz wyodrębnić to zapytanie dla wszystkich kolumn, które to robią:

SELECT
CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' VARBINARY;'),
CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.columns
WHERE TABLE_SCHEMA IN ('[TABLE_SCHEMA]')
AND COLUMN_TYPE LIKE 'varchar%'
AND (COLLATION_NAME IS NOT NULL AND COLLATION_NAME NOT LIKE 'utf%');

I wykonany skrypt , który robi to mniej lub bardziej automatycznie:

<?php
/**
 * Requires php >= 5.5
 * 
 * Use this script to convert utf-8 data in utf-8 mysql tables stored via latin1 connection
 * This is a PHP port from: https://gist.github.com/njvack/6113127
 *
 * BACKUP YOUR DATABASE BEFORE YOU RUN THIS SCRIPT!
 *
 * Once the script ran over your databases, change your database connection charset to utf8:
 *
 * $dsn = 'mysql:host=localhost;port=3306;charset=utf8';
 * 
 * DON'T RUN THIS SCRIPT MORE THAN ONCE!
 *
 * @author hollodotme
 *
 * @author derclops since 2019-07-01
 *
 *         I have taken the liberty to adapt this script to also do the following:
 *
 *         - convert the database to utf8mb4
 *         - convert all tables to utf8mb4
 *         - actually then also convert the data to utf8mb4
 *
 */

header('Content-Type: text/plain; charset=utf-8');

$dsn      = 'mysql:host=localhost;port=3306;charset=utf8';
$user     = 'root';
$password = 'root';
$options  = [
    \PDO::ATTR_CURSOR                   => \PDO::CURSOR_FWDONLY,
    \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    \PDO::MYSQL_ATTR_INIT_COMMAND       => "SET CHARACTER SET latin1",
];


$dbManager = new \PDO( $dsn, $user, $password, $options );

$databasesToConvert = [ 'database1',/** database3, ... */ ];
$typesToConvert     = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext' ];

foreach ( $databasesToConvert as $database )
{
    echo $database, ":\n";
    echo str_repeat( '=', strlen( $database ) + 1 ), "\n";

    $dbManager->exec( "USE `{$database}`" );

    echo "converting database to correct locale too ... \n";

    $dbManager->exec("ALTER DATABASE `{$database}` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci");


    $tablesStatement = $dbManager->query( "SHOW TABLES" );
    while ( ($table = $tablesStatement->fetchColumn()) )
    {
        echo "Table: {$table}:\n";
        echo str_repeat( '-', strlen( $table ) + 8 ), "\n";

        $columnsToConvert = [ ];

        $columsStatement = $dbManager->query( "DESCRIBE `{$table}`" );

        while ( ($tableInfo = $columsStatement->fetch( \PDO::FETCH_ASSOC )) )
        {
            $column = $tableInfo['Field'];
            echo ' * ' . $column . ': ' . $tableInfo['Type'];

            $type = preg_replace( "#\(\d+\)#", '', $tableInfo['Type'] );

            if ( in_array( $type, $typesToConvert ) )
            {
                echo " => must be converted\n";

                $columnsToConvert[] = $column;
            }
            else
            {
                echo " => not relevant\n";
            }
        }


        //convert table also!!!
        $convert = "ALTER TABLE `{$table}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

        echo "\n", $convert, "\n";
        $dbManager->exec( $convert );
        $databaseErrors = $dbManager->errorInfo();
        if( !empty($databaseErrors[1]) ){
            echo "\n !!!!!!!!!!!!!!!!! ERROR OCCURED ".print_r($databaseErrors, true)." \n";
            exit;
        }


        if ( !empty($columnsToConvert) )
        {
            $converts = array_map(
                function ( $column )
                {
                    //return "`{$column}` = IFNULL(CONVERT(CAST(CONVERT(`{$column}` USING latin1) AS binary) USING utf8mb4),`{$column}`)";
                    return "`{$column}` = CONVERT(BINARY(CONVERT(`{$column}` USING latin1)) USING utf8mb4)";
                },
                $columnsToConvert
            );

            $query = "UPDATE IGNORE `{$table}` SET " . join( ', ', $converts );

            //alternative
            // UPDATE feedback SET reply = CONVERT(BINARY(CONVERT(reply USING latin1)) USING utf8mb4) WHERE feedback_id = 15015;


            echo "\n", $query, "\n";


            $dbManager->exec( $query );

            $databaseErrors = $dbManager->errorInfo();
            if( !empty($databaseErrors[1]) ){
                echo "\n !!!!!!!!!!!!!!!!! ERROR OCCURED ".print_r($databaseErrors, true)." \n";
                exit;
            }
        }

        echo "\n--\n";
    }

    echo "\n";
}