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

  • IN additional to the answers below - just checking, you're trying to get Spanish characters correct, and you're using latin1_swedish_ci as your default collation? Wanted to be sure that was deliberate - if so, fine.
    – RDFozz
    Oct 16, 2017 at 18:03
  • yes i am trying to get spanish charecters and i am using latin1_swedish_ci collation . Oct 18, 2017 at 7:49

2 Answers 2


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

  • I tried your sugesstion. I have altered the table column charecter set as utf8 but still there is no change in result . alter table prod_inns modify prod_name varchar(100) CHARSET utf8; result: select * from prod_inns ; Otoño Oct 16, 2017 at 16:56
  • Yes, but it was inserted erroneously to begin with. Try doing the insert again! p.s. use backticks (`) to highlight actual code in comments!
    – Vérace
    Oct 16, 2017 at 17:00
  • The 'correct' fix was to do a pair of ALTERs, hopping through VARBINARY. See here. (I don't want to think about how to fix it now that you have compounded the problems.
    – Rick James
    Oct 16, 2017 at 17:04
  • i tried with backtick while inserting throwing ERROR 1054 (42S22): Unknown column 'Otoño' in 'field list' Tried with varbinary as well still same problem persisting. Oct 16, 2017 at 17:10
  • @Jockhelson - you missed Vérace's point -- backtics are a formatting convention to get This Look instead of This Look in Comments (and in Q&A).
    – Rick James
    Oct 16, 2017 at 17:24

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


ALTER  DATABASE database_name
CHARACTER SET character_set_name
COLLATE collation_name

in your case, it should be

ALTER DATABASE my_database
COLLATE spanish_unicode_ci

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

  • Those are only defaults for new tables. So these ALTERs will not have any effect.
    – Rick James
    Oct 16, 2017 at 17:02
  • mysql> ALTER DATABASE ecomm CHARACTER SET utf8 COLLATE spanish_unicode_ci; ERROR 1273 (HY000): Unknown collation: 'spanish_unicode_ci' I recently upgraded mysql to 5.5 to 5.7 , it worked properly in my old mysql version. Oct 16, 2017 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.