Struggling with MySQL character set, collation

Question

I am facing problems with inserting Spanish characters from my application.

When I am inserting on the DB the characters are inserting properly, but when I am trying passing the same data in from the application, the data stored in the table isn't correct.

For example, when I insert directly on DB server:

CREATE TABLE  prod_inns(`prod_name` varchar(100) COLLATE latin1_bin DEFAULT NULL)

insert into prod_inns values(Otoño);

I see this:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| Otoño       |
+-------------+

delete  FROM  prod_inns ;

However, when I insert the same value from the application form, I see this instead:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| Otoño      |
+-------------+

Here are my MySQL settings:

MySQL>  show global variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MySQL>  show global variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

What's wrong with my settings? What should I do to store the data and read the data properly?

Note: I am using mysql-5.7.19

Answer 1

Don't use latin1, use utf8. You have "Mojibake" because the bytes in the client are utf8-encoded, yet you said they were latin1.

More on Mojibake and what to do about it: https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

Answer 2

You need to alter your database character set and collation as per the instructions here:

i.e.

ALTER  DATABASE database_name
CHARACTER SET character_set_name
COLLATE collation_name

in your case, it should be

ALTER DATABASE my_database
CHARACTER SET utf8
COLLATE spanish_unicode_ci

(or spanish2 if you want Traditional - as per here)