Сортировать с использованием символов utf в mysql или php? лучшие решения

Используя MySQL, я выбираю список песен на испанском языке, которые я хотел бы отсортировать. Вот список имен, возвращаемых запросом:

  • ¡Дечиревилья!
  • Альгамбра
  • 123 пасито
  • Африка
  • Арроз
  • Децир

Отсортированный список должен выглядеть так:

  • 123 пасито
  • Африка
  • Альгамбра
  • Арроз
  • ¡Дечиревилья!
  • Децир

После всех исследований, которые я прочитал, я пришел к выводу, что нет разумного способа добиться этого с помощью MySQL. Я пробовал сортировку, набор символов и т. д., но символы ¡, ? и т. д. не могут быть отсортированы в соответствии с моим желаемым результатом. Даже Á не отсортирован так, как я хочу...

Вопрос 1: Это разумный вывод?

Я считаю, что единственный способ добиться этого - передать результаты в массив в php, а затем отсортировать массив с помощью пользовательской функции... все это с помощью функции usort (нужно сортировать по значению, и мне все равно на поддержание ключевая ассоциация). Что-то похожее на это:

function normalize($a, $b) {
  if ($a == $b) {
     return 0;
  }

  return ($a < $b) ? -1 : 1;
}


$tracks = array();

while ($row = $result->fetch_assoc()) {
    $tracks[] = $row;
}

usort($tracks, 'normalize');

Вопрос 2: Это лучший способ добиться пользовательской сортировки?

Вот где я бью стену:

Вопрос 3: Я понятия не имею, как создать функцию нормализации для сортировки имен в соответствии с моими потребностями. Как игнорировать определенные символы (¡, ?, ', !, ¿) и как заменить другие символы естественным эквивалентом (Á -> A, É -> E и т. д.). Я считаю, что при игнорировании определенных символов и заменяя другие, я могу добиться сортировки, которую я ищу...

Вопрос 4: Все это имеет смысл? Я на правильном пути?

Заранее спасибо за все ваши советы. Марко


mysql php sorting collation
person Marco    schedule 04.03.2011    source источник

Ответы (2)


arrow_upward
1
arrow_downward

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

Выполнение искаженной сортировки на стороне клиента (то есть в PHP, а не в базе данных) будет не таким быстрым, как в базе данных. Этот подход также потерпит неудачу, как только вам придется добавить в запрос предложения LIMIT и OFFSET. Я не уверен, что пользовательские сопоставления делают правильную вещь для MAX() подобных функций, но выполнение искаженного сопоставления в PHP, безусловно, не будет, если вы не хотите вытащить всю таблицу, отсортировать ее, а затем получить только одну запись.

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

Другой вариант, если вы не хотите создавать собственную сортировку, — создать в таблице искусственный столбец, который правильно сортирует. Вы можете использовать функцию normalize() в PHP-стране (что-то вроде функции Джейкоба было бы разумной отправной точкой) и сохранить результат в базе данных в виде столбца с именем, скажем, sortable_title; тогда ORDER BY sortable_title сделает свое дело. Вам нужна функция normalize() PHP, которая создает такой список (без знаков препинания, все строчные буквы, без акцентов,...):

  • 123 пасито
  • Африка
  • Альгамбра
  • Арроз
  • дечиревилья
  • децир

Так что простая ASCII-бетическая сортировка будет делать правильные вещи. Конечно, вам придется инициализировать sortable_title при выполнении INSERT и регенерировать его во время UPDATE, но это должно быть довольно просто, если ваш код правильно инкапсулирован.

Вопрос 4: Думаю, я не соглашусь с Джейкобом и скажу, что вы идете не в правильном направлении, удаляя сопоставление из базы данных. Я не говорю, что вы полностью сбились с пути, но вам лучше позволить MySQL справиться с сортировкой, даже если вы можете в конечном итоге помочь ему с чем-то вроде хака sortable_title, описанного выше.

person mu is too short    schedule 04.03.2011
comment
могу ли я добавить свою собственную сортировку в MySQL, если я нахожусь на общем хосте? - person Marco; 05.03.2011
comment
@Marco: Это зависит от хостинг-провайдера, но я, вероятно, склоняюсь к тому, что нет. Если вы не можете, то подход sortable_title справится с задачей почти так же хорошо. - person mu is too short; 05.03.2011
comment
Я только что закончил программировать оба метода, и метод с sortable_title намного, намного быстрее. Я добавил таймер и средние результаты для решения mysql: 0,009 секунды... решение php: 0,12 секунды. Странно то, что я кэшировал список (используя метод ob_start()..), и кэширование заметно медленнее... Я думаю, в этом конкретном случае открытие кэшированного файла происходит медленнее, чем выполнение запроса. .. Заставляет задуматься, что кеширование в php не всегда нужно... - person Marco; 05.03.2011
comment
@Marco: Хорошо, ты даже проверил, что работает лучше! Базы данных, как правило, выполняют множество массовых сравнений и сортировок, поэтому часть MySQL, вероятно, сильно оптимизирована вплоть до расположения байтов в памяти и на диске; разница в производительности на порядок меня не сильно удивляет: базы данных хороши для обработки больших объемов данных, для этого они и нужны. - person mu is too short; 05.03.2011

arrow_upward
0
arrow_downward

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

Вопрос 3. Возможно, стоит преобразовать строку в ее эквивалент ASCII, используя iconv. Который может конвертировать UTF-8 в ASCII и с помощью транслита будет соответствовать символу, который нельзя напрямую преобразовать во что-то похожее.

то есть Á -> A, É -> E и т. д..

После преобразования вы можете удалить символы, которые не хотите сортировать, с помощью preg_replace или str_replace.

Вот пример функции сравнения, которую вы можете использовать.

function normalize_string($string) {
    $ascii = iconv("utf-8","ascii//TRANSLIT", $string);
    return str_replace(array('!', "'", '?'), '', $ascii);

    // or

    return preg_replace('/[!\'?]/', '', $ascii);

    // or depending on how much you do want to replace... \W => any "non-word" character

    return preg_replace('/\W/', '', $ascii);
}

function custom_str_cmp($a, $b) {
    return strcmp(normalize_string($a), normalize_string($b));
}

usort($tracks, 'custom_str_cmp');

Вопрос 4. Да.

person Jacob    schedule 04.03.2011