convert - utf8_decode mysql

Detectando caracteres rotos de utf8 en MySQL

Question

Tengo una base de datos con un montón de caracteres utf8 rotos dispersos en varias tablas. La lista de caracteres no es muy extensa AFAIK (áéíúóÁÉÍÓÚÑñ)

Reparar una tabla dada es muy sencillo

update orderItem set itemName=replace(itemName,''á'',''á'');

Pero no puedo encontrar una manera de detectar los caracteres rotos. Si hago algo como

SELECT * FROM TABLE WHERE field LIKE "%Ã%";

Obtengo casi todos los campos debido a la intercalación (Ã = a). Todos los personajes rotos hasta el momento comienzan con un "Ã". La base de datos está en español, por lo que este personaje en particular no se usa

La lista de caracteres rotos que tengo hasta ahora es

á = á
é = é
í- = í
ó = ó
ñ = ñ
á = Á

¿Alguna idea de cómo hacer que este SELECCIONAR funcione según lo previsto? (una búsqueda binaria o algo así)

Answer 1

¡¡Gracias por tus respuestas!!

Arreglé mis tablas con esto, y quería compartir la lista completa de cambios. Tenga en cuenta que también incluye la fijación de caracteres decodificados html, además de los latinos, fue realmente un desastre:

update `table` set `field` = replace(`field` ,''É'',''É'');
update `table` set `field` = replace(`field` ,''“'',''"'');
update `table` set `field` = replace(`field` ,''â€'',''"'');
update `table` set `field` = replace(`field` ,''Ç'',''Ç'');
update `table` set `field` = replace(`field` ,''Ã'',''Ã'');
//Edit by slash4
update `table` set `field` = replace(`field` ,''Ã '',''À'');
update `table` set `field` = replace(`field` ,''ú'',''ú'');
update `table` set `field` = replace(`field` ,''•'',''-'');
update `table` set `field` = replace(`field` ,''Ø'',''Ø'');
update `table` set `field` = replace(`field` ,''õ'',''õ'');
-- The next one  appears to be missing a character. But which one?
update `table` set `field` = replace(`field` ,''í'',''í'');
update `table` set `field` = replace(`field` ,''â'',''â'');
update `table` set `field` = replace(`field` ,''ã'',''ã'');
update `table` set `field` = replace(`field` ,''ê'',''ê'');
update `table` set `field` = replace(`field` ,''á'',''á'');
update `table` set `field` = replace(`field` ,''é'',''é'');
update `table` set `field` = replace(`field` ,''ó'',''ó'');
update `table` set `field` = replace(`field` ,''–'',''–'');
update `table` set `field` = replace(`field` ,''ç'',''ç'');
update `table` set `field` = replace(`field` ,''ª'',''ª'');
update `table` set `field` = replace(`field` ,''º'',''º'');
update `table` set `field` = replace(`field` ,''à'',''à'');
update `table` set `field` = replace(`field` ,''ç'',''ç'');
update `table` set `field` = replace(`field` ,''ã'',''ã'');
update `table` set `field` = replace(`field` ,''á'',''á'');
update `table` set `field` = replace(`field` ,''â'',''â'');
update `table` set `field` = replace(`field` ,''é'',''é'');
update `table` set `field` = replace(`field` ,''í'',''í'');
update `table` set `field` = replace(`field` ,''õ'',''õ'');
update `table` set `field` = replace(`field` ,''ú'',''ú'');
update `table` set `field` = replace(`field` ,''ç'',''ç'');
update `table` set `field` = replace(`field` ,''Á'',''Á'');
update `table` set `field` = replace(`field` ,''Â'',''Â'');
update `table` set `field` = replace(`field` ,''É'',''É'');
update `table` set `field` = replace(`field` ,''Í'',''Í'');
update `table` set `field` = replace(`field` ,''Õ'',''Õ'');
update `table` set `field` = replace(`field` ,''Ú'',''Ú'');
update `table` set `field` = replace(`field` ,''Ç'',''Ç'');
update `table` set `field` = replace(`field` ,''Ã'',''Ã'');
update `table` set `field` = replace(`field` ,''À'',''À'');
update `table` set `field` = replace(`field` ,''Ê'',''Ê'');
update `table` set `field` = replace(`field` ,''Ó'',''Ó'');
update `table` set `field` = replace(`field` ,''Ô'',''Ô'');
update `table` set `field` = replace(`field` ,''Ü'',''Ü'');
update `table` set `field` = replace(`field` ,''ã'',''ã'');
update `table` set `field` = replace(`field` ,''à'',''à'');
update `table` set `field` = replace(`field` ,''ê'',''ê'');
update `table` set `field` = replace(`field` ,''ó'',''ó'');
update `table` set `field` = replace(`field` ,''ô'',''ô'');
update `table` set `field` = replace(`field` ,''ü'',''ü'');
update `table` set `field` = replace(`field` ,''&'',''&'');
update `table` set `field` = replace(`field` ,''>'',''>'');
update `table` set `field` = replace(`field` ,''&lt;'',''<'');
update `table` set `field` = replace(`field` ,''&circ;'',''ˆ'');
update `table` set `field` = replace(`field` ,''&tilde;'',''˜'');
update `table` set `field` = replace(`field` ,''&uml;'',''¨'');
update `table` set `field` = replace(`field` ,''&cute;'',''´'');
update `table` set `field` = replace(`field` ,''&cedil;'',''¸'');
update `table` set `field` = replace(`field` ,''&quot;'',''"'');
update `table` set `field` = replace(`field` ,''&ldquo;'',''“'');
update `table` set `field` = replace(`field` ,''&rdquo;'',''”'');
update `table` set `field` = replace(`field` ,''&lsquo;'',''‘'');
update `table` set `field` = replace(`field` ,''&rsquo;'',''’'');
update `table` set `field` = replace(`field` ,''&lsaquo;'',''‹'');
update `table` set `field` = replace(`field` ,''&rsaquo;'',''›'');
update `table` set `field` = replace(`field` ,''&laquo;'',''«'');
update `table` set `field` = replace(`field` ,''&raquo;'',''»'');
update `table` set `field` = replace(`field` ,''&ordm;'',''º'');
update `table` set `field` = replace(`field` ,''&ordf;'',''ª'');
update `table` set `field` = replace(`field` ,''&ndash;'',''–'');
update `table` set `field` = replace(`field` ,''&mdash;'',''—'');
update `table` set `field` = replace(`field` ,''&macr;'',''¯'');
update `table` set `field` = replace(`field` ,''&hellip;'',''…'');
update `table` set `field` = replace(`field` ,''&brvbar;'',''¦'');
update `table` set `field` = replace(`field` ,''&bull;'',''•'');
update `table` set `field` = replace(`field` ,''&para;'',''¶'');
update `table` set `field` = replace(`field` ,''&sect;'',''§'');
update `table` set `field` = replace(`field` ,''&sup1;'',''¹'');
update `table` set `field` = replace(`field` ,''&sup2;'',''²'');
update `table` set `field` = replace(`field` ,''&sup3;'',''³'');
update `table` set `field` = replace(`field` ,''&frac12;'',''½'');
update `table` set `field` = replace(`field` ,''&frac14;'',''¼'');
update `table` set `field` = replace(`field` ,''&frac34;'',''¾'');
update `table` set `field` = replace(`field` ,''&#8539;'',''⅛'');
update `table` set `field` = replace(`field` ,''&#8540;'',''⅜'');
update `table` set `field` = replace(`field` ,''&#8541;'',''⅝'');
update `table` set `field` = replace(`field` ,''&#8542;'',''⅞'');
update `table` set `field` = replace(`field` ,''&gt;'',''>'');
update `table` set `field` = replace(`field` ,''&lt;'',''<'');
update `table` set `field` = replace(`field` ,''&plusmn;'',''±'');
update `table` set `field` = replace(`field` ,''&minus;'',''−'');
update `table` set `field` = replace(`field` ,''&times;'',''×'');
update `table` set `field` = replace(`field` ,''&divide;'',''÷'');
update `table` set `field` = replace(`field` ,''&lowast;'',''∗'');
update `table` set `field` = replace(`field` ,''&frasl;'',''⁄'');
update `table` set `field` = replace(`field` ,''&permil;'',''‰'');
update `table` set `field` = replace(`field` ,''&int;'',''∫'');
update `table` set `field` = replace(`field` ,''&sum;'',''∑'');
update `table` set `field` = replace(`field` ,''&prod;'',''∏'');
update `table` set `field` = replace(`field` ,''&radic;'',''√'');
update `table` set `field` = replace(`field` ,''&infin;'',''∞'');
update `table` set `field` = replace(`field` ,''&asymp;'',''≈'');
update `table` set `field` = replace(`field` ,''&cong;'',''≅'');
update `table` set `field` = replace(`field` ,''&prop;'',''∝'');
update `table` set `field` = replace(`field` ,''&equiv;'',''≡'');
update `table` set `field` = replace(`field` ,''&ne;'',''≠'');
update `table` set `field` = replace(`field` ,''&le;'',''≤'');
update `table` set `field` = replace(`field` ,''&ge;'',''≥'');
update `table` set `field` = replace(`field` ,''&there4;'',''∴'');
update `table` set `field` = replace(`field` ,''&sdot;'',''⋅'');
update `table` set `field` = replace(`field` ,''&middot;'',''·'');
update `table` set `field` = replace(`field` ,''&part;'',''∂'');
update `table` set `field` = replace(`field` ,''&image;'',''ℑ'');
update `table` set `field` = replace(`field` ,''&real;'',''ℜ'');
update `table` set `field` = replace(`field` ,''&prime;'',''′'');
update `table` set `field` = replace(`field` ,''&Prime;'',''″'');
update `table` set `field` = replace(`field` ,''&deg;'',''°'');
update `table` set `field` = replace(`field` ,''&ang;'',''∠'');
update `table` set `field` = replace(`field` ,''&perp;'',''⊥'');
update `table` set `field` = replace(`field` ,''&nabla;'',''∇'');
update `table` set `field` = replace(`field` ,''&oplus;'',''⊕'');
update `table` set `field` = replace(`field` ,''&otimes;'',''⊗'');
update `table` set `field` = replace(`field` ,''&alefsym;'',''ℵ'');
update `table` set `field` = replace(`field` ,''&oslash;'',''ø'');
update `table` set `field` = replace(`field` ,''&Oslash;'',''Ø'');
update `table` set `field` = replace(`field` ,''&isin;'',''∈'');
update `table` set `field` = replace(`field` ,''&notin;'',''∉'');
update `table` set `field` = replace(`field` ,''&cap;'',''∩'');
update `table` set `field` = replace(`field` ,''&cup;'',''∪'');
update `table` set `field` = replace(`field` ,''&sub;'',''⊂'');
update `table` set `field` = replace(`field` ,''&sup;'',''⊃'');
update `table` set `field` = replace(`field` ,''&sube;'',''⊆'');
update `table` set `field` = replace(`field` ,''&supe;'',''⊇'');
update `table` set `field` = replace(`field` ,''&exist;'',''∃'');
update `table` set `field` = replace(`field` ,''&forall;'',''∀'');
update `table` set `field` = replace(`field` ,''&empty;'',''∅'');
update `table` set `field` = replace(`field` ,''&not;'',''¬'');
update `table` set `field` = replace(`field` ,''&and;'',''∧'');
update `table` set `field` = replace(`field` ,''&or;'',''∨'');
update `table` set `field` = replace(`field` ,''&crarr;'',''↵'');

Answer 2

¿Qué tal un enfoque diferente, es decir, convertir la columna de un lado a otro para obtener el conjunto de caracteres correcto? Puede convertirlo a binario, luego a utf-8 y luego a iso-8859-1 o lo que sea que esté usando. Ver el manual para los detalles.

Answer 3

Además de Raúl Avila Solano y la respuesta de Acseven, si desea actualizar todos los caracteres rotos en una consulta , puede hacer:

update `table` set field = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(field,''ü'',''ü''),''ô'',''ô''),''ó'',''ó''),''ê'',''ê''),''à'',''à''),''ã'',''ã''),''Ü'',''Ü''),''Ô'',''Ô''),''Ó'',''Ó''),''Ê'',''Ê''),''À'',''À''),''Ã'',''Ã''),''Ç'',''Ç''),''Ú'',''Ú''),''Õ'',''Õ''),''Í'',''Í''),''Í'',''Í''),''É'',''É''),''Â'',''Â''),''Á'',''Á''),''ç'',''ç''),''ú'',''ú''),''õ'',''õ''),''í'',''í''),''é'',''é''),''â'',''â''),''á'',''á''),''ã'',''ã''),''ç'',''ç''),''à '',''à''),''à '',''à''),''º'',''º''),''ª'',''ª''),''ç'',''ç''),''–'',''–''),''ó'',''ó''),''é'',''é''),''á'',''á''),''ê'',''ê''),''ã'',''ã''),''â'',''â''),''í'',''í''),''õ'',''õ''),''Ø'',''Ø''),''•'',''-''),''ú'',''ú''),''à '',''À''),''Ã'',''Ã''),''Ç'',''Ç''),''â€'',''"''),''“'',''"''),''É'',''É'');

