php une Un script pour modifier toutes les tables et tous les champs dans le classement utf-8-bin dans MYSQL



remplacer une valeur mysql (13)

Existe-t-il un script SQL ou PHP que je puisse utiliser pour modifier le classement par défaut dans toutes les tables et tous les champs d'une base de données?

Je peux en écrire un moi-même, mais je pense que cela devrait être quelque chose facilement disponible sur un site comme celui-ci. Si je peux en trouver un moi-même avant que quelqu'un n'en publie un, je le posterai moi-même.


Answer #1

Faites attention! Si vous avez réellement utf stocké comme un autre encodage, vous pourriez avoir un vrai bordel sur vos mains. Sauvegardez d'abord. Ensuite, essayez quelques-unes des méthodes standard:

par exemple http://www.cesspit.net/drupal/node/898 http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html

J'ai dû recourir à la conversion de tous les champs de texte en binaire, puis revenir à varchar / texte. Cela a sauvé mon cul.

J'avais des données en UTF8, stockées en latin1. Ce que j'ai fait:

Supprimer les index. Convertir les champs en binaire. Convertir à utf8-général ci

Si vous êtes sur LAMP, n'oubliez pas d'ajouter la commande set NAMES avant d'interagir avec la base de données, et assurez-vous de définir des en-têtes de codage de caractères.



Answer #3

J'ai mis à jour la réponse de nlaq pour travailler avec PHP7 et gérer correctement les index multicolonnes, les données binaires assemblées (par exemple latin1_bin ), etc., et nettoyer un peu le code. C'est le seul code que j'ai trouvé / essayé qui a migré avec succès ma base de données de latin1 à utf8.

<?php

/////////// BEGIN CONFIG ////////////////////

$username = "";
$password = "";
$db = "";
$host = "";

$target_charset = "utf8";
$target_collation = "utf8_unicode_ci";
$target_bin_collation = "utf8_bin";

///////////  END CONFIG  ////////////////////

function MySQLSafeQuery($conn, $query) {
    $res = mysqli_query($conn, $query);
    if (mysqli_errno($conn)) {
        echo "<b>Mysql Error: " . mysqli_error($conn) . "</b>\n";
        echo "<span>This query caused the above error: <i>" . $query . "</i></span>\n";
    }
    return $res;
}

function binary_typename($type) {
    $mysql_type_to_binary_type_map = array(
        "VARCHAR" => "VARBINARY",
        "CHAR" => "BINARY(1)",
        "TINYTEXT" => "TINYBLOB",
        "MEDIUMTEXT" => "MEDIUMBLOB",
        "LONGTEXT" => "LONGBLOB",
        "TEXT" => "BLOB"
    );

    $typename = "";
    if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat))
        $typename = $mysql_type_to_binary_type_map["VARCHAR"] . "(" . (2*$mat[1]) . ")";
    else if (!strcasecmp($type, "CHAR"))
        $typename = $mysql_type_to_binary_type_map["CHAR"] . "(1)";
    else if (array_key_exists(strtoupper($type), $mysql_type_to_binary_type_map))
        $typename = $mysql_type_to_binary_type_map[strtoupper($type)];
    return $typename;
}

echo "<pre>";

// Connect to database
$conn = mysqli_connect($host, $username, $password);
mysqli_select_db($conn, $db);

// Get list of tables
$tabs = array();
$query = "SHOW TABLES";
$res = MySQLSafeQuery($conn, $query);
while (($row = mysqli_fetch_row($res)) != null)
    $tabs[] = $row[0];

