Checking Character Set Setting

<< MySQL - Character Set and Encoding

<< Building Chinese Web Sites using PHP

This section describes how to check the character set settings of text column in MySQL server.

After created my test table, I used the following SQL Monitor commands to check what character sets are defined for each text column in the table:

C:\>\local\mysql\bin\mysql -u herong -pTopSecret

mysql> USE HerongDB;
Database changed

mysql> SHOW CREATE DATABASE HerongDB;
+----------+-------------------------------------------
| Database | Create Database
+----------+-------------------------------------------
| herongdb | CREATE DATABASE `herongdb` 
   /*!40100 DEFAULT CHARACTER SET latin1 */
+----------+-------------------------------------------

mysql> SHOW CREATE TABLE Comment_Mixed;
+---------------+--------------------------------------
| Table         | Create Table
+---------------+----------------------------------------------
| Comment_Mixed | CREATE TABLE `comment_mixed` (
  `Test_Name` varchar(256) default NULL,
  `String_ASCII` varchar(256) character set ascii default NULL,
  `String_Latin1` varchar(256) default NULL,
  `String_UTF8` varchar(256) character set utf8 default NULL,
  `String_GBK` varchar(256) character set gbk default NULL,
  `String_Big5` varchar(256) character set big5 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------------+----------------------------------------------

Here is what I learned from the output:

• The default character set for database "HerongDB" is defined as "latin1".
• The default character set for table "comment_mixed" is defined as "latin1".
• No character set is explicitly defined for column "Test_Name", so its character set is "latin1" inherited from the table level default character set.
• Column "String_ASCII" is explicitly defined to use character set "ascii".
• Column "String_Latin1" is explicitly defined to use character set "latin1".
• Column "String_UTF8" is explicitly defined to use character set "utf-8".
• Column "String_GBK" is explicitly defined to use character set "gbk".
• Column "String_Big5 is explicitly defined to use character set "big5".

Sections in This Chapter

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