设置字符集和排序规则 - Source

Revision: 60588
用户: longshuai Ma
Date: 2017-09-26 14:39

<<toc>>
在MariaDB中，默认的字符集[[data-types-character-sets-and-collations|character set]]为latin1，默认的排序规则为latin1_swedish_ci(但不同的发行版可能会不同，例如[[differences-in-mariadb-in-debian|Differences in MariaDB in Debian]])。字符集和排序规则都可以从server端一直指定到字段级别，client到server连接时也可以指定。当修改字符集但却没有指定排序规则时，将总是使用字符集的默认排序规则。

字符集和排序规则总是级联向下的，所以当没有为字段指定排序规则时，将查找表的排序规则，同样对于表来说会上查到数据库，对数据库来说会上查到server级。因此，可以使用极细粒度的字符集和排序规则来控制控制你的数据。

每种字符集的默认排序规则可使用[[show-collation|SHOW COLLATION]]语句查看，例如查找latin2字符集的默认排序规则：
<<code sql>>
SHOW COLLATION LIKE 'latin2%';
+---------------------+---------+----+---------+----------+---------+
| Collation           | Charset | Id | Default | Compiled | Sortlen |
+---------------------+---------+----+---------+----------+---------+
| latin2_czech_cs     | latin2  |  2 |         | Yes      |       4 |
| latin2_general_ci   | latin2  |  9 | Yes     | Yes      |       1 |
| latin2_hungarian_ci | latin2  | 21 |         | Yes      |       1 |
| latin2_croatian_ci  | latin2  | 27 |         | Yes      |       1 |
| latin2_bin          | latin2  | 77 |         | Yes      |       1 |
+---------------------+---------+----+---------+----------+---------+
<</code>>

