MySQL различные UTF – utf8_bin и utf8_general_ci » Blog Archive

MySQL различные UTF – utf8_bin и utf8_general_ci

Май 19th, 2010 оставить комментарий

Чем же отличаются эти два collate для таблиц?
utf8_bin – использует сравнения по двоичным значениям каждого символа
utf8_general_ci – использует сравнения на основе языковых особенностей и игнорирует регистр символов.

Для наглядности приведем небольшой эксперимент.

CREATE TABLE peoples (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
first_name VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
last_name VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
) ENGINE = INNODB CHARACTER SET utf8;

INSERT INTO peoples (first_name, last_name) VALUES ('Петя', 'Иванов'), ('андрей', 'иванов'), ('Иван', 'Петров'), ('Андрей', 'Сидоров');
INSERT INTO peoples (first_name, last_name) VALUES ('Петр', 'Большой'), ('Пётр', 'Большои');

Получаем такую вот табличку:

Из-за разности сопоставления символов получаются различия в сортировке, если делать её по полю с utf8_general_ci получаем следующее:

И не совсем ожидаемым образом отрабатывает запрос с сортировкой по полю utf8_bin

Помимо всего прочего в utf8_general_ci некоторые буквы считаются идентичными, например ё = е:

mysql> SELECT * FROM peoples WHERE first_name='Петр';
+----+------------+----------------+
| id | first_name | last_name |
+----+------------+----------------+
| 5 | Петр | Большой |
| 6 | Пётр | Большои |
+----+------------+----------------+

Отсюда же может появиться и проблема с уникальными ключами. В приведенной структуре уникальный ключ по полю first_name создать не получится, т. к. дублируются значения «Андрей» и «андрей», а так же не совсем ожидаемые «Пётр» и «Петр».

mysql> CREATE UNIQUE INDEX uq_peoples_first_name ON peoples (first_name);
ERROR 1062 (23000): Duplicate entry 'Пётр' FOR KEY 3

Зато по полю last_name создастся без проблем, в нем все значения различающиеся:

mysql> CREATE UNIQUE INDEX uq_peoples_last_name ON peoples (last_name);
Query OK, 6 rows affected (0.06 sec)
Records: 6 Duplicates: 0 Warnings: 0

С проблемой уникальности я столкнулся при переводе старой таблички с пользователями из cp1251_general_ci в utf8_general_ci – логины «Ёлка» и «елка» перестали быть уникальными. Можно было создать эталонное поле utf8_bin (используемое только при авторизации) со значением приведенным к скажем нижнему регистру.

Ну и мануал: Unicode Character Sets

Tags: mysql

One Response to “MySQL различные UTF – utf8_bin и utf8_general_ci”

NotMyName Says:
Июль 11th, 2010 at 08:15
Спасибо. Все просто и понятно.

заметки на полях