kYsPeD2P

1--
2-- Estructura para la tabla `tabla_latin1` con datos insertados en UTF8
3--
4
5DROP TABLE IF EXISTS `tabla_latin1`;
6CREATE TABLE IF NOT EXISTS `tabla_latin1` (
7  `idTabla` int(11) NOT NULL AUTO_INCREMENT,
8  `texto` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
9  PRIMARY KEY (`idTabla`)
10) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
11
12--
13-- Volcado de datos para la tabla `tabla_latin1`
14--
15
16INSERT INTO `tabla_latin1` (`idTabla`, `texto`) VALUES
17(1, 'Ç'),
18(2, 'È'),
19(3, 'É'),
20(4, 'Ê'),
21(5, 'Ë'),
22(6, 'Ì'),
23(7, 'Ã'),
24(8, 'ÃŽ'),
25(9, 'Ã'),
26(10, 'Ã'),
27(11, 'Ñ'),
28(12, 'Ã’'),
29(13, 'Ó'),
30(14, 'Ô'),
31(15, 'Õ'),
32(16, 'Ö');
33
34--
35-- datos correctos: ÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ
36--
37	
38--
39-- Estructura de tabla para la tabla `tabla_latin1` con datos insertados en UTF8
40--
41
42DROP TABLE IF EXISTS `tabla_latin1`;
43CREATE TABLE IF NOT EXISTS `tabla_latin1` (
44  `idTabla` int(11) NOT NULL AUTO_INCREMENT,
45  `texto` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
46  PRIMARY KEY (`idTabla`)
47) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
48
49--
50-- Volcado de datos para la tabla `tabla_latin1`
51--
52
53INSERT INTO `tabla_latin1` (`idTabla`, `texto`) VALUES
54(1, 'Ç'),
55(2, 'È'),
56(3, 'É'),
57(4, 'Ê'),
58(5, 'Ë'),
59(6, 'Ì'),
60(7, 'Ã'),
61(8, 'ÃŽ'),
62(9, 'Ã'),
63(10, 'Ã'),
64(11, 'Ñ'),
65(12, 'Ã’'),
66(13, 'Ó'),
67(14, 'Ô'),
68(15, 'Õ'),
69(16, 'Ö');
70	
71SELECT CONVERT(cast(texto AS BINARY) USING utf8mb4) texto_en_utf8 FROM tabla_latin1;
72	
73+---------------+
74| texto_en_utf8 |
75+---------------+
76| Ç             |
77| È             |
78| É             |
79| Ê             |
80| Ë             |
81| Ì             |
82| Í             |
83| ÃŽ             |
84| Ï             |
85| Ð             |
86| Ñ             |
87| Ã’             |
88| Ó             |
89| Ô             |
90| Õ             |
91| Ö             |
92+---------------+
9316 rows in set (0,00 sec)
94	
95UPDATE tabla_latin1 SET texto = CONVERT(cast(texto as binary) using utf8mb4);
96-- Query OK, 16 rows affected (0,06 sec)
97-- Rows matched: 16  Changed: 16  Warnings: 0
98	
99SELECT * FROM tabla_latin1;
100+---------+-------+
101| idTabla | texto |
102+---------+-------+
103|       1 | Ç     |
104|       2 | È     |
105|       3 | É     |
106|       4 | Ê     |
107|       5 | Ë     |
108|       6 | Ì     |
109|       7 | Í     |
110|       8 | ÃŽ     |
111|       9 | Ï     |
112|      10 | Ð     |
113|      11 | Ñ     |
114|      12 | Ã’     |
115|      13 | Ó     |
116|      14 | Ô     |
117|      15 | Õ     |
118|      16 | Ö     |
119+---------+-------+
12016 rows in set (0,00 sec)
121	
122ALTER TABLE tabla_latin1 COLLATE=utf8mb4_general_ci;
123-- Query OK, 0 rows affected (0,07 sec)
124-- Records: 0  Duplicates: 0  Warnings: 0
125
126
127ALTER TABLE tabla_latin1 CHANGE `texto` `texto` VARCHAR(255) CHARACTER SET utf8mb4;
128--Query OK, 16 rows affected (1,29 sec)
129--Records: 16  Duplicates: 0  Warnings: 0