Натуральная сортировка в MySQL., какой выход?

[GET]

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

У меня на сайте есть место где - нужно выводить отсортированные списки, много списков, хранятся они в БД в varchar`ах. Некоторые из них могут начинаться с цифр, типа такого (но там же есть строки и без цифр):

Начальный уровень
1 уровень
2 уровень
...
10 уровень
Последний уровень

Если сделать такой список ORDER BY `name` то он выведет:

1 уровень
10 уровень
2 уровень
...
Что не есть хорошо конечно, но существуют варианты:

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

Еще varchar поле представляют, как числовое типа:
ORDER BY `name`+0, чтоб отсоритровать как бы цифры, но это тоже не выход т.к. неправильно отобразит похожие строчки.

Или
ORDER BY LENGTH(name), name;//добавляют длинну значения поля

Или
ORDER BY RIGHT(CONCAT('00',number),3);

Но все же это все через Ж.
Либо некорректные результаты, либо неприемлемо долго.

Короче, пришел к выводу (особенно элементов не так много) спасет natcasesort - сортировка ручками на уровне php.

[Placido]

Создать отдельную таблицу с полями id уровня, name уровня. В таблице со списками вместо "10 уровень" писать только его id.

[sergeiss]

Вобщем, следи за логикой... Не так и сложно это сделать. Надо только проявить смекалку :) И - рекомендую - выполни по очереди все запросы, что я написал. Тогда будет понятнее, как работает финальный запрос.

Итак, шаг 1. Получить сортировку по "натуральному" виду. Используем приведение строки к числу, после чего сравниваем.

select * from `tab1`
order by cast(nam as unsigned) asc 

Получаем: сначала идут все строки, которые начинаются не на число, а затем - числовые, отсортированные как нам нужно.

Немного думаем и переходим к шагу 2.

select * from `tab1`
where cast(nam as unsigned) > 0
order by cast(nam as unsigned) asc 

Выбираем все строки, которые начинаются на числа.

Логично получаем шаг 3: выбор всех "нечисловых" строк

select * from tab1
where cast(nam as unsigned) = 0
order by nam

А затем берём всё и собираем в кучу посредством UNION:

select * from
(
SELECT * FROM `tab1`
where cast(nam as unsigned) > 0
order by cast(nam as unsigned) asc 
) ini_1
union
select * from
(
select * from tab1
where cast(nam as unsigned) = 0
order by nam
) ini_2

Всё, результат получен :) И не надо ПХП мучать для того, что может выполнить Мускуль. Пусть он сам работает!!!

Если требуется что-то более сложное... Ну, думай дальше сам. Трамплин для прыжка у тебя есть.

[inpost]

Переведи в int и всё.

[GET]

sergeiss
Задумку понял, спасибо, отпишусь, как попробую. Правда неясен вопрос а если будут строки, а они есть, у которых цифра в конце или серидине, например названия книг:

1 среди первых
2x2
Аврора
Бродяги севера
Телефон 911
В августе 44-го

Тогда слова начищающиеся на букву, но имеющие цифру отсортируются неправильно.

[bestxp]

вообще-то выдуманная проблема, сортирует он строки, а не цифры, следовательно

1
10
101 Далматинец
2
3
31
33 коровы

притом даже еще веселее

1
10
Edit Piaf
Joe Dassin
Несчастный Случай

и тд

[sergeiss]

bestxp, а в чем "выдуманность" проблемы? ТС хочет, чтобы была определенная сортировка Я ему показал, как это можно достичь.
Ты же написал, что получим при простой сортировке по текстовому полю. И к чему это написал?

[GET]

sergeiss
как я уже написал тебе выше способ не работает для слов с цифрами в середине или конце

[sergeiss]

Тогда у тебя только один вариант: завести отдельное "сортировочное" поле, по которому сортировать. И заполнять его вручную.
Например, для строки "В августе 44-го" в этом сортировочном поле напиши 44.

Автоматизировать тут вряд ли что-то получится. Не, ну можно перед записью выбирать из поля только цифры и вводить их в "сортировочное" поле. Но я почти уверен, что ты получишь результат, который не ожидаешь.

[GET]

всем спасибо решил кое что перекроить чтоб выделять поля у которых нужна сортировка