== Server级别
可以设置系统变量[[server-system-variables#character_set_server|character_set_server]]来改变默认的server级的字符集。该变量可以使用[[SET]]命令在启动时或动态地设置：
<<sql>>
SET character_set_server = 'latin2';
<</sql>>
类似地，变量[[server-system-variables#collation_server|collation_server]]用于设置server级别的默认排序规则。
<<sql>>
SET collation_server = 'latin2_czech_cs';
<</sql>>

== Database级别
[[create-database|CREATE DATABASE]] 和 [[alter-database|ALTER DATABASE]] 语句中包含了可选的字符集、排序规则的设置子句。如果没有设置字符集、排序规则，它们将使用server级别的默认值。

<<sql>>
CREATE DATABASE czech_slovak_names 
  CHARACTER SET = 'keybcs2'
  COLLATE = 'keybcs2_bin';
<</sql>>

<<sql>>
ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';
<</sql>>

使用下面的语句可以查看数据库所使用的字符集：
<<code sql>>
SHOW CREATE DATABASE czech_slovak_names;
+--------------------+--------------------------------------------------------------------------------+
| Database           | Create Database                                                                |
+--------------------+--------------------------------------------------------------------------------+
| czech_slovak_names | CREATE DATABASE `czech_slovak_names` /*!40100 DEFAULT CHARACTER SET keybcs2 */ |
+--------------------+--------------------------------------------------------------------------------+
<</code>>
或者，使用下面的语句可以查看各数据库采用的字符集和排序规则：
<<code sql>>
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | czech_slovak_names | keybcs2                    | keybcs2_general_ci     | NULL     |
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
<</code>>

可以仅指定排序规则，由于每种排序规则都对应于一种字符集，因此会同时设置排序规则所对应的字符集。

<<code sql>>
CREATE DATABASE danish_names COLLATE 'utf8_danish_ci';

SHOW CREATE DATABASE danish_names;
+--------------+----------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                              |
+--------------+----------------------------------------------------------------------------------------------+
| danish_names | CREATE DATABASE `danish_names` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci */ |
+--------------+----------------------------------------------------------------------------------------------+
<</code>>

尽管可以动态地设置系统变量[[server-system-variables#character_set_database|character_set_database]]和[[server-system-variables#collation_database|collation_database]]，但它们用于确定数据库所使用的默认字符集和排序规则，应该尽量仅在server端进行设置。

== Table级别

[[create-table|CREATE TABLE]] 和 [[alter-table|ALTER TABLE]] 语句支持可选的字符集、排序规则设置子句，它们是MariaDB和MySQL对标准SQL语句的扩展。

<<code sql>>
CREATE TABLE english_names (id INT, name VARCHAR(40)) 
  CHARACTER SET 'utf8' 
  COLLATE 'utf8_icelandic_ci';
<</code>>

如果既没有指定字符集也没有指定排序规则，则采用数据库的默认值。如果仅设置了字符集，将采用字符集的默认排序规则。如果仅设置了排序规则，则排序规则相关联的字符集也会被设置。见 [[supported-character-sets-and-collations|Supported Character Sets and Collations]]。

<<code sql>>
ALTER TABLE table_name
 CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
<</code>>

如果没有指定排序规则，将使用字符集默认的排序规则。见 [[supported-character-sets-and-collations|Supported Character Sets and Collations]]。

对于[[varchar|VARCHAR]]或[[text|TEXT]]类型的字段，为了保证新字段足够大以能够存储原字段的大量字符，##CONVERT TO CHARACTER SET##可能会改变数据类型。

例如，某TEXT类型的字段存储ascii字符时由于每个字符仅占用一个字节，因此该字段可以存储65,535个字符。如果该字段转换为UTF8，由于每个字符需要3个字节，该字段的数据类型将被转换为[[mediumtext|MEDIUMTEXT]]类型以便能够存储所有原字段的字符。

为了避免##CONVERT TO CHARACTER SET##子句改变数据类型，可以在单独的字段上使用##MODIFY##。例如：
<<code sql>>
ALTER TABLE table_name MODIFY ascii_text_column TEXT CHARACTER SET utf8;
ALTER TABLE table_name MODIFY ascii_varchar_column VARCHAR(M) CHARACTER SET utf8;
<</code>>

== Column级别
同样可以为字段类型为CHAR、TEXT或VARCHAR的字段设置字符集和排序规则。可以使用[[create-table|CREATE TABLE]]和[[alter-table|ALTER TABLE]]语句进行设置——不像table级别的设置，column级别的设置是标准SQL所支持的。

<<code sql>>
CREATE TABLE european_names (
  croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci',
  greek_names VARCHAR(40) CHARACTER SET 'greek');
<</code>>
如果既没有指定字符集也没有指定排序规则，将使用表的默认值。如果仅设置了字符集，排序规则将使用字符集的默认排序规则，如果仅设置了排序规则，则其对应的字符集也会被设置。

<<style class="redbox">>
当使用[[alter-table|ALTER TABLE]]改变字段的字符集时，需要确保字符集可以和已有数据兼容。MariaDB将尽可能地一一映射转换字符数据，但无法转换的数据可能会乱码丢失。
<</style>>

可以使用[[show-create-table|SHOW CREATE TABLE]]语句，或者查询##INFORMATION_SCHEMA##数据库来查看字段的字符集和排序规则所采用的值。

<<code sql>>
SHOW CREATE TABLE european_names\G
*************************** 1. row ***************************
       Table: european_names
Create Table: CREATE TABLE `european_names` (
  `croatian_names` varchar(40) CHARACTER SET cp1250 COLLATE cp1250_croatian_ci DEFAULT NULL,
  `greek_names` varchar(40) CHARACTER SET greek DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci
<</code>>

<<code sql>>
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'european%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: danish_names
              TABLE_NAME: european_names
             COLUMN_NAME: croatian_names
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
  CHARACTER_OCTET_LENGTH: 40
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: cp1250
          COLLATION_NAME: cp1250_croatian_ci
             COLUMN_TYPE: varchar(40)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: danish_names
              TABLE_NAME: european_names
             COLUMN_NAME: greek_names
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
  CHARACTER_OCTET_LENGTH: 40
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: greek
          COLLATION_NAME: greek_general_ci
             COLUMN_TYPE: varchar(40)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
<</code>>

== 文件名

从MariaDB 5.1开始，系统变量[[server-system-variables#character_set_filesystem|character_set_filesystem]]可用来控制对给定字符串的文件名解析。它会影响的下面语句和函数：

* [[select-into-dumpfile|SELECT INTO DUMPFILE]]
* ##SELECT INTO OUTFILE##
* [[load-data-infile|LOAD DATA INFILE]]
* [[load-xml|LOAD XML]]
* [[load_file|LOAD_FILE()]]

== 字面符号(译者注：可理解为常量、普通字符串或文本字符)
默认情况下，字符集和排序规则通过系统变量[[server-system-variables#character_set_connection|character_set_connection]]和[[server-system-variables#collation_connection|collation_connection]]来决定使用的字面符号。但是可以显式地指定它们：
<<code sql>>
[_charset_name]'string' [COLLATE collation_name]
<</code>>

对于没有字符集introducer的字符集来说，它的字符串符号由系统变量[[server-system-variables#character_set_connection|character_set_connection]]决定。

该查询：
<<code sql>>
  SELECT CHARSET('a'), @@character_set_connection;
<</code>>
总是会为两列返回相同的字符集名称。

[[server-system-variables#character_set_client|character_set_client]]和[[server-system-variables#character_set_connection|character_set_connection]]一般会被设置为相同的值(例如在三次握手期间，或使用##SET NAMES##进行了设置)。但允许设置为不同值。

=== 示例
设置@@character_set_client和@@character_set_connection为不同的值时可能很有用处：

示例 1：

假设我们在utf8的数据库中创建下面的表：
<<code sql>>
CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO t1 VALUES ('oe'),('ö');
<</code>>
现在使用"mysql.exe"连接，它会使用DOS的字符集(西欧的机器上是cp850)，如果想要根据德国电话簿规则获取等于“ö”的所有记录。

使用下面的语句：

SET @@character_set_client=cp850, @@character_set_connection=utf8;
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;

它将返回：
<<code>>
+------+
| a    |
+------+
| oe   |
| ö    |
+------+
<</code>>

工作方式如下：
# 客户端使用cp850发送查询语句。
# 服务端解析查询语句时，将把'ö'从@@character_set_client (cp850)转换为@@character_set_connection (utf8)的文本字符。
# 服务端对该文本字符应用排序规则"utf8_germal2_ci"。
# 服务端使用utf8_german2_ci进行字符比较。

注意，如果重写为如下脚本：
<<code sql>>
SET NAMES cp850;
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;
<</code>>
将报错：
<<code>>
ERROR 1253 (42000): COLLATION 'utf8_german2_ci' is not valid for CHARACTER SET 'cp850'
<</code>>
因为：
* 在第2步中，将不会转换为utf8的文本字符，而是转换为cp850的文本字符。
* 在第3步中，服务端无法对cp850的字符串应用排序规则utf8_german2_ci。

示例 2:

继续假设我们的数据库为utf8，并使用西欧机器上的"mysql.exe"进行连接。

我们这样做：
<<code sql>>
SET @@character_set_client=cp850, @@character_set_connection=utf8;
CREATE TABLE t2 AS SELECT 'ö';
<</code>>
这将会创建一张包含##VARCHAR(1) CHARACTER SET utf8##字段类型的表。

注意，如果查询重写为：
<<code sql>>
SET NAMES cp850;
CREATE TABLE t2 AS SELECT 'ö';
<</code>>
创建的表中的字段将为##VARCHAR(1) CHARACTER SET cp850##类型，这可能不是我们所期望的。

=== N
同样, 可以使用前缀##N##或##n##来转换文本字符为国际字符集(MariaDB中为utf8)。

例如：
<<code sql>>
SELECT _latin2 'Müller';
+-----------+
| MĂźller   |
+-----------+
| MĂźller   |
+-----------+
<</code>>

<<code sql>>
SELECT CHARSET(N'a string');
+----------------------+
| CHARSET(N'a string') |
+----------------------+
| utf8                 |
+----------------------+
<</code>>

<<code sql>>
SELECT 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci';
+---------------------------------------------------+
| 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci'  |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
<</code>>

== 存储过程和视图

当创建存储过程或视图时，其内出现的文本字符默认使用系统变量[[server-system-variables#character_set_connection|character_set_connection]]和[[server-system-variables#collation_connection|collation_connection]]指定的字符集和排序规则。可以使用##SHOW CREATE##语句获取所使用的值。要改变已存在存储过程、视图中的文本字符的字符集，需要删除存储程序然后重建。

对于存储过程的参数和返回值，可以通过##CHARACTER SET##和##COLLATE##子句来指定其使用的字符集和排序规则。在MariaDB 5.5之前不支持指定排序规则。

下面的示例中展示了创建存储程序时所使用的字符集和排序规则。

<<code sql>>
SET @@local.character_set_connection='latin1';

DELIMITER ||
CREATE PROCEDURE `test`.`x`()
BEGIN
	SELECT CHARSET('x');
END;
||
Query OK, 0 rows affected (0.00 sec)

DELIMITER ;
SET @@local.character_set_connection='utf8';

CALL `test`.`x`();
+--------------+
| CHARSET('x') |
+--------------+
| latin1       |
+--------------+
<</code>>

下面的示例中展示了如何指定函数的参数和返回值的字符集和排序规则：

<<code sql>>
CREATE FUNCTION `test`.`y`(`str` TEXT CHARACTER SET utf8 COLLATE utf8_bin)
	RETURNS TEXT CHARACTER SET latin1 COLLATE latin1_bin
BEGIN
	SET @param_coll = COLLATION(`str`);
	RETURN `str`;
END;

-- 返回值的排序规则:
SELECT COLLATION(`test`.`y`('Hello, planet!'));
+-----------------------------------------+
| COLLATION(`test`.`y`('Hello, planet!')) |
+-----------------------------------------+
| latin1_bin                              |
+-----------------------------------------+

-- 参数的排序规则：
SELECT @param_coll;
+-------------+
| @param_coll |
+-------------+
| utf8_bin    |
+-------------+
<</code>>

== 示例：更改默认的字符集为UTF-8
要改变默认的字符集latin1为UTF-8，需要在配置文件my.cnf中进行如下设置：
<<code>>
[client]
...
default-character-set=utf8
...
[mysql]
...
default-character-set=utf8
...
[mysqld]
...
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
...
<</code>>

注意，选项##default-character-set##是一个客户端选项，而非服务端选项。

== See also
* [[string-literals|String literals]]
* [[cast|CAST()]]
* [[convert|CONVERT()]]

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

设置字符集和排序规则 - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB