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%';
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 |
+---------------------------------------+
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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级别设置 可以再服务器启动，或者编译，或者配置文件中配置
|
1
2
3
|
[mysqld]
character-
set
-server=utf8
collation-server=utf8_general_ci
|
|
1
2
3
4
5
6
7
8
9
10
|
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
|
1
2
3
4
|
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
|
1
2
3
4
5
6
7
|
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
var
iables. These
var
iables
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修改
|
1
2
3
4
5
6
|
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:
|
1
|
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:
|
1
2
3
4
5
6
7
8
9
|
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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;
|
|
1
2
3
4
|
CREATE TABLE t1
(
col1 CHAR(
10
) CHARACTER SET utf8 COLLATE utf8_unicode_ci
) CHARACTER SET latin1 COLLATE latin1_bin;
|
|
1
2
3
4
|
CREATE TABLE t1
(
col1 CHAR(
10
) CHARACTER SET utf8
) CHARACTER SET latin1 COLLATE latin1_bin;
|
|
1
2
3
4
|
CREATE TABLE t1
(
col1 CHAR(
10
) COLLATE utf8_polish_ci
) CHARACTER SET latin1 COLLATE latin1_bin;
|
|
1
2
3
4
|
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.
本文转自 aklaus 51CTO博客，原文链接:http://blog.51cto.com/aklaus/1715805
