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

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

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

Тем не менее, если вы планируете хранить только английский текст, разница между ними, скорее всего, не будет заметна.

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

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

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

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

Ниже приведен пример, демонстрирующий проблему.

-- Сначала создаем песочницу для экспериментов
CREATE DATABASE `sandbox`;
USE `sandbox`;

-- Далее, убедитесь, что ваше клиентское соединение имеет тот же 
-- тип символов/сортировки, что и то, что мы будем тестировать:
SET NAMES utf8 COLLATE utf8_general_ci;

-- Теперь создаем таблицу и заполняем ее значениями
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');

-- (проверка)
SELECT * FROM `test`;

-- Теперь выявим проблему/ошибку:
SELECT * FROM test WHERE `value` = 'value';

--
-- Обратите внимание, что оба ключа возвращаются! Сравнение 
-- в сортировках MySQL UTF8, которые являются нечувствительными к регистру 
-- (оканчиваются на _ci), не различает оба значения!
--
-- Сортировка 'utf8_bin' этой проблемы не имеет, как я покажу далее:
--

-- Сначала сбрасываем тип символов/сортировки клиентского соединения
SET NAMES utf8 COLLATE utf8_bin;

-- Затем конвертируем значения, которые мы ранее вставили в таблицу
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

-- Теперь снова проверим наличие ошибки
SELECT * FROM test WHERE `value` = 'value';

--
-- Обратите внимание, что теперь возвращается только один ключ, как и ожидалось.
--
-- Эта проблема, похоже, специфична для utf8. Далее я попробую 
-- сделать то же самое с кодировкой 'latin1':
--

-- Сначала сбрасываем тип символов/сортировки клиентского соединения
SET NAMES latin1 COLLATE latin1_general_ci;

-- Затем конвертируем ранее вставленные значения в таблице
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

-- Теперь снова проверим наличие ошибки
SELECT * FROM test WHERE `value` = 'value';

--
-- Снова возвращается только один ключ (ожидается). Это показывает, 
-- что проблема с utf8/utf8_general_ci отсутствует в latin1/latin1_general_ci.
--
-- Чтобы завершить пример, я проверю с двоичной сортировкой 
-- latin1:

-- Сначала сбрасываем тип символов/сортировки клиентского соединения
SET NAMES latin1 COLLATE latin1_bin;

-- Затем конвертируем ранее вставленные значения в таблице
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

-- Теперь снова проверим наличие ошибки
SELECT * FROM test WHERE `value` = 'value';

--
-- Снова возвращается только один ключ (ожидается).
--
-- Наконец, я вновь введу проблему точно тем же способом 
-- (для скептиков):

-- Сначала сбрасываем тип символов/сортировки клиентского соединения
SET NAMES utf8 COLLATE utf8_general_ci;

-- Затем конвертируем ранее вставленные значения в таблице
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Теперь снова проверим наличие проблемы/ошибки
SELECT * FROM test WHERE `value` = 'value';

--
-- Два ключа.
--

DROP DATABASE sandbox;

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

В сущности, это зависит от того, как вы воспринимаете строку.

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

По моему мнению, "value" не должно ни в коем случае быть равно "valúe".

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

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

• utf8_bin сравнивает строки по бинарному значению каждого символа, что может быть более медленным и менее удобным для языка.
• utf8_general_ci осуществляет сравнение строк с учетом общих языковых правил и без учета регистра.

Это означает, что использование utf8_general_ci сделает поиск и индексацию данных быстрее, более эффективными и более удобными для пользователя.