So konvertieren Sie utf8-Tabellen in MySQL 5.5 einfach in utf8mb4

In meinem Handbuch So unterstützen Sie vollständigen Unicode in MySQL-Datenbanken finden Sie hier die Abfragen, die Sie ausführen können, um den Zeichensatz und die Sortierung einer Datenbank, einer Tabelle oder einer Spalte zu aktualisieren:

Für jede Datenbank:

ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

Für jede Tabelle:

ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Für jede Spalte:

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

(Kopieren Sie dies nicht blind und fügen Sie es ein! Die genaue Anweisung hängt vom Spaltentyp, der maximalen Länge und anderen Eigenschaften ab. Die obige Zeile ist nur ein Beispiel für ein VARCHAR Spalte.)

Beachten Sie jedoch, dass Sie die Konvertierung von utf8 Nach utf8mb4 Nicht vollständig automatisieren können. Wie in Schritt 4 des oben genannten Handbuchs beschrieben, müssen Sie die maximale Länge von Spalten und Indexschlüsseln überprüfen, da die von Ihnen angegebene Zahl eine andere Bedeutung hat, wenn utf8mb4 Verwendet wird anstelle von utf8.

Abschnitt 10.1.11 des MySQL 5.5-Referenzhandbuchs enthält weitere Informationen dazu.

Ich habe eine Lösung, die Datenbanken und Tabellen durch Ausführen einiger Befehle konvertiert. Es konvertiert auch alle Spalten vom Typ varchar, text, tinytext, mediumtext, longtext, char. Sie sollten auch sichern Sie Ihre Datenbank für den Fall, dass etwas kaputt geht.

Kopieren Sie den folgenden Code in eine Datei namens 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');

Ersetzen Sie alle Vorkommen von "yourDbName" durch die Datenbank, die Sie konvertieren möchten. Dann renne:

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

Dadurch wird eine neue Datei alterTables.sql mit allen Abfragen generiert, die Sie zum Konvertieren der Datenbank benötigen. Führen Sie den folgenden Befehl aus, um die Konvertierung zu starten:

mysql -uroot < alterTables.sql

Sie können dies auch anpassen, um mehrere Datenbanken zu durchlaufen, indem Sie die Bedingung für das Tabellenschema ändern. Beispielsweise table_schema like "wiki_%" konvertiert alle Datenbanken mit dem Namenspräfix wiki_. Um alle Datenbanken zu konvertieren, ersetzen Sie die Bedingung durch table_type!='SYSTEM VIEW'.

Ein Problem, das auftreten könnte. Ich hatte einige varchar (255) Spalten in MySQL-Schlüsseln. Dies verursacht einen Fehler:

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

In diesem Fall können Sie die Spalte einfach so ändern, dass sie kleiner ist, z. B. varchar (150), und den Befehl erneut ausführen.

Bitte beachten Sie: Diese Antwort konvertiert die Datenbank in utf8mb4_unicode_ci Anstatt von utf8mb4_bin, in der Frage gestellt. Aber Sie können dies einfach ersetzen.

Ich habe das folgende Shell-Skript verwendet. Es verwendet den Datenbanknamen als Parameter und konvertiert alle Tabellen in einen anderen Zeichensatz und eine andere Sortierung (angegeben durch andere Parameter oder einen im Skript definierten Standardwert).

#!/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
)

Ich würde ein Skript (in Perl oder was auch immer) schreiben, um information_schema (TABLES und COLUMNS) zu verwenden, um durch alle Tabellen zu gehen, und MODIFY COLUMN für jedes CHAR/VARCHAR/TEXT-Feld auszuführen. Ich würde alle MODIFYs in einem einzigen ALTER für jede Tabelle sammeln; Dies wird effizienter sein.

Ich denke (bin mir aber nicht sicher), dass Raihans Vorschlag nur das Standard für die Tabelle ändert.

Bin in diese Situation geraten; Hier ist der Ansatz, mit dem ich meine Datenbank konvertiert habe:

