Какие параметры сортировки лучше всего использовать для MySQL с PHP? [закрытый]

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

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

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

Я не DBA, но чтобы избежать этой проблемы, я всегда иду с 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 сортирует путем удаления всех акцентов и сортировки, как если бы это был ASCII
• utf8_unicode_ci использует порядок сортировки Unicode, поэтому он правильно сортируется на нескольких языках

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

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

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

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

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

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

• возможно, потребуется внести изменения в адаптер базы данных приложения.
• изменения должны быть внесены в мой.cnf, включая установку набора символов, параметры сортировки и переключение innodb_file_format на Barracuda
• инструкции SQL CREATE возможно, потребуется включить: ROW_FORMAT=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 и добавьте параметры к существующим мой.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исключает требование для использования Барракуды с 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', '', '');

вы можете увидеть объем пространства, используемого :

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 из-за проблемы, выделенной Guus. На мой взгляд, Что касается базы данных, строка по-прежнему является просто строкой. Строка-это число символов UTF-8. Символ имеет двоичное представление, так почему он должен знать язык, который вы используете? Как правило, люди будут создавать базы данных для систем с областью для многоязычных сайтов. Вот и все точка использования UTF-8 в качестве набора символов. Я немного пуреист, но я думаю, что риски ошибки сильно перевешивают небольшое преимущество, которое вы можете получить при индексации. Любые правила, связанные с языком, должны выполняться на гораздо более высоком уровне, чем СУБД.

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

Если я хочу сохранить текстовое поле и выполнить поиск без учета регистра, я буду использовать строковые функции MYSQL с функциями PHP, такими как LOWER() и функция php 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 обрабатывает значения по умолчанию.

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

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

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

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

SET NAMES utf8;

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