Answer 4

El paso intermedio puede no haber sido necesario ya que TEXT y BLOB son iguales.

Esto tuvo el efecto de corregir todos los datos codificados incorrectamente, pero los datos que antes eran correctos se truncaron en el primer carácter multibyte.

Answer 5

Es posible que tenga filas con UTF8 codificado correctamente y con caracteres codificados incorrectamente. En este caso, "CONVERT (BINARY CONVERT (post_title USING latin1) USING utf8)" recortará algunos campos.

Terminé haciéndolo de esta manera

update `table` set `name` = replace(`name` ,CONVERT(BINARY "ä" USING latin1),''ä'');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "ö" USING latin1),''ö'');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "ü" USING latin1),''ü'');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "Ä" USING latin1),''Ä'');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "Ö" USING latin1),''Ö'');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "Ü" USING latin1),''Ü'');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "ß" USING latin1),''ß'');

Answer 6

Esta es una extensión de la respuesta de @Thales Ceolin para modificar cada tabla en el archivo db:

select concat(
    "update ", 
    a.TABLE_NAME, 
    " set ", b.COLUMN_NAME, 
    " = CONVERT(BINARY CONVERT(", 
    b.COLUMN_NAME, 
    " USING latin1) USING utf8) where ",
    b.COLUMN_NAME, 
    " is not null;") query
