0

The mysql table that I am inserting it into (let's call it foo) is latin1 character encoded (shown via the command shown How do I see what character set a MySQL database / table / column is?) and my database has the following result after show variables: 

| character_set_client                    | latin1                                                                                    |
| character_set_connection                | latin1                                                                                    |
| character_set_database                  | latin1

In foo I have an entry stored in a varchar(255) col with the string foo® and when I view it in hex it shows that it is encoded in utf8 (666F6FC2AE). Running a select query from the mysql prompt shows me foo®. However when I insert the same string encoded in latin1 666F6FAE I get a foo� when I run a select in the same mysql command prompt. Why is it that I have utf8 encoded data in my latin1 table and why am I not able to insert latin1 encoded data into my table?

Community
  • 1
  • 1
Jason Zhu
  • 2,194
  • 3
  • 23
  • 27

1 Answers1

0

Your command line client seems to use UTF-8 for display/input/output. 666F6FC2AE is not a UTF-8 encoded value in a latin-1 column, it's the latin-1 code for "foo®". Getting this back from the database, your CLI interprets the latin-1 data as UTF-8, displaying "foo®".

To illustrate:

  • CLI operates in UTF-8
  • you enter "foo®" on the CLI, which is 66 6F 6F C2AE
  • database connection is set to latin-1
  • database interprets input as latin-1, thinks you want to store "foo®"
  • column encoding is latin-1, no problem, database stores "foo®"
  • you fetch data from database, all latin-1, you get back 66 6F 6F C2 AE
  • CLI interprets this as UTF-8, displays "foo®"

You need to set your connection encoding to utf8, since your client sends UTF-8. That, or convince your CLI to operate in Latin-1, which may be harder/less convenient/causing more side effects.

deceze
  • 510,633
  • 85
  • 743
  • 889