Creating a Table with Multiple Character Sets

∟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) 2005 HerongYang.com. 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_ASCII VARCHAR(256) CHARACTER SET ascii,
  String_Latin1 VARCHAR(256) CHARACTER SET latin1,
  String_UTF8 VARCHAR(256) CHARACTER SET utf8,
  String_GBK VARCHAR(256) CHARACTER SET gbk,
  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.