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

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

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

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

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

Ниже описывается проблема script на примере.

-- 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_unicode_ci или utf8_general_ci.

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

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

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

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

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

Набор символов utf8mb4 был введен в MySQL 5.5.3 в 2010-03-24.

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

• Возможно, потребуется внести изменения в адаптер базы данных приложения.
• Необходимо внести изменения в 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' символ в 2 строке:

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

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

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

utf8_general_ci также является удовлетворительным для немецкого и французского языков, кроме что 'ß равно' s, а не "Сс. Если это приемлемо для вашего приложения, то вы должны использовать utf8_general_ci, потому что он быстрее. В противном случае используйте utf8_unicode_ci, потому что это более точно.

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

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

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

В моих книгах "ценность" никогда в миллион лет не должна быть равна "valúe".

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

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

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

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

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

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

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

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

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

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

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

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

[Править - почти 6 лет спустя]

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

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

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

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

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

SET NAMES utf8;

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

Для поля casee предназначен для поиска, а не для пользователя, затем используйте поле utf8_general_ce для поиска, а не для пользователя, затем используйте utf8_general_ci или utf8_unicode_ci. Оба нечувствительны к регистру, одно будет соответствовать друг другу ( "ß равно" s ", а не" ss "). Существуют также языковые версии, такие как utf8_german_ci, где совпадение потерь более подходит для указанного языка. я или utf8_unicode_ci. Оба нечувствительны к регистру, одно будет соответствовать друг другу ( "ß равно" s ", а не" ss "). Существуют также языковые версии, такие как utf8_german_ci, где совпадение потерь более подходит для указанного языка. подчеркнуто Гусом, я бы настоятельно рекомендовал использовать либо utf8_unicode_cs (с учетом регистра, строгое соответствие, упорядочение поля ce, предназначенное для поиска, в отличие от согласованного для пользователя, а затем использовать utf8_general_ci или utf8_unicode_ci. Оба не чувствительны к регистру, один будет бесполезным match ('ß равно' s, а не ss). Существуют также языковые версии, такие как utf8_german_ci, где совпадение потерь более подходит для указанного языка. Правильно по большей части) вместо utf8_bin (строгое совпадение, неправильный порядок).

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