MySQL 字符集学习笔记

原创

aklaus 2015-11-23 00:24:28 博主文章分类：MySQL DBA ©著作权

文章标签 mysql 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者aklaus的原创作品，请联系作者获取转载授权，否则将追究法律责任

    MySQL includes character set support that enables you to store data using a variety of 
character sets and perform comparisons according to a variety of collations. You can 
specify character . MySQL supports 
the use of character

MySQL支持server，databases，table，column级别的字符集和排序方式。这些设置在information_schema库里边有表记录，COLLATION

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

mysql> use information_schema
mysql> show tables like '%COLL%';
+---------------------------------------+
| Tables_in_information_schema (%COLL%) |
+---------------------------------------+
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
+---------------------------------------+
mysql> show tables like '%SETS%';
+---------------------------------------+
| Tables_in_information_schema (%SETS%) |
+---------------------------------------+
| CHARACTER_SETS                        |
+---------------------------------------+

The MySQL server can support multiple character sets. To list the available character sets,
use the SHOW CHARACTER SET statement. A partial listing follows. For more complete 
information, see Character Sets and Collations That MySQL Supports.
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
...
Any given character set always has at least one collation. It may have several collations.
To list the collations for a character set, use the SHOW COLLATION statement. For example,
to see the collations for the latin1 (cp1252 West European) character set, use this 
statement to find those collation names that begin with latin1:
mysql> SHOW COLLATION LIKE 'latin1%';
+---------------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+---------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1 | 5 | | | 0 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |

查看字符集和排序方式通过：SHOW CHARACTER SET;SHOW COLLATION LIKE 'character name'

每一个字符集都有一个默认的排序方式。

SERVER级别设置可以再服务器启动，或者编译，或者配置文件中配置

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

MySQL Server has a server character set and a server collation. These can be set at server 
startup on the command line or in an option file 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 [396] for the character set. Along 
with it,you can add --collation-server [396] for the collation. If you don't specify a 
character set,that is the same as saying --character-set-server=latin1 [396]. If you 
specify only a character set (for example, latin1) but not a collation, that is the same 
as saying --characterset-server=latin1 [396] --collation-server=latin1_swedish_ci [396] 
because latin1_swedish_ci is the default collation for latin1. 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. To change the default server character
set and collation when building from sources, use the DEFAULT_CHARSET [125] and 
DEFAULT_COLLATION [126] options for CMake. For example: Specifying Character Sets and 
Collations

shell> cmake . -DDEFAULT_CHARSET=latin1
Or:
shell> cmake . -DDEFAULT_CHARSET=latin1 \
-DDEFAULT_COLLATION=latin1_german1_ci

Both mysqld and CMake verify that the character set/collation combination is valid. If not,
each program displays an error message and terminates.
The server character set and collation are used as default values if the database character
set and collation are not specified in CREATE DATABASE statements. They have no other purpose.
The current server character set and collation can be determined from the values of the
character_set_server [445] and collation_server [447] system variables. These variables
can be changed at runtime.

DATABASES级别

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:可以通过创建DB时或者后期通过ALTER DATABASE修改

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]

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.每个库下都有一个db.opt保存字符集和排序方式
The CHARACTER SET and COLLATE clauses make it possible to create databases with different
character sets and collations on the same MySQL server.
Example:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

以下是字符集和排序方式设置之后，选择哪个去生效的问题，基本上db，table，colum都是这个规则
If both CHARACTER SET X and COLLATE Y are specified, character set X and collation Y are used.
If CHARACTER SET X is specified without COLLATE, character set X and its default collation are
used. To see the default collation for each character set, use the SHOW COLLATION statement.
If COLLATE Y is specified without CHARACTER SET, the character set associated with Y and
collation Y are used.
Otherwise, the server character set and server collation are used.
The database character set and collation are used as default values for table definitions if the table character set and collation are not specified in CREATE TABLE statements. The database character set also is used by LOAD DATA INFILE.

Table Character Set and Collation
Every table has a table character set and a table collation. The CREATE TABLE and ALTER TABLE
statements have optional clauses for specifying the table character set and collation:

CREATE TABLE tbl_name (column_list)
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]]
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]
Example:
CREATE TABLE t1 ( ... )
CHARACTER SET latin1 COLLATE latin1_danish_ci;

Every “character” column (that is, a column of type CHAR, VARCHAR, or TEXT) has a column character set and a column collation. Column definition syntax for CREATE TABLE and ALTER TABLE has optional clauses for specifying the column character set and collation:

col_name {CHAR | VARCHAR | TEXT} (col_length)
[CHARACTER SET charset_name]
[COLLATE collation_name]
These clauses can also be used for ENUM and SET columns:
col_name {ENUM | SET} (val_list)
[CHARACTER SET charset_name]
[COLLATE collation_name]
Examples:
CREATE TABLE t1
(
col1 VARCHAR(5)
CHARACTER SET latin1
COLLATE latin1_german1_ci
);
ALTER TABLE t1 MODIFY
col1 VARCHAR(5)
CHARACTER SET latin1
Specifying Character Sets and Collations
901
COLLATE latin1_swedish_ci;

CREATE TABLE t1
(
col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
) CHARACTER SET latin1 COLLATE latin1_bin;

CREATE TABLE t1
(
col1 CHAR(10) CHARACTER SET utf8
) CHARACTER SET latin1 COLLATE latin1_bin;

CREATE TABLE t1
(
col1 CHAR(10) COLLATE utf8_polish_ci
) CHARACTER SET latin1 COLLATE latin1_bin;

CREATE TABLE t1
(
col1 CHAR(10)
) CHARACTER SET latin1 COLLATE latin1_bin;

Neither the character set nor collation are specified for the column,

so the table defaults are used.
The column has character set latin1 and collation latin1_bin.
The MySQL client programs mysql, mysqladmin, mysqlcheck, mysqlimport, and mysqlshow
determine the default character set to use as follows: 这些程序使用默认

举例说明 character character name 使用及生效Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say
SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back
all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET latin1 before issuing the SELECT statement, the server converts the latin2 values to latin1 just before sending results back. Conversion may be lossy if there are characters that are not in both character sets.