Сортировка строк как чисел
При разработке часто возникает задача сортировки. Числа и строки сортируются по-разному – тут нет никакой тайны. Проблемы возникают, когда необходимо в текстовом поле сохранять числовые данные. При сортировке к числам будут применены правила строк: после 1 будет 10, 11, 12… 19, 100. И только после упорядочивания всех чисел, начинающихся с 1, дойдет очередь до числа 2. Давайте разберем пример и методы решения проблемы.
В базе есть таблица address_home, в которой хранятся номера домов. Номера домов, кроме цифр самого номера дома, могут содержать информацию о корпусе, строении и т д… Для упрощения поиска было принято решение не отделять номер в отдельное поле. То есть, хранить номер дома как строку.
В итоге при сортировке получаем аномалию, описанную в начале статьи:
SELECT name FROM address_home ORDER BY name LIMIT 10 +------+ | name | +------+ | 1 | | 10 | | 10/1 | | 10/2 | | 11 | | 12 | | 13 | | 18 | | 19 | | 19а | | 100 | +------+ |
Чтобы исправить сортировку, проведем одну хитрую манипуляцию:
SELECT name, CAST(name AS UNSIGNED) AS name2 FROM address_home ORDER BY name2, name LIMIT 10 +------+ | name | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 5а | | 5б | | 6 | | 7 | | 8 | | 9 | +------+ |
Функция CAST используется для приведения поля к другому типу, в нашем случае – к числовому. По созданному полю производим сортировку. Чтобы корпуса и строения сортировались в нужном порядке, добавляем сортировку по полю name.
Решение приведено лишь для ознакомления. При больших нагрузках использовать такое не рекомендуется ввиду полного сканирования таблицы (даже при использовании индекса).
EXPLAIN SELECT name FROM address_home ORDER BY name +----+-------------+--------------+-------+------+------+-------------+ | id | select_type | table | type | key | rows | Extra | +----+-------------+--------------+-------+------+------+-------------+ | 1 | SIMPLE | address_home | index | name | 10 | Using index | +----+-------------+--------------+-------+------+------+-------------+ EXPLAIN SELECT name, CAST(name AS UNSIGNED) AS name2 FROM address_home ORDER BY name2, name +----+-------------+--------------+-------+------+------+-----------------------------+ | id | select_type | table | type | key | rows | Extra | +----+-------------+--------------+-------+------+------+-----------------------------+ | 1 | SIMPLE | address_home | index | name | 154 | Using index; Using filesort | +----+-------------+--------------+-------+------+------+-----------------------------+ |
Можно ли это применить в html?
Все лучше генерировать сразу подготовленные данные на серверной стороне.
Но если стоит задача делать живую сортировку, то подключайте JavaScript, JQuery + TableSorter или любые другие подобные библиотеки
ORDER BY CONVERT(name,UNSIGNED)