// Now fix tables
foreach ($tabs as $tab) {
    $res = MySQLSafeQuery($conn, "SHOW INDEX FROM `{$tab}`");
    $indicies = array();

    while (($row = mysqli_fetch_array($res)) != null) {
        if ($row[2] != "PRIMARY") {
            $append = true;
            foreach ($indicies as $index) {
                if ($index["name"] == $row[2]) {
                    $index["col"][] = $row[4];
                    $append = false;
                }
            }
            if($append)
                $indicies[] = array("name" => $row[2], "unique" => !($row[1] == "1"), "col" => array($row[4]));
        }
    }

    foreach ($indicies as $index) {
        MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` DROP INDEX `{$index["name"]}`");
        echo "Dropped index {$index["name"]}. Unique: {$index["unique"]}\n";
    }

    $res = MySQLSafeQuery($conn, "SHOW FULL COLUMNS FROM `{$tab}`");
    while (($row = mysqli_fetch_array($res)) != null) {
        $name = $row[0];
        $type = $row[1];
        $current_collation = $row[2];
        $target_collation_bak = $target_collation;
        if(!strcasecmp($current_collation, "latin1_bin"))
            $target_collation = $target_bin_collation;
        $set = false;
        $binary_typename = binary_typename($type);
        if ($binary_typename != "") {
            MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` MODIFY `{$name}` {$binary_typename}");
            MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` MODIFY `{$name}` {$type} CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");
            $set = true;
            echo "Altered field {$name} on {$tab} from type {$type}\n";
        }
        $target_collation = $target_collation_bak;
    }

    // Rebuild indicies
    foreach ($indicies as $index) {
         // Handle multi-column indices
         $joined_col_str = "";
         foreach ($index["col"] as $col)
             $joined_col_str = $joined_col_str . ", `" . $col . "`";
         $joined_col_str = substr($joined_col_str, 2);

         $query = "";
         if ($index["unique"])
             $query = "CREATE UNIQUE INDEX `{$index["name"]}` ON `{$tab}` ({$joined_col_str})";
         else
             $query = "CREATE INDEX `{$index["name"]}` ON `{$tab}` ({$joined_col_str})";
         MySQLSafeQuery($conn, $query);

        echo "Created index {$index["name"]} on {$tab}. Unique: {$index["unique"]}\n";
    }

    // Set default character set and collation for table
    MySQLSafeQuery($conn, "ALTER TABLE `{$tab}`  DEFAULT CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");
}

// Set default character set and collation for database
MySQLSafeQuery($conn, "ALTER DATABASE `{$db}` DEFAULT CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");

mysqli_close($conn);
echo "</pre>";

?>

Answer #4

Dans les scripts au-dessus de toutes les tables sélectionnées pour la conversion (avec SHOW TABLES ), mais un moyen plus pratique et portable pour vérifier le classement de la table avant de convertir une table. Cette requête le fait:

SELECT table_name
     , table_collation 
FROM information_schema.tables

Answer #5

Je pense qu'il est facile de le faire en deux étapes runin PhpMyAdmin.
Étape 1:

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`,
 '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as stmt 
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'database_name'
ORDER BY 1

Étape 2:
Cette requête affichera une liste de requêtes, une pour chaque table. Vous devez copier la liste des requêtes et les coller dans la ligne de commande ou dans l'onglet SQL de PhpMyAdmin pour les modifications à effectuer.


Answer #6

Une solution simple (stupide? :), en utilisant la fonctionnalité multi-sélection de votre IDE:

  1. exécutez "SHOW TABLES"; requête et copier la colonne des résultats (noms de table).
  2. débuts multiples et ajoutez "ALTER TABLE".
  3. finit plusieurs fins et ajoute "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"
  4. exécuter des requêtes créées.

Answer #7

Cet extrait PHP va changer le classement sur toutes les tables dans un DB. (Il est pris sur ce site .)

<?php
// your connection
mysql_connect("localhost","root","***");
mysql_select_db("db1");

// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
    foreach ($row as $key => $table)
    {
        mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
        echo $key . " =&gt; " . $table . " CONVERTED<br />";
    }
}
?> 

Answer #8

Le jeu de caractères et le classement ne sont pas la même chose. Un classement est un ensemble de règles sur la manière de trier les chaînes. Un charset est un ensemble de règles sur la façon de représenter les caractères. Une collation dépend du jeu de caractères.


Answer #9

Je pense que le moyen le plus rapide est avec phpmyadmin et certains jQuery sur la console.

Allez dans la structure de la table et ouvrez la console du développeur chrome / firefox (normalement F12 sur le clavier):

  1. exécutez ce code pour sélectionner tous les champs avec un charset incorrect et commencer à modifier:

    var elems = $('dfn'); var lastID = elems.length - 1;
    elems.each(function(i) {
        if ($(this).html() != 'utf8_general_ci') { 
           $('input:checkbox', $('td', $(this).parent().parent()).first()).attr('checked','checked');
        }       
    
        if (i == lastID) {
            $("button[name='submit_mult'][value='change']").click();
        }
    });
  2. lorsque la page est chargée, utilisez ce code sur la console pour sélectionner le codage correct:

    $("select[name*='field_collation']" ).val('utf8_general_ci');
  3. enregistrer

  4. changer le jeu de caractères de la table dans le champ "Collation" de l'onglet "Opération"

Testé sur phpmyadmin 4.0 et 4.4, mais je pense que le travail sur toutes les versions 4.x


Answer #10

Peut être fait dans une seule commande (plutôt que 148 de PHP):

mysql --database=dbname -B -N -e "SHOW TABLES" \
| awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql --database=dbname &

Vous devez aimer la ligne de commande ... (Vous devrez peut-être utiliser les options --user et --password pour mysql ).

EDIT: pour éviter les problèmes de clé étrangère, ajouter SET foreign_key_checks = 0; et SET foreign_key_checks = 1;


Answer #11

OK, j'ai écrit ceci en tenant compte de ce qui a été dit dans ce fil. Merci pour l'aide, et j'espère que ce script aidera les autres. Je n'ai aucune garantie pour son utilisation, VEUILLEZ SVP SAUVEGARDER avant de l'exécuter. Cela devrait fonctionner avec toutes les bases de données; et ça a marché très bien tout seul.

EDIT: Ajout de vars en haut pour le charset / collate à convertir. EDIT2: modifie le jeu de caractères / assemblages par défaut de la base de données et des tables

<?php

function MysqlError()
{
    if (mysql_errno())
    {
        echo "<b>Mysql Error: " . mysql_error() . "</b>\n";
    }
}

$username = "root";
$password = "";
$db = "database";
$host = "localhost";

$target_charset = "utf8";
$target_collate = "utf8_general_ci";

echo "<pre>";

$conn = mysql_connect($host, $username, $password);
mysql_select_db($db, $conn);

$tabs = array();
$res = mysql_query("SHOW TABLES");
MysqlError();
while (($row = mysql_fetch_row($res)) != null)
{
    $tabs[] = $row[0];
}

// now, fix tables
foreach ($tabs as $tab)
{
    $res = mysql_query("show index from {$tab}");
    MysqlError();
    $indicies = array();

    while (($row = mysql_fetch_array($res)) != null)
    {
        if ($row[2] != "PRIMARY")
        {
            $indicies[] = array("name" => $row[2], "unique" => !($row[1] == "1"), "col" => $row[4]);
            mysql_query("ALTER TABLE {$tab} DROP INDEX {$row[2]}");
            MysqlError();
            echo "Dropped index {$row[2]}. Unique: {$row[1]}\n";
        }
    }

    $res = mysql_query("DESCRIBE {$tab}");
    MysqlError();
    while (($row = mysql_fetch_array($res)) != null)
    {
        $name = $row[0];
        $type = $row[1];
        $set = false;
        if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat))
        {
            $size = $mat[1];
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARBINARY({$size})");
            MysqlError();
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARCHAR({$size}) CHARACTER SET {$target_charset}");
            MysqlError();
            $set = true;

            echo "Altered field {$name} on {$tab} from type {$type}\n";
        }
        else if (!strcasecmp($type, "CHAR"))
        {
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} BINARY(1)");
            MysqlError();
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARCHAR(1) CHARACTER SET {$target_charset}");
            MysqlError();
            $set = true;

            echo "Altered field {$name} on {$tab} from type {$type}\n";
        }
        else if (!strcasecmp($type, "TINYTEXT"))
        {
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} TINYBLOB");
            MysqlError();
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} TINYTEXT CHARACTER SET {$target_charset}");
            MysqlError();
            $set = true;

            echo "Altered field {$name} on {$tab} from type {$type}\n";
        }
        else if (!strcasecmp($type, "MEDIUMTEXT"))
        {
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMBLOB");
            MysqlError();
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMTEXT CHARACTER SET {$target_charset}");
            MysqlError();
            $set = true;

            echo "Altered field {$name} on {$tab} from type {$type}\n";
        }
        else if (!strcasecmp($type, "LONGTEXT"))
        {
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} LONGBLOB");
            MysqlError();
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} LONGTEXT CHARACTER SET {$target_charset}");
            MysqlError();
            $set = true;

            echo "Altered field {$name} on {$tab} from type {$type}\n";
        }
        else if (!strcasecmp($type, "TEXT"))
        {
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} BLOB");
            MysqlError();
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} TEXT CHARACTER SET {$target_charset}");
            MysqlError();
            $set = true;

            echo "Altered field {$name} on {$tab} from type {$type}\n";
        }

        if ($set)
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} COLLATE {$target_collate}");
    }

    // re-build indicies..
    foreach ($indicies as $index)
    {
        if ($index["unique"])
        {
            mysql_query("CREATE UNIQUE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
            MysqlError();
        }
        else
        {
            mysql_query("CREATE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
            MysqlError();
        }

        echo "Created index {$index["name"]} on {$tab}. Unique: {$index["unique"]}\n";
    }

    // set default collate
    mysql_query("ALTER TABLE {$tab}  DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
}

// set database charset
mysql_query("ALTER DATABASE {$db} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");

mysql_close($conn);
echo "</pre>";

?>

Answer #12

Pour les utilisateurs Windows

En plus de la réponse @davidwinterbottom, les utilisateurs de Windows peuvent utiliser la commande ci-dessous:

mysql.exe --database=[database] -u [user] -p[password] -B -N -e "SHOW TABLES" \
| awk.exe '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql.exe -u [user] -p[password] --database=[database] &

Remplacez les espaces réservés [base de données], [utilisateur] et [mot de passe] par des valeurs réelles.

Les utilisateurs de Git-bash peuvent télécharger ce script bash et l'exécuter facilement.


Answer #13

Une autre approche utilisant la ligne de commande, basée sur @ david sans l' awk

for t in $(mysql --user=root --password=admin  --database=DBNAME -e "show tables";);do echo "Altering" $t;mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";done

justifié

  for t in $(mysql --user=root --password=admin  --database=DBNAME -e "show tables";);
    do 
       echo "Altering" $t;
       mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
    done




collation