Does MySQL server implicitly support encoding conversion?

Question

I need to implement a sorted SELECT, on a specific encoding of a field, without CONVERT.

That is, normally I'd do it by

SELECT * FROM table ORDER BY CONVERT(field USING gbk) COLLATE gbk_chinese_ci

However for some reason CONVERT was not allowed. As a result, I tried to approach this by

ALTER TABLE table MODIFY field VARCHAR(xx) CHARACTER SET gbk COLLATE gbk_chinese_ci;

SELECT * FROM table ORDER BY field

It works. That's good. However I'm worried about encoding problems.

Connection to the MySQL server includes the parameters characterEncoding=utf8 and useUnicode=true. I couldn't yet find the explanation of these params in MySQL's official document, but I suppose these ensure that the communications between the client and the server should be in utf-8.

That brings the question. Does MySQL server implicitly convert data in utf-8 to gbk when it receives the data? Do the GET params only define the charset of communication rather than that of the final stored data?

---

Edit

Comments say that the server does convert them! Thanks guys!

My further confusion is that, only one of the fields is set to use gbk, while everything else has been left to use utf8. That means the server's charset should still be utf8 globally but gbk locally for that field only.

Suppose now I fire this line of script to the server

INSERT INTO table (field_gbk, field_utf8) VALUES ("a", "b");

Does the server:

1. Receive the whole statement in utf8;
2. Convert only "a" to gbk and stores it; and
3. Stores "b" as-is to the database?

Many thanks guys!

Answer 1

Yes.

• You specify the encoding of in the client when you connect.
• You specify the encoding ("Character set") of the column you are Inserting into.

MySQL converts from one encoding to the other as it INSERTs the rows. Similarly, it converts the other way when SELECTing.

The CONVERT function should not (normally) be used for anything.

You are using Java? characterEncoding=utf8 and useUnicode=true is what it uses for declaring the client side.

"gbk" for a single column? Find. That column will handled differently than other columns.