Мне интересно, есть ли «лучший» выбор для сортировки в MySQL для общего веб-сайта, на котором вы не уверены в 100% того, что будет введено? Я понимаю, что все кодировки должны быть такими же, как MySQL, Apache, HTML и что-нибудь внутри PHP.
Раньше я устанавливал PHP для вывода в «UTF-8», но какая сортировка делает это в MySQL? Я думаю, что это один из UTF-8, но раньше я использовал utf8_unicode_ci , utf8_general_ci и utf8_bin .
Основное отличие заключается в точности сортировки (при сравнении символов на языке) и производительности. Единственный специальный – utf8_bin, который предназначен для сравнения символов в двоичном формате.
utf8_general_ci несколько быстрее, чем utf8_unicode_ci , но менее точный (для сортировки). Кодирование специфического языка utf8 (например, utf8_swedish_ci ) содержит дополнительные языковые правила, которые делают их наиболее точными для сортировки для этих языков. В большинстве случаев я использую utf8_unicode_ci (я предпочитаю точность для небольших улучшений производительности), если у меня нет веских оснований предпочитать конкретный язык.
Вы можете больше узнать о конкретных наборах символов Юникода в руководстве по MySQL – http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html
Будьте очень, очень осведомлены об этой проблеме, которая может возникнуть при использовании utf8_general_ci .
MySQL не будет различать некоторые символы в операторах select, если utf8_general_ci сортировка 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_unicode_ci или utf8_general_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 символов utf8mb4 был введен в MySQL 5.5.3 в 2010-03-24.
Некоторые из необходимых изменений для использования нового набора символов не являются тривиальными:
ROW_FORMAT=DYNAMIC
ПРИМЕЧАНИЕ. Переход на Barracuda из Antelope может потребовать перезапуска службы MySQL более одного раза. innodb_file_format_max не изменяется до тех пор, пока служба MySQL не будет перезапущена до: innodb_file_format = barracuda .
MySQL использует старый формат Antelope InnoDB. Barracuda поддерживает динамические форматы строк, которые вам понадобятся, если вы не хотите ударять SQL-ошибки для создания индексов и ключей после переключения на кодировку: utf8mb4
В 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;
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
Рекомендации:
Коллации влияют на сортировку данных и на то, как строки сравниваются друг с другом. Это означает, что вы должны использовать сопоставление, которое ожидает большинство ваших пользователей.
Пример из документации :
utf8_general_ciтакже является удовлетворительным как для немецкого, так и для французского, за исключением того, что «ß» равно «s», а не «ss». Если это приемлемо для вашего приложения, вы должны использовать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 : сравнивать строки, используя общие правила языка и используя нечувствительные к регистру сравнения
он также должен сделать поиск и индексирование данных быстрее / эффективнее / полезнее.
В принятом ответе довольно однозначно предлагается использовать 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, где совпадение потерь более подходит для указанного языка.
Я нашел эти диаграммы сортировки полезными. 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;
И ваша проблема должна быть решена.