Какая наилучшая сортировка для MySQL с PHP?

Ответ 1

Основное отличие - точность сортировки (при сравнении символов на языке) и производительности. Единственный специальный - 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

Ответ 2

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

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

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

Ответ 3

Быть очень, очень осознавая эту проблему, которая может возникнуть при использовании 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;

Ответ 4

Лучше всего использовать набор символов 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

Ответ 5

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

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

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

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

Ответ 6

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

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

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

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

Ответ 7

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

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

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

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

Ответ 8

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

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

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

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

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

Ответ 9

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

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

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

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

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

Ответ 10

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

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

Ответ 11

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

SET NAMES utf8;

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

Ответ 12

Для поля 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, где совпадение потерь более подходит для указанного языка.