Какое сопоставление лучше всего использовать для MySQL с PHP?

Для текстовой информации UTF-8 следует использовать utf8_general_ci, потому что ...

• utf8_bin: сравнение строк по двоичное значение каждого символа в строка

• utf8_general_ci: сравнить строки используя общие языковые правила и с использованием сравнений без учета регистра

a.k.a. это должно сделать поиск и индексацию данных быстрее / эффективнее / полезнее.

Основное отличие - точность сортировки (при сравнении символов на языке) и производительность. Единственный специальный - utf8_bin, предназначенный для сравнения символов в двоичном формате.

utf8_general_ci несколько быстрее utf8_unicode_ci, но менее точен (для сортировки). кодировка utf8 на конкретном языке (например, utf8_swedish_ci) содержат дополнительные языковые правила, которые делают их наиболее точными для сортировки для этих языков. Большую часть времени я использую utf8_unicode_ci (я предпочитаю точность небольшим улучшениям производительности), если у меня нет веских причин предпочитать конкретный язык.

Вы можете прочитать больше о конкретных наборах символов Unicode в руководстве MySQL - http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

На самом деле, вы, вероятно, захотите использовать utf8_unicode_ci или utf8_general_ci.

• utf8_general_ci сортирует, убирая все акценты и сортируя, как если бы это был ASCII
• utf8_unicode_ci использует порядок сортировки Unicode, поэтому сортировка выполняется правильно на большем количестве языков.

Однако, если вы используете это только для хранения английского текста, они не должны отличаться.

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

Пример из документация для кодировки Unicode:

utf8_general_ci also is satisfactory for both German and French, except that ‘ß’ is equal to ‘s’, and not to ‘ss’. If this is acceptable for your application, then you should use utf8_general_ci because it is faster. Otherwise, use utf8_unicode_ci because it is more accurate.

Итак - это зависит от вашей ожидаемой пользовательской базы и от того, насколько вам нужна сортировка верный. Для англоязычной пользовательской базы utf8_general_ci должно быть достаточно, для других языков, например шведского, были созданы специальные сопоставления.

Помните об этой проблеме, которая может возникнуть при использовании utf8_general_ci.

MySQL не будет различать некоторые символы в операторах select, если используется сопоставление utf8_general_ci. Это может привести к очень неприятным ошибкам - особенно, например, когда задействованы имена пользователей. В зависимости от реализации, в которой используются таблицы базы данных, эта проблема может позволить злоумышленникам создать имя пользователя, соответствующее учетной записи администратора.

Эта проблема проявляется, по крайней мере, в ранних версиях 5.x - я не уверен, изменилось ли это поведение позже.

Я не администратор баз данных, но, чтобы избежать этой проблемы, я всегда использую utf8-bin, а не регистронезависимый.

Приведенный ниже сценарий описывает проблему на примере.

-- first, create a sandbox to play in
CREATE DATABASE `sandbox`;
use `sandbox`;

-- next, make sure that your client connection is of the same 
-- character/collate type as the one we're going to test next:
charset utf8 collate utf8_general_ci

-- now, create the table and fill it with values
CREATE TABLE `test` (`key` VARCHAR(16), `value` VARCHAR(16) )
    CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `test` VALUES ('Key ONE', 'value'), ('Key TWO', 'valúe');

-- (verify)
SELECT * FROM `test`;

-- now, expose the problem/bug:
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get BOTH keys here! MySQLs UTF8 collates that are 
-- case insensitive (ending with _ci) do not distinguish between 
-- both values!
--
-- collate 'utf8_bin' doesn't have this problem, as I'll show next:
--

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get just one key now, as you'd expect.
--
-- This problem appears to be specific to utf8. Next, I'll try to 
-- do the same with the 'latin1' charset:
--

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_general_ci

-- next, convert the values that we've previously inserted
-- in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected). This shows 
-- that the problem with utf8/utf8_generic_ci isn't present 
-- in latin1/latin1_general_ci
--
-- To complete the example, I'll check with the binary collate
-- of latin1 as well:

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected).
--
-- Finally, I'll re-introduce the problem in the exact same 
-- way (for any sceptics out there):

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_generic_ci

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- now, re-check for the problem/bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Two keys.
--

DROP DATABASE sandbox;

По сути, это зависит от того, как вы думаете о строке.

