MySQL - Character Set and Encoding

∟MySQL - Character Set and Encoding

This chapter provides tutorial notes on character set and encoding in MySQL. Topics include defining text columns with specific character set encodings; encoding conversions performed by MySQL on SQL statements and result sets; fetching non-ASCII characters in UTF-8 encoding.

Specifying Character Set for Text Columns

Creating a Table with Multiple Character Sets

Checking Character Set Setting

Storing ASCII Characters in Non-ASCII Columns

Storing Non-ASCII Characters with Encoded Bytes

Transmitting Non-ASCII Characters between PHP and MySQL

Viewing Character Set Variables

Non-ASCII Test Result Analysis

Fetching Non-ASCII Text from MySQL

MySQL Encoding Issue Example

Takeaways:

MySQL 8 uses UTF-8 as the default character set (encoding) for all tables.
MYSQL uses "character_set_results" session variable to control encoding conversion when fetching data from database.
If your database column uses a character set different than the "character_set_results" session variable, your data will be converted by MySQL Server when fetching it from the database.
PHP 7 MySQL extension sets "character_set_results" to UTF-8 by default.
Use UTF-8 as the single character set (encoding) for all database columns and set encoding to UTF-8 everywhere will avoid a lots of trouble.
MySQL 5 uses Latin1 as the default character set for all tables.
PHP 5 MySQL extension sets "character_set_results" to Latin1 by default.

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

Retrieving Chinese Text from Database to Web Pages

Input Chinese Text Data to MySQL Database

Chinese Text Encoding Conversion and Corruptions

Archived Tutorials

References

Full Version in PDF/EPUB