Issue with mysql import file .sql in local, due to the language encoding

Question

When I import a sql file from a server I get strange characters. I want to believe it's encoding. I am writing the values of the server bd and the local bd:

SERVER:

collation_connection: utf8mb4_general_ci
collation_database: utf8_spanish_ci
collation_server: utf8_general_ci

character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: utf8
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: utf8
character_set_system: utf8
character_sets_dir: C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\

LOCAL

collation_connection: utf8mb4_general_ci
collation_database: latin1_swedish_ci
collation_server: latin1_swedish_ci

character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: latin1
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: latin1
character_set_system: utf8
character_sets_dir: C:\xampp\mysql\share\charsets\

File SQL import contains this script:

CREATE DATABASE IF NOT EXISTS `prueba` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_spanish_ci */;
USE `prueba`;
CREATE TABLE IF NOT EXISTS `pruebaTable` (
`co_pruebaTable` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_spanish_ci NOT NULL
PRIMARY KEY (`co_pruebaTable`)
) ENGINE=InnoDB AUTO_INCREMENT=418630 DEFAULT CHARSET=utf8 
COLLATE=utf8_spanish_ci;

When I import database in my local, column "name" in "pruebaTable" contains rare characters but in server database i see this data correctly

I have tried to change this variables with this queries, but it do not work:

set collation_database=utf8_spanish_ci;

What can be the solution? I need see this data correctly when i import this file sql. I open this file with notepad++ and i can see data correctly. The codification .sql is: UTF-8, so the problem should be encoding to local database, i think

Thanks

Answer 1

Try to alter your DB:

ALTER DATABASE prueba CHARACTER SET utf8 COLLATE utf8_spanish_ci;

Answer 2

The column definition overrides the table definition, which overrides the database definition. The column definition is not the problem.

How are you doing the "import"? If it is with LOAD DATA, use the clause in it that establishes the CHARACTER SET for the load.

Do you establish a character set when connecting? Do you use SET NAMES utf8;? Something else?

See "Mojibake" in Trouble with UTF-8 characters; what I see is not what I stored

Let's debug this from the other side... What "strange characters" do you get? What is the result of SELECT HEX(name) ... for the strange text?

F1 C3B1 ñ is hex F1 when encoded in latin1, hexC3B1when correctly encoded in utf8 (or utf8mb4), or hexC383C2B1when it shows asñ` for "double encoding".