Al tratar de importar datos desde un archive con LOAD DATA INFILE, mysql muestra estas advertencias
Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_estado' at row 16 Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_ciudad' at row 16 Warning (Code 1366): Incorrect string value: '\xC1lvaro...' for column 'd_mnpio' at row 17 Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_estado' at row 17 Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_ciudad' at row 17 Warning (Code 1366): Incorrect string value: '\xF3lvora' for column 'd_asenta' at row 18 Warning (Code 1366): Incorrect string value: '\xC1lvaro...' for column 'd_mnpio' at row 18 Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_estado' at row 18 Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_ciudad' at row 18 Warning (Code 1366): Incorrect string value: '\xC1lvaro...' for column 'd_mnpio' at row 19 Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_estado' at row 19 Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_ciudad' at row 19 Warning (Code 1366): Incorrect string value: '\xF3n El ...' for column 'd_asenta' at row 20 Warning (Code 1366): Incorrect string value: '\xC1lvaro...' for column 'd_mnpio' at row 20 Warning (Code 1366): Incorrect string value: '\xE9xico' for column 'd_estado' at row 20
Para solucionarlos agregar estos parámetros en el archivo de configuración de mysql my.cnf:
[mysqld] … character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci skip-character-set-client-handshake [client] default-character-set = utf8mb4 [mysql] default-character-set=utf8mb4
Comprobación:
Abrir una nueva sesión en mysql y verificar que la codificación de caracteres esta correcta:
MariaDB [(none)]> show variables like 'collation%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +----------------------+--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> show variables like '%charac%'; +--------------------------+-----------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | D:\Software\xampp\mysql\share\charsets\ | +--------------------------+-----------------------------------------+ 8 rows in set (0.00 sec) MariaDB [(none)]>