Summary - Sending and Fetching Non-ASCII Text
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>
Last update: 2015.
Table of Contents
About This Book
PHP Installation on Windows Systems
Integrating PHP with Apache Web Server
charset="*" - Encodings on Chinese Web Pages
Chinese Characters in PHP String Literals
Multibyte String Functions in UTF-8 Encoding
Input Text Data from Web Forms
Input Chinese Text Data from Web Forms
MySQL - Installation on Windows
MySQL - Connecting PHP to Database
MySQL - Character Set and Encoding
►MySQL - Sending Non-ASCII Text to MySQL
Sending Non-ASCII Text to MySQL in Latin1 Encoding
Sending Non-ASCII Text to MySQL in UTF8 Encoding
Sending Non-ASCII Text to MySQL in GBK Encoding
Sending Non-ASCII Text to MySQL in Big5 Encoding
Sending Non-ASCII Text to MySQL in Multiple Encoding
►Summary - Sending and Fetching Non-ASCII Text
Retrieving Chinese Text from Database to Web Pages
Input Chinese Text Data to MySQL Database
PDF Printing Version