Я всегда использую utf8_bin из-за проблемы, обозначенной Гуусом. На мой взгляд, что касается базы данных, строка по-прежнему остается просто строкой. Строка - это количество символов UTF-8. У символа есть двоичное представление, так зачем ему знать язык, который вы используете? Обычно люди создают базы данных для систем с возможностью многоязычных сайтов. В этом весь смысл использования UTF-8 в качестве набора символов. Я немного сторонник чистоты, но думаю, что риск ошибки сильно перевешивает небольшое преимущество, которое вы можете получить при индексировании. Любые правила, относящиеся к языку, должны выполняться на гораздо более высоком уровне, чем СУБД.

В моих книгах «ценность» никогда не должна быть равна «ценности» через миллион лет.

Если я хочу сохранить текстовое поле и выполнять поиск без учета регистра, я буду использовать строковые функции MYSQL с функциями PHP, такими как LOWER () и php-функцией strtolower ().

Для случая, выделенного Гуусом, я настоятельно рекомендую использовать либо utf8_unicode_cs (с учетом регистра, строгое соответствие, по большей части правильный порядок) вместо utf8_bin (строгое соответствие, неправильный порядок).

Если поле предназначено для поиска, а не для поиска пользователя, используйте utf8_general_ci или utf8_unicode_ci. В обоих случаях регистр не учитывается, в одном случае совпадение будет безуспешным («ß» равно «s», а не «ss»). Существуют также версии для конкретных языков, такие как utf8_german_ci, где сопоставление потерь больше подходит для указанного языка.

[Edit - почти 6 лет спустя]

Я больше не рекомендую набор символов «utf8» в MySQL, а вместо этого рекомендую набор символов «utf8mb4». Они почти полностью совпадают, но позволяют использовать немного (намного) больше символов Юникода.

Реально MySQL должен был обновить набор символов «utf8» и соответствующие сопоставления, чтобы они соответствовали спецификации «utf8», но вместо этого нужно было использовать отдельный набор символов и соответствующие сопоставления, чтобы не влиять на обозначение хранилища для тех, кто уже использует свой неполный набор символов «utf8». .

В принятом ответе довольно однозначно предлагается использовать utf8_unicode_ci, и хотя для новых проектов это здорово, я хотел рассказать о своем недавнем противоположном опыте на всякий случай, если это кому-то сэкономит время.

Поскольку utf8_general_ci является сортировкой по умолчанию для Unicode в MySQL, если вы хотите использовать utf8_unicode_ci, вам придется указать его в много мест.

Например, все клиентские соединения имеют не только кодировку по умолчанию (для меня это имеет смысл), но также сопоставление по умолчанию (т.е. сопоставление всегда будет по умолчанию на utf8_general_ci для юникода).

Вероятно, если вы используете utf8_unicode_ci для своих полей, ваши скрипты, которые подключаются к базе данных, необходимо будет обновить, чтобы явно указать желаемое сопоставление - в противном случае запросы с использованием текстовых строк могут завершиться ошибкой, если ваше соединение использует сопоставление по умолчанию.

В результате при преобразовании существующей системы любого размера в Unicode / utf8 вам может потребоваться использовать utf8_general_ci из-за того, как MySQL обрабатывает значения по умолчанию.

Лучше всего использовать набор символов utf8mb4 с сопоставлением utf8mb4_unicode_ci.

Набор символов utf8 поддерживает только небольшое количество кодовых точек UTF-8, около 6% возможных символов. utf8 поддерживает только базовую многоязычную плоскость (BMP). Еще 16 самолетов. Каждый самолет содержит 65 536 знаков. utf8mb4 поддерживает все 17 самолетов.

MySQL усекает 4-байтовые символы UTF-8, что приводит к повреждению данных.

Набор символов utf8mb4 был представлен в MySQL 5.5.3 24.03.2010.

Некоторые из необходимых изменений для использования нового набора символов нетривиальны:

• Возможно, потребуется внести изменения в адаптер базы данных вашего приложения.
• В my.cnf необходимо внести изменения, включая настройку набора символов, сопоставление и переключение innodb_file_format на Barracuda.
• Операторы SQL CREATE могут включать: ROW_FORMAT=DYNAMIC
  • DYNAMIC требуется для индексов на VARCHAR (192) и выше.

