Xrhrft

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

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

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)