This section provides a quick summary on sending and fetching non-ASCII character strings to and from MySQL server.
Looking back on tests I did in this chapter and the previous chapter, here are some simple rules
you need to follow when sending and fetch non-ASCII text to and from MySQL server:
Non-ASCII text data can be included in SQL statement. But you need to tell MySQL server what is the encoding used for non-ASCII text
in the SQL statement by setting session variable character_set_client. For example, "SET character_set_client=UTF8".
To preventing MySQL server performing any encoding conversion before executing the SQL statement, set session variable character_set_connection
to match character_set_client. For example, "SET character_set_connection=utf8".
When fetching non-ASCII text data back from MySQL server, set session variable character_set_connection to match the encoding used by the table column
to the text data. This prevents MySQL server from doing any encoding conversion beforing returning the fetched result.
For example, "SET character_set_results=utf8".
If you want the fetched text data to be in an encoding different than the encoding of the original data column,
you can tell MySQL to perform an encoding conversion for you by setting character_set_results to the encoding you wanted.
For example, text data stored in a Big5 text column, and you want to fetch it as UTF8, run "SET character_set_results=utf8".
Applying those rules to a Chinese web site, my suggestions would be:
Define all text columns as UTF-8.
Run "SET character_set_client=utf8" and "SET character_set_connection=utf8".
Include all text data in UTF-8 encoding in SQL statements.
Run "SET character_set_result=utf8".
Fetch all text data in UTF-8 encoding.
By the way, MySQL offers a special SET command:
SET NAMES <encoding_name>
This is command is equivalent to 3 sET commands:
SET character_set_client = <encoding_name>
SET character_set_results = <encoding_name>
SET character_set_connection = <encoding_name>
