MySQL 5.0 Reference Manual :: 10 Character Set Support :: 10.3 Specifying Character Sets and Collations :: 10.3.7 Examples of Character Set and Collation Assignment


10.3.7. Examples of Character Set and Collation Assignment

The following examples show how MySQL determines default character set and collation values.
下面的例子显示了 MySQL 怎样决定默认的字符集character set和排序collation的值:

Example 1: Table and Column Definition

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

Here we have a with a latin1 character set and a latin1_german1_ci collation. The definition is explicit, so that's straightforward.
Notice that there is no problem with storing a latin1 column in a latin2 table.
这里我们有一个用latin1的字符集和latin1_german1_ci的 行(列column) 。 定义非常明显,所以很直接。
注意把一个lain1的列存到一个latin2的表里不会有问题

Example 2: Table and Column Definition

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

This time we have a column with a latin1 character set and a default collation. 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 has a collation of latin1_swedish_ci (not latin1_danish_ci).

这次 我们有一 列(column) with latin1 character set和默认排序(default collation)   这默认排序并不是默认自 table level(表 层),相反的 因为latin1默认排序永远是 latin1_swedish_ci,
所以 column列c1是latin1_swedish_ci (而不是 latin1_danish_ci  )

Example 3: Table and Column Definition

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

We have a column with a default character set and a default collation. In this circumstance, MySQL checks the table level to determine the column character set and collation. Consequently, the character set for column c1 is latin1 and its collation is latin1_danish_ci.

Example 3和上例 不同 column列c1没有指定字符集 (character set)和排序( collation), MySQL就是默认自 table level(表 层) 的设定 c1 is latin1 and its collation is latin1_danish_ci.

# # Example 4: Database, Table, and Column Definition

# # MySQL数据库 表 和 列 (资料录)的定义

CREATE DATABASE d1
    DEFAULT 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 checks the database level to determine the table settings, which thereafter become the column settings.) Consequently, the character set for column c1 is latin2 and its collation is latin2_czech_ci.

当我们建立一个新的 列(column) 和 表(table),若未明确指定(specifying) 字符集(character set) 和排序( collation), MySQL 会检查 database层 而决定, 所以 这例 : the character set for column c1 is latin2 and its collation is latin2_czech_ci

 character_set_database 是用作设定为这个数据库默认default的字符集;
 在这个数据库里创建一张表table时,表table默认的字符集被设定为character_set_database,
 也就是这个数据库默认的字符集;
 当在表table内设置一栏Column时,除非明确指定,
 否则此栏Colum缺省(default)的字符集就是表table默认的字符集;