tabla - optimizar consultas lentas mysql

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

Question

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?

Answer 1

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"

Answer 2

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!

Answer 3

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
)

Answer 4

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

Answer 5

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"

Answer 6

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

Answer 7

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

Answer 8

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.