ПРИМЕЧАНИЕ. Переход на Barracuda с Antelope может потребовать перезапуска службы MySQL более одного раза. innodb_file_format_max не изменяется до тех пор, пока служба MySQL не будет перезапущена на: innodb_file_format = barracuda.

MySQL использует старый формат файла Antelope InnoDB. Barracuda поддерживает динамические форматы строк, которые вам понадобятся, если вы не хотите сталкиваться с ошибками SQL для создания индексов и ключей после переключения на кодировку: utf8mb4

• # 1709 - Размер столбца индекса слишком велик. Максимальный размер столбца - 767 байт.
• # 1071 - Указанный ключ был слишком длинным; максимальная длина ключа 767 байт

Следующий сценарий был протестирован в MySQL 5.6.17: По умолчанию MySQL настроен так:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope

Остановите службу MySQL и добавьте параметры в существующий my.cnf:

[client]
default-character-set= utf8mb4

[mysqld]
explicit_defaults_for_timestamp = true
innodb_large_prefix = true
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = true

# Character collation
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

Пример оператора SQL CREATE:

CREATE TABLE Contacts (
 id INT AUTO_INCREMENT NOT NULL,
 ownerId INT DEFAULT NULL,
 created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 contact VARCHAR(640) NOT NULL,
 prefix VARCHAR(128) NOT NULL,
 first VARCHAR(128) NOT NULL,
 middle VARCHAR(128) NOT NULL,
 last VARCHAR(128) NOT NULL,
 suffix VARCHAR(128) NOT NULL,
 notes MEDIUMTEXT NOT NULL,
 INDEX IDX_CA367725E05EFD25 (ownerId),
 INDEX created (created),
 INDEX modified_idx (modified),
 INDEX contact_idx (contact),
 PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT=DYNAMIC;

• Вы можете увидеть ошибку # 1709, сгенерированную для INDEX contact_idx (contact), если ROW_FORMAT=DYNAMIC удален из оператора CREATE.

ПРИМЕЧАНИЕ. Изменение индекса на ограничение до первых 128 символов на contact устраняет необходимость использования Barracuda с ROW_FORMAT=DYNAMIC.

INDEX contact_idx (contact(128)),

Также обратите внимание: когда он говорит, что размер поля составляет VARCHAR(128), это не 128 байт. Вы можете использовать 128, 4-байтовые символы или 128, 1-байтовые символы.

Этот оператор INSERT должен содержать 4-байтовый символ poo во второй строке:

INSERT INTO `Contacts` (`id`, `ownerId`, `created`, `modified`, `contact`, `prefix`, `first`, `middle`, `last`, `suffix`, `notes`) VALUES
(1, NULL, '0000-00-00 00:00:00', '2014-08-25 03:00:36', '1234567890', '12345678901234567890', '1234567890123456789012345678901234567890', '1234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678', '', ''),
(2, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '????????????????????????????????????????', '????????????????????????????????????????', '????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????', '', ''),
(3, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '????????????????????????????????????????', '????????????????????????????????????????', '123?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????', '', '');

Вы можете увидеть, сколько места занимает столбец last:

mysql> SELECT BIT_LENGTH(`last`), CHAR_LENGTH(`last`) FROM `Contacts`;
+--------------------+---------------------+
| BIT_LENGTH(`last`) | CHAR_LENGTH(`last`) |
+--------------------+---------------------+
|               1024 |                 128 | -- All characters are ASCII
|               4096 |                 128 | -- All characters are 4 bytes
|               4024 |                 128 | -- 3 characters are ASCII, 125 are 4 bytes
+--------------------+---------------------+

В вашем адаптере базы данных вы можете установить кодировку и сопоставление для вашего соединения:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

В PHP это будет: \PDO::MYSQL_ATTR_INIT_COMMAND

Рекомендации:

• Справочное руководство Mysql 5.6: ограничения таблиц InnoDB
• Как поддерживать полный Unicode в базах данных MySQL

Я нашел эти таблицы сопоставления полезными. http://collation-charts.org/mysql60/. Я не уверен, что используется utf8_general_ci.

Например, вот диаграмма для utf8_swedish_ci. Он показывает, какие символы он интерпретирует как одинаковые. http://collation-charts.org/mysql60/mysql604.utf8_swedish_ci.html

В файле загрузки базы данных добавьте следующую строку перед любой строкой:

SET NAMES utf8;

И твоя проблема должна быть решена.