una tabla que optimizar lentas intercalacion datos crear cotejamiento consultas cambiar mysql sql

tabla - optimizar consultas lentas mysql



¿Cómo convertir todas las tablas de la base de datos a una intercalación? (8)

A continuación se muestra la consulta más precisa. Estoy dando ejemplo de cómo convertirlo a 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"

Estoy recibiendo error:

Mezcla ilegal de colaciones (utf8_general_ci, IMPLICIT) y (utf8_unicode_ci, IMPLICIT) para la operación ''='' "

Intenté cambiar ambas tablas manualmente a utf8_general_ci,IMPLICIT pero sigo recibiendo el error.

¿Hay una manera de convertir todas las tablas a utf8_general_ci,IMPLICIT y terminar con eso?


Debe ejecutar una sentencia de modificación de tabla para cada tabla. La declaración seguiría este formulario:

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

Ahora para obtener todas las tablas de la base de datos, deberá ejecutar la siguiente consulta:

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

Así que ahora deja que MySQL escriba el código para ti:

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

Puedes copiar los resultados y ejecutarlos. No he probado la sintaxis, pero deberías poder averiguar el resto. Piensa en ello como un pequeño ejercicio.

¡Espero que ayude!


Esta es mi versión de un script de bash. Toma el nombre de la base de datos como un parámetro y convierte todas las tablas en otro conjunto de caracteres y compilación (dado por otros parámetros o valor predeterminado definido en el script).

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


La sugerencia de @ Namphibian me ayudó mucho ...
Sin embargo, fue un poco más lejos y agregó columnas y vistas al script.

simplemente ingrese el nombre de su esquema a continuación y hará el resto

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


Llevando la respuesta de @Petr Stastny un paso más allá, agregando una variable de contraseña. Preferiría que en realidad lo tomara como una contraseña normal en lugar de como un argumento, pero está funcionando para lo que necesitaba.

#!/bin/bash # mycollate.sh <database> <password> [<charset> <collation>] # changes MySQL/MariaDB charset and collation for one database - all tables and # all columns in all tables DB="$1" PW="$2" CHARSET="$3" COLL="$4" [ -n "$DB" ] || exit 1 [ -n "$PW" ] [ -n "$CHARSET" ] || CHARSET="utf8mb4" [ -n "$COLL" ] || COLL="utf8mb4_bin" PW="--password=""$PW" echo $DB echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql -u root "$PW" echo "USE $DB; SHOW TABLES;" | mysql -s "$PW" | ( while read TABLE; do echo $DB.$TABLE echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql "$PW" $DB done ) PW="pleaseEmptyMeNow"


Mejor opción para cambiar también la intercalación de columnas varchar dentro de la tabla también

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"

Además, si tiene datos con la clave delantera en la columna no utf8 antes de iniciar el uso del script agrupado

SET foreign_key_checks = 0;

Significa que el SQL global será para 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;

Pero tenga cuidado si de acuerdo con la documentación de mysql http://dev.mysql.com/doc/refman/5.1/en/charset-column.html ,

Si usa ALTER TABLE para convertir una columna de un conjunto de caracteres a otro, MySQL intenta asignar los valores de los datos, pero si los conjuntos de caracteres son incompatibles, puede haber pérdida de datos. "

EDITAR: Especialmente con el tipo de columna enum, simplemente se bloquea completamente enumeración conjunto (incluso si no hay caracteres especiales) https://bugs.mysql.com/bug.php?id=26731


Puedes usar este script BASH:

#!/bin/bash USER="YOUR_DATABASE_USER" PASSWORD="YOUR_USER_PASSWORD" DB_NAME="DATABASE_NAME" CHARACTER_SET="utf8" # your default character set COLLATE="utf8_general_ci" # your default collation tables=`mysql -u $USER -p$PASSWORD -e "SELECT tbl.TABLE_NAME FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = ''$DB_NAME'' AND tbl.TABLE_TYPE=''BASE TABLE''"` for tableName in $tables; do if [[ "$tableName" != "TABLE_NAME" ]] ; then mysql -u $USER -p$PASSWORD -e "ALTER TABLE $DB_NAME.$tableName DEFAULT CHARACTER SET $CHARACTER_SET COLLATE $COLLATE;" echo "$tableName - done" fi done


Si quieres un script bash para copiar y pegar:

var=$(mysql -e ''SELECT CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_czech_ci;") AS execTabs FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="zabbix" AND TABLE_TYPE="BASE TABLE"'' -uroot -p ) var+=''ALTER DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_general_ci;'' echo $var | cut -d " " -f2- | mysql -uroot -p zabbix

Cambie zabbix al nombre de su base de datos.