Creating a Table with Multiple Character Sets

This section describes how to create a table with multiple character sets in MySQL.

In order to prepare for storing Chinese character strings in multiple character sets, I created the following table with a number of VARCHAR columns. Each of them is set to a different "character set".

1. Create a SQL script file, MySQL-Create-Table-Mixed.sql:

-- MySQL-Create-Table-Mixed.sql
-- Copyright (c) 2007-2019, All Rights Reserved.
-- To run this script at mysql> prompt, use the "source" command:
--   mysql> source MySQL-Create-Table-Mixed.sql

-- Drop the table
DROP TABLE Comment_Mixed;

-- Create a new table
CREATE TABLE Comment_Mixed (
  Test_Name VARCHAR(356),
  String_Latin1 VARCHAR(256) CHARACTER SET latin1,
  String_UTF8 VARCHAR(256) CHARACTER SET utf8,
  String_Big5 VARCHAR(256) CHARACTER SET big5);

-- Insert a test record
INSERT INTO Comment_Mixed (String_ASCII, String_Latin1, 
    String_UTF8, String_GBK, String_Big5)
  VALUES ('Television', 'Television', 
    'Television', 'Television', 'Television');

2. Run this script with the "SOURCE" command in MySQL Monitor:

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

mysql> USE HerongDB;
Database changed

mysql> SOURCE MySQL-Create-Table-Mixed.sql
Query OK, 0 rows affected (1.07 sec)

Query OK, 0 rows affected, 1 warning (0.38 sec)

Now I have a table with text columns defined with different character sets to test ASCII, Latin1, UTF-8 GBK, and Big5 encodings. Note that I didn't create any text column with GB2312 encoding, because GB2312 is subset of GBK.

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

 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 - Sending Non-ASCII Text to MySQL

 Retrieving Chinese Text from Database to Web Pages

 Input Chinese Text Data to MySQL Database

 Archived Tutorials


 Full Version in PDF/EPUB