6

Esta pregunta nace en base ha esta otra Mi base de datos no estaba utf-8 , como arreglar las tildes,una vez ya insertada PHP [duplicada] marcada como duplicada por una posible respuesta aquí, ¿Por que el Carácter inspector (�) aparece en algunos datos obtenidos de la Base de Datos?

Es cierto que en proyectos nuevos no existe inconveninte alguno en reacer la base de datos con las codificaciones de acaracteres adecuadas si no lo establecimos correctamente en un principio, pues las tablas no estan pobladas o contienen pocos registros.

Pero en algunos casos asumimos proyectos con tablas pobladas con miles de registros, tal vez millones, en las que es imprescindible conservar los datos y corregirlos, hacer esto a mano es inviable. Por lo tanto la pregunta va en este sentido.

¿Cómo podemos migrar/arreglar los datos ya intruducidos de una tabla en latin1 con datos introducidos en utf8?

Dejo un ejemplo lo mas básico posible:

--
-- Estructura para la tabla `tabla_latin1` con datos insertados en UTF8
--

DROP TABLE IF EXISTS `tabla_latin1`;
CREATE TABLE IF NOT EXISTS `tabla_latin1` (
  `idTabla` int(11) NOT NULL AUTO_INCREMENT,
  `texto` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`idTabla`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

--
-- Volcado de datos para la tabla `tabla_latin1`
--

INSERT INTO `tabla_latin1` (`idTabla`, `texto`) VALUES
(1, 'Ç'),
(2, 'È'),
(3, 'É'),
(4, 'Ê'),
(5, 'Ë'),
(6, 'Ì'),
(8, 'ÃŽ'),
(11, 'Ñ'),
(12, 'Ã’'),
(13, 'Ó'),
(14, 'Ô'),
(15, 'Õ'),
(16, 'Ö');

--
-- datos correctos: ÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ
--
Xerif
  • 7,283
  • 3
  • 17
  • 41
  • ¿Todos los registros están mal cargado o puede que haya algunos que no? – Marcos Jan 26 '18 at 20:49
  • @Mariano Parece ser que al crear la pregunta se han podido alterar los datos, enconcreto esos que indicas, ahora edito y elimino esos para que el set de datos sea válido en cualquier caso no es mas que un ejemplo. – Xerif Jan 26 '18 at 21:15
  • @Marcos En este caso concreto podemos suponer que todos los registros se cargaron como UTF8. – Xerif Jan 26 '18 at 21:16

1 Answers1

1

Suponiendo que disponemos de una tabla en la que se guardaron registros en UTF8 cuando la tabla y columnas son latin1 por ejemplo, podemos jugar con CAST() y CONVERT() para optener el resultado adecuado.

Por ejemplo para la tabla dada:

--
-- Estructura de tabla para la tabla `tabla_latin1` con datos insertados en UTF8
--

DROP TABLE IF EXISTS `tabla_latin1`;
CREATE TABLE IF NOT EXISTS `tabla_latin1` (
  `idTabla` int(11) NOT NULL AUTO_INCREMENT,
  `texto` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`idTabla`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

--
-- Volcado de datos para la tabla `tabla_latin1`
--

INSERT INTO `tabla_latin1` (`idTabla`, `texto`) VALUES
(1, 'Ç'),
(2, 'È'),
(3, 'É'),
(4, 'Ê'),
(5, 'Ë'),
(6, 'Ì'),
(7, 'Ã'),
(8, 'ÃŽ'),
(9, 'Ã'),
(10, 'Ã'),
(11, 'Ñ'),
(12, 'Ã’'),
(13, 'Ó'),
(14, 'Ô'),
(15, 'Õ'),
(16, 'Ö');

Primero cambiamos la codificación de la tabla:

ALTER TABLE tabla_latin1 CHANGE `texto` `texto` VARCHAR(255) CHARACTER SET utf8mb4;
--Query OK, 16 rows affected (1,29 sec)
--Records: 16  Duplicates: 0  Warnings: 0

Podríamos ejecuar la siguiente consulta, pasando la columna a binario y luego a utf8 (utf8mb4) para revisar los datos:

SELECT CONVERT(cast (CONVERT (texto USING latin1) AS BINARY) USING utf8mb4) texto_en_utf8 FROM tabla_latin1;

Y optedriamos esto:

+---------------+
| texto_en_utf8 |
+---------------+
| Ç             |
| È             |
| É             |
| Ê             |
| Ë             |
| Ì             |
| Í             |
| Î             |
| Ï             |
| Ð             |
| Ñ             |
| Ò             |
| Ó             |
| Ô             |
| Õ             |
| Ö             |
+---------------+
16 rows in set (0,00 sec)

Si todo se ve correcto, solo nos quedaría hacer un UPDATE de la columna aplicando la misma conversión:

UPDATE tabla_latin1 SET texto = CONVERT(cast (CONVERT (texto USING latin1) as binary) USING utf8mb4);
-- Query OK, 16 rows affected (0,06 sec)
-- Rows matched: 16  Changed: 16  Warnings: 0

Y ahora podemos ver el resultado con un select normal:

SELECT * FROM tabla_latin1;
+---------+-------+
| idTabla | texto |
+---------+-------+
|       1 | Ç     |
|       2 | È     |
|       3 | É     |
|       4 | Ê     |
|       5 | Ë     |
|       6 | Ì     |
|       7 | Í     |
|       8 | Î     |
|       9 | Ï     |
|      10 | Ð     |
|      11 | Ñ     |
|      12 | Ò     |
|      13 | Ó     |
|      14 | Ô     |
|      15 | Õ     |
|      16 | Ö     |
+---------+-------+
16 rows in set (0,00 sec)

Por último cambiamos las codificaciones de la tabla:

ALTER TABLE tabla_latin1 COLLATE=utf8mb4_general_ci;
-- Query OK, 0 rows affected (0,07 sec)
-- Records: 0  Duplicates: 0  Warnings: 0
Xerif
  • 7,283
  • 3
  • 17
  • 41
  • 1
    @Mariano Se ha de pensar que la base tabla tiene datos utf8, y que hoy en dia la implementacion de utf8 en mysql se queda corta, por ejemplo el caracter no se puede representar, la **recomendación es utf8bm4 para mysql**. Puedes revisar [esta pregunta](https://es.stackoverflow.com/questions/65950/utf8mb4-y-utf8-en-mysql-cu%c3%a1l-es-la-diferencia). Por otro de antemano podemos desconocer el set de datos de la tabla en su estado inicial, por lo que no se podría garantizar su utf8(mysql) cubre todos los caracteres que haya almacenados – Xerif Jan 26 '18 at 22:00
  • emoticons/astrales... ni la pensé. Tenés toda la razón! – Mariano Jan 26 '18 at 22:03
  • 1
    Plantearé una duda, no con mala intención, sino para ampliar un poco más el problema y que pueda haber una respuesta mejor y más completa... La codificación es algo que depende de muchos factores y no siempre es tan fácil como los ejemplos que tú pones, hay casos en que los caracteres se pueden guardar simplemente con este símbolo �, o con otros símbolos extraños que no son necesariamente símbolos con un patrón de correspondencia determinado. ¿Qué harías en esos casos? – A. Cedano Jan 26 '18 at 23:41
  • @A.Cedano el caracter � se da a la inversa, cuando insertamos datos `latin1` en una tabla/columna con `utf8`, el procedimiento seria el mismo pero adaptando las codificaciones, aun que en este caso seria inevitable perder algunos caracteres ya que el conjunto que latin1 puede representar es menor, se podria añadir una condicion if para salta los datos nulos y evitar errores en la consulta. – Xerif Jan 27 '18 at 00:33
  • Concuerdo con @A.Cedano. Me gustaría saber justamente eso, que hacer con casos en los que aparece � ya que es uno de los más usuales. Otra cosa que no me queda clara y disculpen. Puedo realizar una pregunta y responderla yo mismo? Es válido esto? Soy un poco (bastante) nuevo y me gustaría que me aclararan esto – Art_Code Jan 27 '18 at 00:35
  • 1
    @Huskie, sí, [es totalmente válido responder a su propia pregunta](https://es.stackoverflow.com/help/self-answer), si la misma es conforme a lo establecido por SO. – A. Cedano Jan 27 '18 at 00:39
  • 1
    @Huskie Veo que A.Cedano fue mas rápido, si es correcto autoresponder tu pregunta. – Xerif Jan 27 '18 at 00:40
  • Muchas gracias @A.Cedano y Xerif. – Art_Code Jan 27 '18 at 00:41
  • @Huskie por otro lado mañana que hoy es muy tarde amplio la respuesta con el caso inverso. En cualquier caso y lo acabo de probar se puede hacer. El caracter � aparece al representar los datos, ya que utf8 entiende que el dato que se le a pasado no corresponde a ningun simbolo de unicode por lo que no sabe que mostrar, pero la informacion del caracter original se mantiene por lo que se puede recuperar. – Xerif Jan 27 '18 at 00:50
  • @Xerif Ok te lo agradecería mucho! Mañana estaré atento a tu actualización! y aunque no es mucho te coloco el positivo a la respuesta también ya que me ayudarás a salir de una buena duda. – Art_Code Jan 27 '18 at 00:53