php - que - mostrar registros de una tabla mysql

Una secuencia de comandos para cambiar todas las tablas y campos a la intercalación de utf-8-bin en MYSQL

Question

¿Existe un script SQL o PHP que pueda ejecutar que cambie la intercalación predeterminada en todas las tablas y campos en una base de datos?

Puedo escribir uno yo mismo, pero creo que esto debería ser algo fácilmente disponible en un sitio como este. Si puedo encontrar uno antes de que alguien publique uno, lo publicaré yo mismo.

Answer 1

¡Ten cuidado! Si realmente tienes utf almacenado como otra codificación, podrías tener un verdadero lío en tus manos. Copia de seguridad primero. Luego prueba algunos de los métodos estándar:

por ejemplo, http://www.cesspit.net/drupal/node/898 http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html

Tuve que recurrir a la conversión de todos los campos de texto a binarios, luego de vuelta a varchar / text. Esto me ha salvado el culo.

Tenía datos en UTF8, almacenados como latin1. Lo que hice:

Soltar índices Convierte campos a binario. Convertir a utf8-general ci

Si está en LAMP, no olvide agregar el comando set NAMES antes de interactuar con el db, y asegúrese de configurar los encabezados de codificación de caracteres.

Answer 2

Charset y colación no son lo mismo. Una intercalación es un conjunto de reglas sobre cómo ordenar cadenas. Un juego de caracteres es un conjunto de reglas sobre cómo representar personajes. Una intercalación depende del juego de caracteres.

Answer 3

Este fragmento de PHP cambiará la intercalación en todas las tablas en un db. (Se toma de este sitio )

<?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 4

Una versión más completa del script anterior se puede encontrar aquí:

http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=1937

Por favor, deje un comentario sobre esta contribución aquí: http://www.zen-cart.com/forum/showthread.php?p=1034214

Answer 5

En scripts sobre todas las tablas seleccionadas para convertation (con SHOW TABLES ), pero una manera más conveniente y portátil de verificar la intercalación de tabla antes de convertir una tabla. Esta consulta lo hace:

SELECT table_name
, table_collation
FROM information_schema.tables

Answer 6

Otro enfoque usando línea de comando, basado en @ david sin el 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

embellecido

 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

Answer 7

