Объединения должны быть для правильного набора символов

 

Каждый набор символов имеет одно или большее количество объединений, но каждое объединение связано с одним и только одним набором символов. Следовательно, следующая инструкция вызывает сообщение об ошибке, потому что объединение latin2_bin не допустимо с набором символов latin1:

 

mysql> SELECT _latin1 'x' COLLATE latin2_bin;

ERROR 1253 (42000): COLLATION 'latin2_bin' is not valid

for CHARACTER SET 'latin1'

 

Пример эффекта объединения

 

Предположите, что столбец X в таблице T имеет эти значения столбца latin1:

Muffler

M├╝ller

MX Systems

MySQL

 

Предположите также, что значения столбца получены, используя следующую инструкцию:

SELECT X FROM T ORDER BY X COLLATE collation_name;

 

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

 

latin1_swedish_ci

latin1_german1_ci

latin1_german2_ci

 

Muffler

Muffler

M├╝ller

 

MX Systems

M├╝ller

Muffler

 

M├╝ller

MX Systems

MX Systems

 

MySQL

MySQL

MySQL

Символ, который вызывает различные порядки сортировки в этом примере: U с двумя точками сверху, который в Германии известен как U‑umlaut.

 

Первый столбец показывает результат SELECT, использующего правило объединения Swedish/Finnish, которое говорит, что U‑umlaut сортируется после Y.

 

Второй столбец показывает результат SELECT, использующего правило German DIN‑1, которое говорит, что U‑umlaut сортируется с U.

 

Третий столбец показывает результат SELECT, использующего правило German DIN‑2 rule, которое говорит, что U‑umlaut сортируется с UE.

 

Операции, на которые воздействует поддержка набора символов

 

Этот раздел описывает операции, которые берут во внимание информацию о наборе символов.

 

Строки результата

 

MySQL имеет много операторов и функций, которые возвращают строку. Этот раздел отвечает на вопрос: каков набор символов и объединение у такой строки?

 

Для простых функций, которые берут строку ввода и возвращают строковый результат как вывод, набор символов и объединение вывода такие же, как таковые у входного значения. Например, UPPER(X) возвращает строку, чья символьная строка и объединение являются такими же, как X. Это относится к INSTR(), LCASE(), LOWER(), LTRIM(), MID(), REPEAT(), REPLACE(), REVERSE(), RIGHT(), RPAD(), RTRIM(), SOUNDEX(), SUBSTRING(), TRIM(), UCASE() и UPPER().

 

Обратите внимание: функция REPLACE(), в отличие от всех других функций, всегда игнорирует объединение строкового ввода и выполняет чувствительное к регистру сравнение.

 

Если строковый ввод или функциональный результат является двоичной строкой, она не имеет никакого набора символов или объединения. Это может быть проверено, используя функции CHARSET() и COLLATION(), которые вернут binary, чтобы указать, что их параметр двоичная строка:

 

mysql> SELECT CHARSET(BINARY 'a'), COLLATION(BINARY 'a');

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

| CHARSET(BINARY 'a') | COLLATION(BINARY 'a') |

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

| binary | binary |

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

 

Для операций, которые объединяют многостроковые вводы и возвращают одиночный строковый вывод, правила соединения частей стандарта SQL дают определение объединения результата:

 

Если явно задан COLLATE X, использовать X.

 

Если явно заданы COLLATE X и COLLATE Y, выдать ошибку.

 

Иначе, если все объединения X, используовать X.

 

Иначе, результат не имеет никакого объединения вообще.

 

Например, с CASE … WHEN a THEN b WHEN b THEN c COLLATE X END возникающее в результате объединение X. То же самое для UNION, ||, CONCAT(), ELT(), GREATEST(), IF() и LEAST().

 

Для операций, которые преобразовываются в символьные данные, набор символов и объединение строк результата операции определены переменными системы character_set_connection и collation_connection. Это применяется только для CAST(), CONV(), FORMAT(), HEX() и SPACE().

 

Если Вы не уверены относительно набора символов или объединения результата, возвращенного строковой функцией, Вы можете использовать функцию CHARSET() или COLLATE(), чтобы выяснить:

 

mysql> SELECT USER(), CHARSET(USER()), COLLATION(USER());

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

| USER() | CHARSET(USER()) | COLLATION(USER()) |

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

| test@localhost | utf8 | utf8_general_ci |

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

 

CONVERT() и CAST()

 

CONVERT() обеспечивает способ преобразовать данные между различными наборами символов. Синтаксис:

CONVERT(expr USING transcoding_name)

 

В MySQL имена перекодировки такие же, как соответствующие имена наборов символов.

 

Примеры:

SELECT CONVERT(_latin1'M├╝ller' USING utf8);

