8.3.5 Examples of Character Set and Collation Assignment

The following examples show how MySQL determines default character set and collation values.

Example 1: Table + Column Definition

CREATE TABLE t1
(
  c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) CHARACTER SET latin2 COLLATE latin2_bin;

Here you have a column with a latin1 character set and a latin1_german1_ci collation. The definition is explicit, so that's straightforward. Notice that there's no problem storing a latin1 column in a latin2 table.

Example 2: Table + Column Definition

CREATE TABLE t1
(
   c1 CHAR(10) CHARACTER SET latin1
) CHARACTER SET latin1 COLLATE latin1_danish_ci;

This time we have a column with a latin1 character set and a default collation. Now, although it might seem natural, the default collation is not taken from the table level. Instead, because the default collation for latin1 is always latin1_swedish_ci, column c1 will have a collation of latin1_swedish_ci (not latin1_danish_ci).

Example 3: Table + Column Definition

CREATE TABLE t1
(
   c1 CHAR(10)
) CHARACTER SET latin1 COLLATE latin1_danish_ci;

We have a column with a default character set and a default collation. In this circumstance, MySQL looks up to the table level for inspiration in determining the column character set and collation. So the character set for column c1 is latin1 and its collation is latin1_danish_ci.

Example 4: Database + Table + Column Definition

CREATE DATABASE d1 CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
   c1 CHAR(10)
);

We create a column without specifying its character set and collation. We're also not specifying a character set and a collation at the table level. In this circumstance, MySQL looks up to the database level for inspiration. (The database's settings become the table's settings, and thereafter become the column's setting.) So the character set for column c1 is latin2 and its collation is latin2_czech_ci.

User Comments

Add your own comment.