Se puede hacer en un solo comando (en lugar de 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 &

Tienes que amar la línea de comandos ... (Es posible que necesites emplear las opciones --user y --password para mysql ).

EDITAR: para evitar problemas de clave externa, se agregó SET foreign_key_checks = 0; y SET foreign_key_checks = 1;

Answer 8

Usar mi shell personalizado, debe funcionar:

collatedb <username> <password> <database> <collation>

Ejemplo:

collatedb root 0000 myDatabase utf8_bin

Answer 9

Creo que es fácil hacer esto en dos pasos con PhpMyAdmin.
Paso 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

Paso 2:
Esta consulta generará una lista de consultas, una para cada tabla. Debe copiar la lista de consultas y pegarlas en la línea de comando o en la pestaña SQL de PhpMyAdmin para que se realicen los cambios.

Answer 10

Gracias @nlaq por el código, eso me ayudó a comenzar con la siguiente solución.

Lancé un plugin de WordPress sin darme cuenta de que WordPress no establece el cotejo automáticamente. Así que muchas personas que usan el complemento terminaron con latin1_swedish_ci cuando debería haber sido utf8_general_ci .

Aquí está el código que agregué al complemento para detectar el cotejo latin1_swedish_ci y cambiarlo a utf8_general_ci .

¡Pruebe este código antes de usarlo en su propio complemento!

// list the names of your wordpress plugin database tables (without db prefix)
$tables_to_check = array(
''social_message'',
''social_facebook'',
''social_facebook_message'',
''social_facebook_page'',
''social_google'',
''social_google_mesage'',
''social_twitter'',
''social_twitter_message'',
);
// choose the collate to search for and replace:
$convert_fields_collate_from = ''latin1_swedish_ci'';
$convert_fields_collate_to = ''utf8_general_ci'';
$convert_tables_character_set_to = ''utf8'';
$show_debug_messages = false;
global $wpdb;
$wpdb->show_errors();
foreach($tables_to_check as $table) {
$table = $wpdb->prefix . $table;
$indicies = $wpdb->get_results( "SHOW INDEX FROM `$table`", ARRAY_A );
$results = $wpdb->get_results( "SHOW FULL COLUMNS FROM `$table`" , ARRAY_A );
foreach($results as $result){
if($show_debug_messages)echo "Checking field ".$result[''Field''] ." with collat: ".$result[''Collation'']."/n";
if(isset($result[''Field'']) && $result[''Field''] && isset($result[''Collation'']) && $result[''Collation''] == $convert_fields_collate_from){
if($show_debug_messages)echo "Table: $table - Converting field " .$result[''Field''] ." - " .$result[''Type'']." - from $convert_fields_collate_from to $convert_fields_collate_to /n";
// found a field to convert. check if there''s an index on this field.
// we have to remove index before converting field to binary.
$is_there_an_index = false;
foreach($indicies as $index){
if ( isset($index[''Column_name'']) && $index[''Column_name''] == $result[''Field'']){
// there''s an index on this column! store it for adding later on.
$is_there_an_index = $index;
$wpdb->query( $wpdb->prepare( "ALTER TABLE `%s` DROP INDEX %s", $table, $index[''Key_name'']) );
if($show_debug_messages)echo "Dropped index ".$index[''Key_name'']." before converting field.. /n";
break;
}
}
$set = false;
if ( preg_match( "/^varchar/((/d+)/)$/i", $result[''Type''], $mat ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` VARBINARY({$mat[1]})" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` VARCHAR({$mat[1]}) CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result[''Type''], "CHAR" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` BINARY(1)" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` VARCHAR(1) CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result[''Type''], "TINYTEXT" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` TINYBLOB" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` TINYTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result[''Type''], "MEDIUMTEXT" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` MEDIUMBLOB" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` MEDIUMTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result[''Type''], "LONGTEXT" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` LONGBLOB" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` LONGTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result[''Type''], "TEXT" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` BLOB" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result[''Field'']}` TEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
}else{
if($show_debug_messages)echo "Failed to change field - unsupported type: ".$result[''Type'']."/n";
}
if($set){
if($show_debug_messages)echo "Altered field success! /n";
$wpdb->query( "ALTER TABLE `$table` MODIFY {$result[''Field'']} COLLATE $convert_fields_collate_to" );
}
if($is_there_an_index !== false){
// add the index back.
if ( !$is_there_an_index["Non_unique"] ) {
$wpdb->query( "CREATE UNIQUE INDEX `{$is_there_an_index[''Key_name'']}` ON `{$table}` ({$is_there_an_index[''Column_name'']})", $is_there_an_index[''Key_name''], $table, $is_there_an_index[''Column_name''] );
} else {
$wpdb->query( "CREATE UNIQUE INDEX `{$is_there_an_index[''Key_name'']}` ON `{$table}` ({$is_there_an_index[''Column_name'']})", $is_there_an_index[''Key_name''], $table, $is_there_an_index[''Column_name''] );
}
}
}
}
// set default collate
$wpdb->query( "ALTER TABLE `{$table}` DEFAULT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
if($show_debug_messages)echo "Finished with table $table /n";
}
$wpdb->hide_errors();

Answer 11

Una solución simple (¿tonta? :), usando la función de selección múltiple de su IDE:

1. ejecutar "MOSTRAR TABLAS"; columna de consulta y copia de resultados (nombres de tablas).
2. inicios de selección múltiple y agregue "ALTER TABLE".
3. selecciones multiselección y agregue "CONVERTIR AL CONJUNTO DE CARACTERES utf8 COLLATE utf8_general_ci;"
4. ejecutar consultas creadas.

Answer 12

Esta es una forma sencilla de hacerlo con solo phpmyadmin si no tiene acceso a la línea de comando o acceso para editar INFORMATION_SCHEMA.

En primer lugar, escucha el consejo de muchas de las otras respuestas aquí: puedes arruinar las cosas aquí, así que haz una copia de seguridad. Ahora haga una copia de seguridad de su copia de seguridad. Además, es poco probable que esto funcione si sus datos están codificados de manera diferente a lo que lo está cambiando.

Tenga en cuenta que necesitará encontrar los nombres exactos del esquema ofensivo y la codificación de caracteres que necesita cambiar antes de comenzar.

1. Exportar la base de datos como SQL; Hacer una copia; Ábralo en un editor de texto de su elección
2. Encuentre y reemplace el esquema primero, por ejemplo - find: latin1_swedish_ci , replace: utf8_general_ci
3. Encuentre y reemplace las codificaciones de caracteres si lo necesita, por ejemplo - find: latin1 , replace: utf8
4. Cree una nueva base de datos de prueba y cargue su nuevo archivo SQL en phpmyadmin

Esta es una forma muy sencilla de hacerlo, pero una vez más, esto no cambiará la codificación de sus datos, por lo que solo funcionará en determinadas circunstancias.

Answer 13

Creo que la manera más rápida es con phpmyadmin y algo de jQuery en la consola.

Vaya a la estructura de la tabla y abra la consola de desarrollador de Chrome / Firefox (normalmente F12 en el teclado):

1. ejecuta este código para seleccionar todos los campos con juego de caracteres incorrecto y comienza a modificar:

   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. cuando la página está cargada, use este código en la consola para seleccionar la codificación correcta:

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

3. salvar

4. cambie el juego de caracteres de la tabla en el campo "Colación" en la pestaña "Operación"

Probado en phpmyadmin 4.0 y 4.4, pero creo que funciona en todas las versiones 4.x

Answer 14

OK, escribí esto teniendo en cuenta lo que se dijo en este hilo. Gracias por la ayuda, y espero que este script ayude a otros. No tengo ninguna garantía para su uso, así que FAVOR DE RESPALDAR antes de ejecutarlo. Debería funcionar con todas las bases de datos; y funcionó muy bien por mi cuenta.

EDITAR: Se agregaron vars en la parte superior para los que se convertirá el conjunto de caracteres / cotejo. EDIT2: Cambia el juego de caracteres / compilación predeterminado de la base de datos y las tablas

<?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 15

Actualicé la respuesta de nlaq para trabajar con PHP7 y para manejar correctamente índices de latin1_bin , datos intercalados binarios (por ejemplo, latin1_bin ), etc., y latin1_bin un poco el código. Este es el único código que encontré / intenté que migró mi base de datos de latin1 a 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 16

Para usuarios de Windows

Además de la respuesta de @davidwinterbottom, los usuarios de Windows pueden usar el siguiente comando:

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] &

Reemplace marcadores de posición [base de datos], [usuario] y [contraseña] con valores reales.

Los usuarios de Git-bash pueden descargar este script bash y ejecutarlo fácilmente.