INSERT INTO utf8table (utf8column)

SELECT CONVERT(latin1field USING utf8) FROM latin1table;

 

CONVERT(… USING …) выполнено согласно стандарту SQL.

 

Вы можете также использовать CAST(), чтобы преобразовать строку в иной набор символов. Синтаксис:

CAST(character_string AS

character_data_type

CHARACTER SET charset_name)

 

Пример:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);

 

Если Вы используете CAST() без того, чтобы определить CHARACTER SET, возникающие в результате набор символов и объединение определены переменными системы character_set_connection и collation_connection. Если Вы используете CAST() с CHARACTER SET X, возникающие в результате набор символов и объединение X и заданное по умолчанию объединение для X.

 

Вы не можете использовать предложение COLLATE внутри CAST(), но Вы можете использовать это снаружи. То есть CAST(… COLLATE …) запрещено, но CAST(…) COLLATE … допустимо.

 

Пример:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

 

10.6.3. Инструкции SHOW и INFORMATION_SCHEMA

 

Несколько инструкций SHOW обеспечивают дополнительную информацию о наборе символов. Они включают SHOW CHARACTER SET, SHOW COLLATION, SHOW CREATE DATABASE, SHOW CREATE TABLE и SHOW COLUMNS. Эти инструкции описаны здесь кратко.

 

INFORMATION_SCHEMA имеет несколько таблиц, которые содержат информацию, подобную отображаемой инструкциями SHOW. Например, таблицы CHARACTER_SETS и COLLATIONS содержат информацию, отображаемую SHOW CHARACTER SET и SHOW COLLATION.

 

Команда SHOW CHARACTER SET показывает все доступные наборы символов. Требуется факультативное предложение LIKE, которое указывает, которым именам набора символов соответствовать. Например:

 

mysql> SHOW CHARACTER SET LIKE 'latin%';

+‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

| Charset | Description | Default collation | Maxlen |

+‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

| latin1 | cp1252 West European | latin1_swedish_ci | 1 |

| latin2 | ISO 8859‑2 Central European | latin2_general_ci | 1 |

| latin5 | ISO 8859‑9 Turkish | latin5_turkish_ci | 1 |

| latin7 | ISO 8859‑13 Baltic | latin7_general_ci | 1 |

+‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

 

Вывод SHOW COLLATION включает все доступные наборы символов. Требуется факультативное предложение LIKE, которое указывает, которым именам объединения соответствовать. Например:

 

mysql> SHOW COLLATION LIKE 'latin1%';

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+‑‑‑‑+‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+

| Collation | Charset | Id | Default | Compiled | Sortlen |

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+‑‑‑‑+‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+

| latin1_german1_ci | latin1 | 5 | | | 0 |

| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 |

| latin1_danish_ci | latin1 | 15 | | | 0 |

| latin1_german2_ci | latin1 | 31 | | Yes | 2 |

| latin1_bin | latin1 | 47 | | Yes | 0 |

| latin1_general_ci | latin1 | 48 | | | 0 |

| latin1_general_cs | latin1 | 49 | | | 0 |

| latin1_spanish_ci | latin1 | 94 | | | 0 |

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+‑‑‑‑+‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+

 

SHOW CREATE DATABASE отображает инструкцию CREATE DATABASE, которая создала эту базу данных:

 

mysql> SHOW CREATE DATABASE test;

+‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

| Database | Create Database |

+‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

| test | CREATE DATABASE `test` /*!40100 DEFAULT |

| | CHARACTER SET latin1 */ |

+‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

 

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

 

SHOW CREATE TABLE подобна, но отображает инструкцию CREATE TABLE, чтобы создать данную таблицу. Определения столбца указывают любые спецификации набора символов, и параметры таблицы включают информацию набора символов.

 

Инструкция SHOW COLUMNS отображает объединения столбцов таблицы когда вызывается как SHOW FULL COLUMNS. Столбцы с типами данных CHAR, VARCHAR или TEXT имеют объединения. Числовые и другие не‑символьные типы не имеют никакого объединения (обозначены NULL как значение Collation). Например:

 

mysql> SHOW FULL COLUMNS FROM person\G

*************************** 1. row ***************************

Field: id

Type: smallint(5) unsigned

Collation: NULL

Null: NO

Key: PRI

Default: NULL

Extra: auto_increment

Privileges: select, insert, update, references

Comment:

*************************** 2. row ***************************

Field: name

Type: char(60)

Collation: latin1_swedish_ci

Null: NO

Key:

Default:

Extra:

Privileges: select, insert, update, references

Comment:

 

Набор символов не отображается, но подразумевается именем объединения.

 

Поддержка Unicode

 

