ORDER BY для полей типа TEXT CHARSET UTF8. Русские буквы.

Serggio
Дата: 08.01.2013 00:07:57
Итак, имеем такую схему:

CREATE TABLE `books`(
`name` TEXT NOT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci

Заполнена табличка значениями:
'Книга 11', 'Книга 12', 'Книга 13', 'Книга 14', 'Книга 15', 'Книга 16', 'Книга 17', 'Книга 18', 'Книга 19', 'Книга 20'

Запрашиваем:
SELECT * FROM `books` ORDER BY `name`

В результате получаем:
Книга 19
Книга 18
Книга 17
Книга 16
Книга 15
Книга 14
Книга 13
Книга 12
Книга 11
Книга 20

Исследования показали, что такая байда наблюдается только если тип поля TEXT (для VARCHAR все работает правильно), и в тексте присутствуют русские буквы (то есть, собственно, те, которые в UTF-8 будут представлены двумя байтами). Если, например, вместо 'Книга xx' указать везде 'Book xx' всё будет пучком.

MySQL версии 5.5.27

Чё делать?

PS. Вопрос не из серии сортировки в натуральном порядке (у меня все числа тут одной размерности - двузначные). И не из серии SET NAMES и т.п.
Serggio
Дата: 08.01.2013 00:15:12
Дополню. Сортировка в обратном порядке (SELECT * FROM `books` ORDER BY `name` DESC) даёт следующую картину:

Книга 20
Книга 19
Книга 18
Книга 17
Книга 16
Книга 15
Книга 14
Книга 13
Книга 12
Книга 11

Что похоже на правду.
javajdbc
Дата: 08.01.2013 00:18:43
Serggio,

можно прямолинейно вырезать цисло
всякими сабстр-ингами, кастировать в
интегер и сортировать --- но это прошай индексы...
и надо что однозначно вырезалось...
Serggio
Дата: 08.01.2013 00:23:59
Да, решение с разбивкой строки на части и преобразование в число для конкретного случая работать будет. Но меня сильно смущает сам факт такой бяки. На деле строки будут разные и надо, чтобы всё сортировалось хорошо. Сейчас буду ставить MariaDB, может там лучше с этим :-/
Может, я всё-таки что-то делаю не так? Я не имею с MySQL большого опыта.
Serggio
Дата: 08.01.2013 00:26:56
По поводу индексов, кстати, в MySQL они не применяются при сортировки, если это поле типа TEXT. Поскольку, чтобы создать индекс по этому полю, надо указать конкретную длину поля как части индекса, то есть он теоретически может не вместить в себя все возможные значения поля. Поэтому для сортировки MySQL выбирает filesort. Но с этим я уж как-то смирюсь.
Users
Дата: 08.01.2013 18:20:52
Serggio,

Сам борюсь с UTF8 в реализации MySQL. Попробуйте конвертацию в 1251.
Serggio
Дата: 08.01.2013 22:02:59
Выход вижу только в создании дополнительного поля типа VARCHAR небольшой длины (мне, строго говоря, для сортировки первых ста символов хватит за глаза), в которое помещать часть текста, и сортировать по нему. Ну и, соответственно, поддерживать его в актуальном состоянии. Так заодно и индекс можно будет задействовать.
Думаю над тем, как организовать надёжное автоматическое обновление значения такого "сортировочного" поля. На приклад полагаться некорректно. Приемлемый вариант: добавить ещё одну таблицу со списком записей (значений ключа) из данной таблицы, которые требуют обновления значения сортировочного поля. Вспомогательная эта таблица пополняется соответствующими триггерами, когда происходят изменения в основной таблице. Затем, перед непосредственным запросом с сортировкой, вызывается простой апдейт для обновления сортировочного поля для всех записей, указанных в вспомогательной таблице. Благо запрос находится в хранимой процедуре и предварить его таким апдейтом (который сам в свою очередь можно поместить в некую хранимку) не является проблемой. Такой способ получается полностью реализованным внутри БД и не требует от приклада каких-то доделок.
Arhat109
Дата: 09.01.2013 00:01:54
Serggio,

думаю, дорогой вариант. А триггерами - никак?
Serggio
Дата: 09.01.2013 01:46:44
Триггер не может менять таблицу, для которой был вызван. Поэтому приходится делать триггером отметку в некоей вспомогательной таблице о том, что такая-то строка была изменена/добавлена (только при условии, что контролируемое поле изменилось! иначе может возникнуть бесконечная рекурсия, когда мы потом будем обновлять вычисляемое поле). И потом уже скопом обновлять только эти помеченные записи тогда, когда они понадобятся на чтение. После обновления вспомогательную таблицу очищать. Эти две операции (обновление по списку и очистка списка) нужно поместить в транзакцию, чтобы не возникла ситуация, когда после обновления появились новые записи в вспомогательной таблице, которые мы не успели обработать и удалили их следующей командой.
Кстати, тут выплывает какая-то неоднозначность в INNODB с блокировками. Следуя идеологии, я должен наложить на вспомогательную таблицу эксклюзивную блокировку с удержанием до конца транзакции. В MSSQL это табличные подсказки WITH (TABLOCKX, HOLDLOCK). Только таким образом я могу удержать конкурирующие транзакции от записи в таблицу, из которой читаю и только готовлюсь в неё записать/удалить. Соответственно, это же не даст мне внести изменения в исходную таблицу, поскольку триггер, который пишет об этом событии в вспомогательную таблицу, тоже подвиснет "до лучших времён". Что и нужно. Вот только INNODB сделало это за меня само, что странно. Что-то мне подсказывает, что это просто следствие эскалации грануляции блокировки от уровня row lock до table lock. И плохо, что я не могу этим управлять - по сути, повезло.

PS. Один эксперимент заставляет меня усомниться в последнем предложении. Возможно, работает всё как раз за счёт версионности изоляции транзакций. Это подтверждается тем фактом, что удаление строк через TRUNCATE показывает неприемлемый результат (удаляется в том числе и строка, помещенная триггером после обновления, но до удаления). В то же время DELETE FROM сработало правильно. В этом случае получается, что строка, которую триггер таки успевает втиснуть между апдейтом и делитом, последний не затронет как ту, что имеет отличный номер версии. В отличие от TRUNCATE, который на эти версии плевал с высокой колокольни.
Как-то так.
Serggio
Дата: 09.01.2013 01:54:47
Вместо "Соответственно, это же не даст мне внести изменения в исходную таблицу, поскольку триггер..." читать "Соответственно, это же не даст конкуренту внести изменения в исходную таблицу, поскольку триггер..."