MySQL 5.0 Reference Manual :: 10 Character Set Support :: 10.3 Specifying Character Sets and Collations :: 10.3.2 Database Character Set and Collation

  • MySQL 5.0 Reference Manual

  • 10.3 Specifying Character Sets and Collations
  • 10.3.1 Server Character Set and Collation
  • 10.3.2 Database Character Set and Collation
  • 10.3.3 Table Character Set and Collation
  • 10.3.4 Column Character Set and Collation
  • 10.3.5 Character String Literal Character Set and Collation
  • 10.3.6 National Character Set
  • 10.3.7 Examples of Character Set and Collation Assignment
  • 10.3.8 Compatibility with Other DBMSs


10.3.1. Server Character Set and Collation

MySQL Server has a server character set and a server collation. These can be set at server startup and changed at runtime(运行中).

Initially, the server character set and collation depend on the options that you use when you start mysqld. You can use --character-set-server for the character set. Along with it, you can add --collation-server for the collation.
If you don't specify a character set, that is the same as saying --character-set-server=latin1. If you specify only a character set (for example, latin1) but not a collation, that is the same as saying --character-set-server=latin1 --collation-server=latin1_swedish_ci because latin1_swedish_ci is the default collation for latin1.
如果你不指定(specify)字符集,默认就是说--character-set-server=latin1。
如果你只指定了字符集(例如latin1)但是没有指定 collation,
就相当于:--character-set-server=latin1
--collation-server=latin1_swedish_ci
因为 latin1_swedish_ci是latin1字符集的默认collation,

Therefore, the following three commands all have the same effect: 因此下面三个命令都具有 同样效果:

shell> mysqld
shell> mysqld --character-set-server=latin1
shell> mysqld --character-set-server=latin1 \
           --collation-server=latin1_swedish_ci

One way to change the settings is by recompiling. If you want to change the default server character set and collation when building from sources, use: --with-charset and --with-collation as arguments for configure. For example:

有个方法 改变这个设置 是重新编译recompiling,如果你想改变 默认的服务器(default server)字符集和 collation,在我們建立源码就使用参数(arguments)來configure 設定 --with-charset和 --with-collation,例如:

shell> ./configure --with-charset=latin1

Or:

shell> ./configure --with-charset=latin1 \
           --with-collation=latin1_german1_ci

Both mysqld and configure verify that the character set/collation combination is valid.
If not, each program displays an error message and terminates.
mysqld 和configure 都会檢查verify 字符集/collation的结合是否有效,If not,会显示 error message并中止terminates。

The current server character set and collation can be determined from the values of the character_set_server and collation_server system variables. These variables can be changed at runtime.
目前的服务器字符集和排序collation 是被 character_set_server和 collation_server这两个系统变量system variables的值決定,这些变量可以在运行时runtime更改

10.3.2. Database Character Set and Collation

http://dev.mysql.com/doc/refman/5.0/en/charset-database.html

Every database has a database character set and a database collation. The CREATE DATABASE and ALTER DATABASE statements have optional clauses for specifying the database character set and collation:
用 CREATE DATABASE和 ALTER DATABASE语句(可选择 规则 optional clauses) 来指明数据库字符集和排序collation :

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]
 mysql> CREATE DATABASE raisin
     -> DEFAULT CHARACTER SET utf8
     -> DEFAULT COLLATE utf8_general_ci;
这[      ] 里面的指令 可省略 can be omitted	 

The keyword SCHEMA can be used instead of DATABASE.

All database options are stored in a text file named db.opt that can be found in the database directory.
所有的

The CHARACTER SET and COLLATE clauses make it possible to create databases with different character sets and collations on the same MySQL server.
因为这样, 就可能在同一个MySQL 服务器上创建具有不同字符集和collation的数据库。

Example:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL chooses the database character set and database collation in the following manner:
MySQL选择数据库字符集和排序collation可以用以下方法:

  • If both CHARACTER SET X and COLLATE Y were specified, then character set X and collation Y.
    如果 CHARACTER SET X 和 COLLATE Y 被指定了, 那么字符集是 X , 排序collation 是 Y.

  • If CHARACTER SET X was specified without COLLATE, then character set X and its default collation.
    如果 CHARACTER SET X 被指定,但是没有指定 COLLATE, 那么字符集是 X , 排序collation 是默认collation.

  • If COLLATE Y was specified without CHARACTER SET, then the character set associated with Y and collation Y.
    如果 COLLATE Y 被指定,但是没有指定 CHARACTER SET, 那么字符集是 Y , 排序collation 是Y.

  • Otherwise, the server character set and server collation.
    否则, 就用服务器字符集和服务器 collation ( that is the same as saying --character-set-server=latin1
    --collation-server=latin1_swedish_ci )

The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements. They have no other purpose.
如果没有指定 table-level, 就继承database-level 的值.

The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes.
If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server.
数据库的默认字符集和 排序collation 是被 character_set_database和 collation_database这两个系统变量system variables的值決定,
If there is no 默认数据库的值, 就继承server-level 的系统变量system variables.

你可以只设置 server-level的字符集,其它的database-level,table-level,以及column-level的字符集,都继承自server-level的字符集。






real_vine@hotmail.com