MySQL 5.1 поддерживает два набора символов для сохранения данных Unicode:

 

ucs2, набор символов UCS‑2 Unicode.

 

utf8, набор символов UTF‑8 Unicode.

 

В UCS‑2 (двоичное представление Unicode) каждый символ представляется двухбайтным Unicode‑кодом со старшим байтом сначала. Например: LATIN CAPITAL LETTER A имеет код 0x0041, и это сохранено как двухбайтовая последовательность 0x00 0x41. CYRILLIC SMALL LETTER YERU (Unicode 0x044B) сохранена как двухбайтовая последовательность 0x04 0x4B. Для получения символов Unicode и их кодов, пожалуйста, обратитесь к Unicode Home Page (http://www.unicode.org).

 

В настоящее время UCS‑2 не может использоваться как набор символов пользователя, это означает, что SET NAMES 'ucs2' не работает.

 

UTF‑8 (трансформируемое представление Unicode) представляет собой альтернативный способ сохранить Unicode данные. Это выполнено согласно RFC 3629. Идея относительно UTF‑8 состоит в том, что различные символы Unicode, используя последовательности байтов различных длин:

 

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

 

Большинство европейских и ближневосточных символов вписываются в двухбайтовую последовательность: расширенные латинские символы (с тильдой, апострофом, острые, умлауты и другие диакритические знаки), кириллица, греческие, армянские, еврейские, арабские, сирийские и прочие.

 

Корейские, китайские и японские иероглифы использует трехбайтовые последовательности.

 

RFC 3629 описывает последовательности кодирования, которые берут от одного до четырех байтов. В настоящее время MySQL‑поддержка для UTF‑8 не включает последовательности с четырьмя байтами. Старый стандарт для кодирования UTF‑8 задан RFC 2279 и описывает UTF‑8‑последовательности, которые берут от одного до шести байтов. RFC 3629 объявляет RFC 2279 устаревшим, по этой причине последовательности с пятью и шестью байтами больше не используются.

 

Совет: чтобы сохранять пробел а UTF‑8, используйте VARCHAR вместо CHAR. Иначе MySQL должен резервировать по три байта для каждого символа в столбце CHAR CHARACTER SET utf8, потому что это максимальная возможная длина. Например, MySQL должен резервировать 30 байтов для столбца CHAR(10) CHARACTER SET utf8.

 

10.8. UTF‑8 для метаданных

 

Метаданные представляют собой такие данные, которые описывают базу данных в противоположность данным, являющимся содержанием базы данных. Таким образом, имена столбцов, базы данных, пользователей, версия и большинство строк‑результатов SHOW как раз и являются именно метаданными. Это также истинно для содержания таблиц в INFORMATION_SCHEMA, потому что те таблицы по определению содержат информацию относительно объектов базы данных.

 

Представление метаданных должно удовлетворять эти требованиям:

 

Все метаданные должны быть в том же самом наборе символов. Иначе, ни команды SHOW, ни инструкции SELECT для таблиц в INFORMATION_SCHEMA не работали бы правильно, потому что различные строки в том же самом столбце результатов этих операций будут в различных наборах символов.

 

Метаданные должны включить все символы во все языки. Иначе пользователи не способны называть столбцы и таблицы, использующие их собственные языки.

 

Чтобы удовлетворять обоим требованиям, MySQL сохраняет метаданные в наборе символов Unicode, а именно в UTF‑8. Это не вызывает никаких сбоев, если Вы никогда не используете не латинские или символы с диакритическим знаком. Но если Вы это делаете, Вы должны знать, что метаданные находятся в UTF‑8.

 

Требования метаданных означают, что возвращаемые значения функций USER(), CURRENT_USER(), SESSION_USER(), SYSTEM_USER(), DATABASE() и VERSION() имеют по умолчанию набор символов UTF‑8.

 

Сервер устанавливает переменную системы character_set_system к имени набора символов метаданных:

mysql> SHOW VARIABLES LIKE 'character_set_system';

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑+

| Variable_name | Value |

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑+

| character_set_system | utf8 |

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑+

 

Хранение метаданных, использующих Unicode, не означает, что сервер возвращает заголовки столбцов и результатов функции DESCRIBE в наборе символов character_set_system по умолчанию. Когда Вы используете SELECT column1 FROM t, имя column1 непосредственно возвращено в наборе символов, определенном значением переменной системы character_set_results, которая имеет значение по умолчанию latin1. Если Вы хотите, чтобы сервер передал результаты метаданных в ином наборе символов, используйте инструкцию SET NAMES, чтобы выполнять преобразование набора символов. SET NAMES устанавливает character_set_results и другие связанные переменные системы. В качестве альтернативы программа пользователя может выполнять преобразование после получения результата с сервера. Это более эффективно для пользователя, но эта опция не всегда доступна для всей клиентуры.

 

Если character_set_results установлен в NULL, никакое преобразование не выполняется и метаданные возвращаются, используя первоначальный набор символов (набор, обозначенный character_set_system).

 

Сообщения об ошибках, возвращенные сервером, преобразованы в набор символов пользователя автоматически, как в случае с метаданными.

 

Если Вы используете (например) функцию USER() для сравнения или назначения внутри одиночной инструкции, можете не волноваться. MySQL выполняет автоматическое преобразование для Вас.

SELECT * FROM Table1 WHERE USER() = latin1_column;

 

Это работает потому, что содержание latin1_column автоматически преобразовано в UTF‑8 перед сравнением.

INSERT INTO Table1 (latin1_column) SELECT USER();

 

Это работает потому, что содержание USER() автоматически преобразовано в latin1 перед назначением. Автоматическое преобразование полностью все же не выполнено, но должно работать правильно в более поздней версии.

 

Хотя автоматическое преобразование не в SQL стандарте, документ SQL‑стандарта говорит, что каждый набор символов (в терминах обеспечиваемых символов) подмножество Unicode. Поэтому объединение для Unicode может применяться для сравнения с не‑Unicode строками.

 

10.9. Преобразование набора символов столбца

 

Чтобы преобразовывать двоичный или не двоичный строковый столбец, чтобы использовать специфический набор символов, примените ALTER TABLE. Для успешного преобразования одно из следующих условий должно примениться:

 

Если столбец имеет двоичный тип данных (BINARY, VARBINARY, BLOB), все значения, которые он содержит, должны быть закодированы, используя одиночный набор символов. Если Вы используете двоичный столбец, чтобы сохранить информацию в нескольких наборах символов, MySQL не имеет никакого способа узнать, который набор символов применять и не может преобразовывать данные правильно.

 

Если столбец имеет не двоичный тип данных (CHAR, VARCHAR, TEXT), содержание должно быть закодировано в наборе символов столбца, а не в некотором другом наборе символов. Если содержание закодировано в ином наборе символов, Вы можете преобразовывать столбец, чтобы сначала использовать двоичный тип данных, а затем к не двоичному столбцу с желательным набором символов.

 

Предположите, что таблица t имеет двоичный столбец col1, определенный как BINARY(50). При условии, что информация в столбце закодирована, используя одиночный набор символов, Вы можете преобразовывать это в не двоичный столбец, который имеет нужный набор символов. Например, если col1 содержит двоичные символы представления данных в греческом наборе символов (greek), Вы можете преобразовывать это следующим образом:

 

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET greek;

 

Предположите, что таблица t имеет не двоичный столбец col1, определенный как CHAR(50) CHARACTER SET latin1, но Вы хотите преобразовывать это, чтобы использовать utf8 так, чтобы Вы могли сохранять значения из многих языков. Следующая инструкция выполняет это:

 

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8;

 

Преобразование может быть с потерями, если столбец содержит символы, которые не содержатся в обоих наборах символов.

 

Специальный случай происходит, если Вы имеете старые таблицы из MySQL 4.0 или ранее, где не двоичный столбец содержит значения, которые фактически закодированы в наборе символов, отличном от заданного по умолчанию набора символов сервера. Например, прикладная программа могла бы сохранить значения sjis в столбце даже при том, что заданный по умолчанию набор символов MySQL latin1. Возможно преобразовать столбец, чтобы использовать соответствующий набор символов, но дополнительный шаг требуется. Предположите, что заданный по умолчанию набор символов сервера был latin1, а col1 определен как CHAR(50), но содержит значения в sjis. Первый шаг должен преобразовать столбец в двоичный тип данных, который удаляет существующую информацию набора символов без того, чтобы выполнить любое символьное преобразование:

 

ALTER TABLE t MODIFY col1 BINARY(50);

 

Следующий шаг должен преобразовать столбец в не двоичный тип данных с соответствующим набором символов:

 

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET sjis;

 

Эта процедура требует, чтобы таблица не изменилась с инструкциями типа INSERT или UPDATE после обновления до MySQL 4.1 или позже. В этом случае MySQL сохранил бы новые значения в столбце, использующем latin1, и столбец будет содержать смесь значений sjis и latin1, а значит не может быть преобразован правильно.

 

Если Вы определили атрибуты при создании столбца первоначально, Вы должны также определить их, при изменении таблицы с помощью ALTER TABLE. Например, если Вы определили NOT NULL и явное значение DEFAULT, Вы должны также обеспечить их в инструкции ALTER TABLE. Иначе возникающее в результате определение столбца не будет включать эти атрибуты.