Настройка наборов символов и сортировок

По умолчанию в MariaDB используется наборы символов latin1 и сортировка latin1_swedish_ci (хотя это может отличаться в некоторых дистрибутивах, см., например, Различия в MariaDB в Debian). И наборы символов, и сортировки могут быть заданы на уровне сервера, вплоть до уровня столбцов, а также для клиентских соединений. При изменении набора символов без указания сортировки всегда используется сортировка по умолчанию для нового набора символов.

Наборы символов и сортировки всегда применяются по принципу «снизу вверх», поэтому столбец без указанной сортировки будет использовать сортировку по умолчанию для таблицы, базы данных и сервера. Это позволяет очень точно контролировать все наборы символов и сортировки, используемые в ваших данных.

Значения сортировки по умолчанию для каждого набора символов можно просмотреть с помощью команды SHOW COLLATION, например, чтобы найти сортировку по умолчанию для набора символов latin2:

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 |
+---------------------+---------+----+---------+----------+---------+

Уровень сервера

Переменную системы character_set_server можно использовать для изменения набора символов по умолчанию на сервере. Ее можно задать как при запуске, так и динамически, с помощью команды SET:

SET character_set_server = 'latin2';

Аналогично, переменная collation_server используется для установки сортировки по умолчанию на сервере.

SET collation_server = 'latin2_czech_cs';

Уровень базы данных

Команды CREATE DATABASE и ALTER DATABASE имеют необязательные пункты набора символов и сортировки. Если они опущены, используются значения по умолчанию сервера.

CREATE DATABASE czech_slovak_names 
  CHARACTER SET = 'keybcs2'
  COLLATE = 'keybcs2_bin';

ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';

Чтобы определить набор символов по умолчанию, используемый базой данных, используйте:

SHOW CREATE DATABASE czech_slovak_names;
+--------------------+--------------------------------------------------------------------------------+
| Database           | Create Database                                                                |
+--------------------+--------------------------------------------------------------------------------+
| czech_slovak_names | CREATE DATABASE `czech_slovak_names` /*!40100 DEFAULT CHARACTER SET keybcs2 */ |
+--------------------+--------------------------------------------------------------------------------+

или, альтернативно, для набора символов и сортировки:

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     |
+--------------+--------------------+----------------------------+------------------------+----------+

Также можно указать только сортировку, и поскольку каждая сортировка применяется только к одному набору символов, соответствующий набор символов будет автоматически указан.

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 */ |
+--------------+----------------------------------------------------------------------------------------------+

Хотя существуют переменные системы character_set_database и collation_database, которые можно устанавливать динамически, они используются для определения набора символов и сортировки по умолчанию для базы данных и должны настраиваться только сервером.

Уровень таблицы

Команды CREATE TABLE и ALTER TABLE поддерживают необязательные пункты набора символов и сортировки, как расширение MariaDB и MySQL для стандартного SQL.

CREATE TABLE english_names (id INT, name VARCHAR(40)) 
  CHARACTER SET 'utf8' 
  COLLATE 'utf8_icelandic_ci';

Если ни набор символов, ни сортировка не указаны, используется значение по умолчанию базы данных. Если указан только набор символов, используется сортировка по умолчанию для этого набора символов. Если указана только сортировка, используется соответствующий набор символов. См. Поддерживаемые наборы символов и сортировки.

ALTER TABLE table_name
 CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

Если сортировка не указана, она устанавливается по умолчанию для данного набора символов. См. Поддерживаемые наборы символов и сортировки.

Для столбцов типа VARCHAR или TEXT, CONVERT TO CHARACTER SET изменяет тип данных при необходимости, чтобы новый столбец был достаточно длинным для хранения всех символов исходного столбца.

Например, столбец типа ascii TEXT требует одного байта на символ, поэтому столбец может содержать до 65 535 символов. Если столбец преобразуется в utf8, на каждый символ может потребоваться 3 байта, поэтому столбец будет преобразован в MEDIUMTEXT, чтобы он мог хранить то же количество символов.

CONVERT TO CHARACTER SET binary преобразует столбцы типа CHAR, VARCHAR и TEXT в BINARY, VARBINARY и BLOB соответственно, и после этого не будет иметь набора символов или не будет затронут будущими CONVERT TO CHARACTER SET операциями.

Чтобы избежать изменения типов данных, вызванных CONVERT TO CHARACTER SET, используйте MODIFY для отдельных столбцов вместо этого. Например:

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;

Уровень столбцов

Наборы символов и сортировки также можно указать для столбцов, которые являются типами символов CHAR, TEXT или VARCHAR. Команды CREATE TABLE и ALTER TABLE поддерживают необязательные пункты набора символов и сортировки для этой цели — в отличие от определения на уровне таблицы, определения на уровне столбца являются стандартным SQL.

CREATE TABLE european_names (
  croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci',
  greek_names VARCHAR(40) CHARACTER SET 'greek');

Если ни сортировка, ни набор символов не указаны, используется значение по умолчанию таблицы. Если указан только набор символов, используется сортировка по умолчанию для этого набора символов, а если указана только сортировка, используется соответствующий набор символов.

Для определения наборов символов и сортировок столбцов можно использовать команду SHOW CREATE TABLE или базу данных INFORMATION SCHEMA.

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

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: 

Имена файлов

С MariaDB 5.1 переменная системы character_set_filesystem управляет интерпретацией имён файлов, заданных как строковые литералы. Это влияет на следующие команды и функции:

• SELECT INTO DUMPFILE
• SELECT INTO OUTFILE
• LOAD DATA INFILE
• LOAD XML
• LOAD_FILE()

Литералы

По умолчанию набор символов и сортировка, используемые для литералов, определяются переменными системы character_set_connection и collation_connection. Однако их также можно явно указать:

[_charset_name]'string' [COLLATE collation_name]

Набор символов строковых литералов, не имеющих указателя набора символов, определяется переменной системы character_set_connection.

Этот запрос:

  SELECT CHARSET('a'), @@character_set_connection;

всегда возвращает одинаковое имя набора символов в обоих столбцах.

character_set_client и character_set_connection обычно (например, во время рукопожатия или после запроса SET NAMES) устанавливаются в равные значения. Однако возможно установить разные значения.

Примеры

Примеры, когда @@character_set_client и @@character_set_connection имеют разные значения, могут быть полезны:

Пример 1:

Предположим, у нас есть база данных utf8 с этой таблицей:

CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO t1 VALUES ('oe'),('ö');

Теперь мы подключаемся к ней с помощью «mysql.exe», который использует набор символов DOS (cp850 на машине Западной Европы), и хотим получить все записи, равные 'ö' в соответствии с правилами немецкого телефонного справочника.

Это возможно с помощью следующего:

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

Это вернет:

+------+
| a    |
+------+
| oe   |
| ö    |
+------+

Это работает следующим образом:

1. Клиент отправляет запрос, используя cp850.
2. Сервер при парсинге запроса создаёт строковый литерал utf8, преобразовывая 'ö' из @@character_set_client (cp850) в @@character_set_connection (utf8).
3. Сервер применяет сортировку «utf8_german2_ci» к этому строковому литералу.
4. Сервер использует utf8_german2_ci для сравнения.

Обратите внимание, если мы перепишем скрипт так:

SET NAMES cp850;
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;

мы получим ошибку:

ERROR 1253 (42000): COLLATION 'utf8_german2_ci' is not valid for CHARACTER SET 'cp850'

потому что:

• на шаге #2, литерал больше не преобразуется в utf8 и создаётся, используя cp850.
• на шаге #3, сервер не может применить utf8_german2_ci к строковому литералу cp850.

Пример 2:

Предположим, у нас есть база данных utf8 и мы снова используем «mysql.exe» с машины Западной Европы.

Мы можем сделать так:

SET @@character_set_client=cp850, @@character_set_connection=utf8;
CREATE TABLE t2 AS SELECT 'ö';

Это создаст таблицу со столбцом типа VARCHAR(1) CHARACTER SET utf8.

Обратите внимание, если мы перепишем запрос так:

SET NAMES cp850;
CREATE TABLE t2 AS SELECT 'ö';

Это создаст таблицу со столбцом типа VARCHAR(1) CHARACTER SET cp850, что, вероятно, не является хорошей идеей.

N

Также N или n можно использовать в качестве префикса для преобразования литерала в национальный набор символов (который в MariaDB всегда является utf8).

Например:

SELECT _latin2 'Müller';
+-----------+
| MĂźller   |
+-----------+
| MĂźller   |
+-----------+

SELECT CHARSET(N'a string');
+----------------------+
| CHARSET(N'a string') |
+----------------------+
| utf8                 |
+----------------------+

SELECT 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci';
+---------------------------------------------------+
| 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci'  |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+

Сохранённые программы и представления

Литералы, которые встречаются в сохранённых программах и представлениях, по умолчанию используют набор символов и сортировку, которые были указаны переменными системы character_set_connection и collation_connection при создании сохранённой программы. Эти значения можно увидеть с помощью команд SHOW CREATE. Чтобы изменить наборы символов, используемые для литералов в существующей сохранённой программе, необходимо удалить и заново создать сохранённую программу.

Для параметров и возвращаемых значений сохранённых процедур можно указать набор символов и сортировку с помощью пунктов CHARACTER SET и COLLATE. До версии 5.5 указание сортировки не поддерживалось.

Следующий пример показывает, что набор символов и сортировка определяются на момент создания:

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       |
+--------------+

Следующий пример показывает, как указать набор символов и сортировку параметров функции:

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;

-- return value's collation:
SELECT COLLATION(`test`.`y`('Hello, planet!'));
+-----------------------------------------+
| COLLATION(`test`.`y`('Hello, planet!')) |
+-----------------------------------------+
| latin1_bin                              |
+-----------------------------------------+

-- parameter's collation:
SELECT @param_coll;
+-------------+
| @param_coll |
+-------------+
| utf8_bin    |
+-------------+

Неправильное сочетание сортировок

CREATE TABLE test.t1 (
   a TEXT CHARACTER SET gbk 
) ENGINE=InnoDB 
CHARSET=latin1
COLLATE=latin1_general_cs;

INSERT INTO t1 VALUES ('user_a');

CREATE VIEW v1 AS
SELECT a <> 0xEE5D FROM t1;

SELECT * FROM v1;
Error 1267 (HY000): Illegal mix of collations (gbk_chinese_ci, IMPLICIT)
and (latin_swedish_ci, COERCIBLE) for operation

Пример: Изменение кодировки по умолчанию на UTF-8

Для изменения кодировки по умолчанию с latin1 на UTF-8 необходимо указать следующие настройки в файле конфигурации my.cnf.

[mysql]
...
default-character-set=utf8mb4
...
[mysqld]
...
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
...

Обратите внимание, что опция default-character-set является опцией клиента, а не сервера.

См. также

• Строковые литералы
• CAST()
• CONVERT()