from INFORMATION_SCHEMA.TABLES a
left join INFORMATION_SCHEMA.COLUMNS b on a.TABLE_NAME = b.TABLE_NAME
where a.table_schema = ''db_name''
and a.TABLE_TYPE = ''BASE TABLE''
and b.data_type in (''text'', ''varchar'')
and a.TABLE_NAME = ''table_name'';

Esto dará como resultado:

update table_name set idn = CONVERT(BINARY CONVERT(idn USING latin1) USING utf8) where idn is not null;
update table_nameset name = CONVERT(BINARY CONVERT(name USING latin1) USING utf8) where name is not null;
update table_name set primary_last_name = CONVERT(BINARY CONVERT(primary_last_name USING latin1) USING utf8) where primary_last_name is not null;

Answer 7

Esto me salvó la vida

UPDATE ohp_posts SET post_content = CONVERT(CAST(CONVERT(post_content USING latin1) AS BINARY) USING utf8)

Lo he encontrado aquí http://stanis.net/2014/04/replacing-latin-1-with-utf-8-characters-in-mysql/

Answer 8

Hay una buena secuencia de comandos para automatizar el proceso de conversión en una base de datos completa. También es útil saber que la implementación UTF-8 de MySQL es incompleta ya que solo admite caracteres UTF-8 de hasta 3 bytes. La solución es usar el juego de caracteres utf8mb4 introducido en MySQL 5.5.3.

Answer 9

La instrucción SELECT que necesita es la siguiente:

SELECT * FROM TABLE WHERE LENGTH(name) != CHAR_LENGTH(name);

Esto devuelve todas las filas que contienen caracteres de varios bytes.

name se supone que es un campo / el campo donde se encontrarían personajes extraños. *

Answer 10

Lo arreglé con

UPDATE wp_zcs9ck_posts_copy SET post_title = 
    CONVERT(BINARY CONVERT(post_title USING latin1) USING utf8);

Solución completa: http://jonisalonen.com/2012/fixing-doubly-utf-8-encoded-text-in-mysql/

Answer 11

Ningún reemplazo de texto es una solución universal porque puedes olvidarte de algún personaje. Una solución más adecuada para los caracteres convertidos dobles es:

1. volver a convertir a latin1
2. convertir a binario
3. convertir a utf8

Me gusta esto:

alter table descriptions modify name VARCHAR(2000) character set latin1;
alter table descriptions modify name blob;
alter table descriptions modify name VARCHAR(2000) character set utf8;

Answer 12

Tuve el mismo problema pero no me gustó la solución de reemplazo () porque siempre existe la posibilidad de perder algunos caracteres. Estaba trabajando contra una columna con datos mixtos (algunos habían sido utf8_encode () d y otros no) con 4 millones o más filas, aproximadamente 250k registros con datos mal codificados (con caracteres à ‰ / etc), que abarcaban unos 15 idiomas internacionales , incluidos principalmente los idiomas europeos, pero también ruso, japonés y chino.

Empecé duplicando la columna, ya que no quería perder ningún dato:

ALTER TABLE images ADD COLUMN reptitle TEXT;

Copió todos los datos con caracteres multibyte (gracias a Adam por la sugerencia)

UPDATE images SET reptitle = title WHERE LENGTH(title) != CHAR_LENGTH(title)

Dado que reptitle se creó con el conjunto de caracteres predeterminado de la tabla, ya era utf8, pero contenía los datos dañados ya que la tabla de imágenes solía ser una fuente latina. Columna reptitle ahora contiene algunos datos que están codificados correctamente y algunos corruptos (todos los valores con caracteres multibyte, algunos han sido correctamente utf8_encode () d. Entonces, con la sugerencia de David ...

ALTER TABLE images MODIFY reptitle TEXT character set latin1;
ALTER TABLE images MODIFY reptitle BLOB;
ALTER TABLE images MODIFY reptitle TEXT character set utf8;

El paso intermedio puede no haber sido necesario ya que TEXT y BLOB (creo) son lo mismo. Esto tuvo el efecto de corregir todos los datos incorrectamente codificados ("Éntudiantes" se convirtieron en "étudiantes", etc.), pero los datos que antes eran correctos se truncaron en el primer carácter multibyte ("Lapin de Pâques" se convirtió en "Lapin de P"). No sé por qué el truncamiento, pero está en una columna desechable, así que no me importó. Los datos truncados dan CHAR_LENGTH y LENGTH de los mismos valores porque no quedan caracteres de varios bytes que sean tan fáciles de consultar ...

UPDATE images SET title = reptitle WHERE LENGTH(reptitle)!=CHAR_LENGTH(reptitle)

Entonces, por supuesto, solo deja caer la columna de repuesto

ALTER TABLE images DROP COLUMN reptitle

También asegúrate (ya que uso PHP y esto me ha hecho tropezar un par de veces, así que pensé que lo mencionaría aquí) todos tus archivos de script son UTF8 (sin BOM) y estás usando:

mysql_set_charset(''utf8'', $connection);

Et voilà ... datos perfectamente reparados, todos los idiomas :)

Answer 13

esto también solucionó mi problema en algunos caracteres italianos

UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''á'',''á'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''ä'',''ä'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''é'',''é'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''í©'',''é'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''ó'',''ó'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''íº'',''ú'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''ú'',''ú'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''ñ'',''ñ'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''í‘'',''Ñ'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''Ã'',''í'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''–'',''–'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''’'',''/''');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''…'',''...'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''–'',''-'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''“'',''"'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''â€'',''"'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''‘'',''/''');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''•'',''-'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''‡'',''c'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''Â'','''');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''í '',''à'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''í¨'',''è'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''íˆ'',''È'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''€'',''€'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''eÌ€'',''è'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''í²'',''ò'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''í¹'',''ù'');

Answer 14

UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''á'',''á'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''ä'',''ä'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''é'',''é'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''í©'',''é'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''ó'',''ó'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''íº'',''ú'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''ú'',''ú'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''ñ'',''ñ'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''í‘'',''Ñ'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''Ã'',''í'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''–'',''–'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''’'',''/''');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''…'',''...'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''–'',''-'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''“'',''"'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''â€'',''"'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''‘'',''/''');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''•'',''-'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,''‡'',''c'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,''Â'','''');