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