1. Zuerst müssen Sie my.cnf Bearbeiten, um die Standarddatenbankverbindung (zwischen Anwendungen und MYSQL) utf8mb4_unicode_ci kompatibel zu machen. Ohne diese Zeichen wie Emojis und ähnliches, die von Ihren Apps übermittelt werden, gelangen sie nicht in den richtigen Bytes/Codierungen zu Ihren Tabellen (es sei denn, die DB CNN-Parameter Ihrer Anwendung geben eine utf8mb4-Verbindung an).

   Anweisungen gegeben hier .
   
   

2. Führen Sie die folgende SQL aus (es ist nicht erforderlich, SQL vorzubereiten, um einzelne Spalten zu ändern. ALTER TABLE - Anweisungen führen dies aus).

   Bevor Sie den folgenden Code ausführen, ersetzen Sie "DbName" durch Ihren tatsächlichen DB-Namen.

   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. Sammeln und speichern Sie die Ausgabe des obigen SQL in einer Punkt-SQL-Datei und führen Sie sie aus.
   
   

4. Wenn Sie einen Fehler wie #1071 - Specified key was too long; max key length is 1000 bytes. Zusammen mit dem problematischen Tabellennamen erhalten, bedeutet dies, dass der Indexschlüssel für eine Spalte dieser Tabelle (die in MB4-Zeichenfolge konvertiert werden sollte) sehr groß ist, daher sollte diese Varchar-Spalte sein <= 250, so dass sein Indexschlüssel maximal 1000 Bytes beträgt. Überprüfen Sie die Spalten, für die Sie Indizes haben, und wenn eine davon ein varchar> 250 (höchstwahrscheinlich 255) ist, dann

   • Schritt 1: Überprüfen Sie die Daten in dieser Spalte, um sicherzustellen, dass die maximale Zeichenfolgengröße in dieser Spalte <= 250 ist.

     Beispielabfrage:

     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;
     

   • Schritt 2: Wenn die maximale Länge der indizierten Spaltendaten <= 250 ist, ändern Sie die Spaltenlänge auf 250. Wenn dies nicht möglich ist, entfernen Sie den Index für diese Spalte

   • Schritt 3: Führen Sie dann die Abfrage zum Ändern der Tabelle für diese Tabelle erneut aus, und die Tabelle sollte nun erfolgreich in utf8mb4 konvertiert werden.

Prost!

Für Personen, die dieses Problem haben könnten, besteht die beste Lösung darin, zuerst die Spalten gemäß dieser Tabelle in einen Binärtyp zu ändern:

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

Ändern Sie anschließend die Spalte wieder in den vorherigen Typ und mit dem gewünschten Zeichensatz.

Z.B.:

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

Ich habe es in mehreren lateinischen Tabellen versucht und es hat alle diakritischen Zeichen beibehalten.

Sie können diese Abfrage für alle Spalten extrahieren, die dies tun:

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%');

Ich habe diesen Leitfaden geschrieben: http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4

Bei meiner Arbeit habe ich gesehen, dass ALTER die Datenbank und die Tabellen nicht ausreichen. Ich musste in jede Tabelle gehen und auch jede der Spalten text/mediumtext/varchar ändern.

Glücklicherweise konnte ich ein Skript schreiben, um die Metadaten von MySQL-Datenbanken zu erkennen, sodass die Tabellen und Spalten durchlaufen und automatisch geändert werden konnten.

Langer Index für MySQL 5.6:

Sie müssen über die Berechtigung DBA/SUPER USER verfügen: Festlegen der Datenbankparameter:

 innodb_large_prefix: ON 
 innodb_file_format: Barracuda 
 Innodb_file_format_max: Barracuda 

In den Antworten auf diese Frage finden Sie Anweisungen zum Festlegen dieser Parameter: https://stackoverflow.com/questions/35847015/mysql-change-innodb-large-prefix

Natürlich gibt es in meinem Artikel auch Anweisungen dazu.

Für MySQL Version 5.7 oder neuer ist der innodb_large_prefix standardmäßig aktiviert, und das innodb_file_format ist standardmäßig auch Barracuda.

Ich habe ein Skript erstellt das macht das mehr oder weniger automatisch:

<?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";
}