Comment convertir toutes les tables de la base de données en une seule collation ?

Question

Je reçois une erreur :

Mélange illégal de collations (utf8_general_ci,IMPLICIT) et (utf8_unicode_ci,IMPLICIT) pour l'opération '='".

J'ai essayé de changer manuellement les deux tables en utf8_general_ci,IMPLICIT mais je reçois toujours l'erreur.

Existe-t-il un moyen de convertir toutes les tables en utf8_general_ci,IMPLICIT et en finir avec ça ?

Answer 1

Vous devez exécuter une instruction alter table pour chaque table. L'instruction doit prendre la forme suivante :

ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]

Maintenant, pour obtenir toutes les tables de la base de données, vous devez exécuter la requête suivante :

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDataBaseName"
AND TABLE_TYPE="BASE TABLE";

Laissez maintenant MySQL écrire le code pour vous :

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE your_collation_name_here;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDatabaseName"
AND TABLE_TYPE="BASE TABLE";

Vous pouvez copier les résultats et les exécuter. Je n'ai pas testé la syntaxe mais vous devriez être capable de comprendre le reste. Considérez cela comme un petit exercice.

J'espère que cela vous aidera !

Answer 2

Meilleure option pour changer aussi la collation des colonnes varchar à l'intérieur d'une table

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS    mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= "myschema"
AND TABLE_TYPE="BASE TABLE"

De plus si vous avez des données avec une clé forein sur une colonne non utf8 avant de lancer le bouquet script utiliser

SET foreign_key_checks = 0;

Cela signifie que le SQL global sera pour mySQL :

SET foreign_key_checks = 0;
ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `tableXXX` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
SET foreign_key_checks = 1;

Mais faites attention si selon la documentation mysql http://dev.mysql.com/doc/refman/5.1/en/charset-column.html ,

Si vous utilisez ALTER TABLE pour convertir une colonne d'un jeu de caractères à un autre, MySQL tente de mapper les valeurs des données, mais si les jeux de caractères sont incompatibles, il peut y avoir des pertes de données. "

EDIT : Spécialement avec le type de colonne enum, il plante complètement l'ensemble des enums (même s'il n'y a pas de caractères spéciaux). https://bugs.mysql.com/bug.php?id=26731

Answer 3

La suggestion de @Namphibian m'a beaucoup aidé...
est allé un peu plus loin cependant et a ajouté des colonnes et des vues au script.

Il suffit d'entrer le nom de votre schéma ci-dessous et le système fera le reste.

-- set your table name here
SET @MY_SCHEMA = "";

-- tables
SELECT DISTINCT
    CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=@MY_SCHEMA
  AND TABLE_TYPE="BASE TABLE"

UNION

-- table columns
SELECT DISTINCT
    CONCAT("ALTER TABLE ", C.TABLE_NAME, " CHANGE ", C.COLUMN_NAME, " ", C.COLUMN_NAME, " ", C.COLUMN_TYPE, " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
FROM INFORMATION_SCHEMA.COLUMNS as C
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.COLLATION_NAME is not null
    AND C.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="BASE TABLE"

UNION

-- views
SELECT DISTINCT
    CONCAT("CREATE OR REPLACE VIEW ", V.TABLE_NAME, " AS ", V.VIEW_DEFINITION, ";") as queries
FROM INFORMATION_SCHEMA.VIEWS as V
    LEFT JOIN INFORMATION_SCHEMA.TABLES as T
        ON V.TABLE_NAME = T.TABLE_NAME
WHERE V.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="VIEW";

Answer 4

Voici la requête la plus précise. Je donne un exemple de conversion en utf8.

SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;") AS    mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="myschema"
AND TABLE_TYPE="BASE TABLE"

Answer 5

Si vous utilisez PhpMyAdmin, vous pouvez maintenant :

1. Sélectionnez la base de données.
2. Cliquez sur l'onglet "Opérations".
3. Dans la section "Collation", sélectionnez la collation souhaitée.
4. Cliquez sur la case à cocher "Modifier les collations de toutes les tables".
5. Une nouvelle case à cocher "Modifier les collations de toutes les colonnes des tableaux" apparaît.
6. Cliquez sur la case à cocher "Modifier les collations des colonnes de toutes les tables".
7. Cliquez sur le bouton "Go".

J'avais plus de 250 tableaux à convertir. Cela a pris un peu plus de 5 minutes.