Re: UTF-8 database, Windows mysql.exe and INSERT/SELECT troubles. SET NAMES latin1 doesn't translate to latin1
Posted by: Rick James
Date: March 18, 2010 09:05PM

I agree, it is very confusing. And very easy to mess up.

1. You have some bytes in hand.
* If you have "extended ASCII values 164", then you have the 1-byte latin1 for tilde-n.
* If you have the two bytes \xC2\xA4, you have the utf8 representation of tilde-n.

2. When you connect to the mysql server, you announce which (latin1 or utf8 or ...) you have in hand.

3. Meanwhile, you have declared your database.table.column to be latin1 (or utf8 or whatever).

4. When you INSERT what you have in hand, it gets converted (if necessary) to the column character set.

5. Similarly, when you SELECT the conversion (from column specification, to SET NAMES specification) occurs.

Your 'N' occurred because the next byte(s) were invalid utf8, and mysql simply (and rather silently) truncated the string.

To figure out what is in the database, do
select lastname, HEX(lastname), LENGTH(lastname), CHAR_LENGTH(lastname) from clients;
* If it is proper utf8, LENGTH=5 and CHAR_LENGTH=7 and the hex will have ...C2A4...
* If lastname is latin1 and you stored latin1, LENGTH=5, CHAR_LENGTH=5
* If you did a double conversion, LENGTH=5 and CHAR_LENGTH=11.

I would not depend on the Windows and the mysql client to 'do the right thing'. But perhaps you can get close with cp1251(?) codeset for Windows and latin1 for the cli, with utf8 for the table. Avoid the windows-only characters; hope you don't need any characters beyond Western European.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: UTF-8 database, Windows mysql.exe and INSERT/SELECT troubles. SET NAMES latin1 doesn't translate to latin1
3892
March 18, 2010 09:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.