A Workaround to Support Natural Sorting in MySQL
Several times in the last few projects I’ve built, I’ve come across the need to sort alphanumeric rows in a database by number (i.e. entry1, entry2, entry3, etc.). If you’ve ever tried to do this, you know that natural sorting in MySQL with alphanumeric entries is a pain.
The Data
For our purposes, we’ll be using the following table:
Table: sorting_test -------------------------- ------------- | alphanumeric VARCHAR(75) | integer INT | -------------------------- ------------- | test1 | 1 | | test2 | 2 | | test3 | 3 | | test4 | 4 | | test5 | 5 | | test6 | 6 | | test7 | 7 | | test8 | 8 | | test9 | 9 | | test10 | 10 | | test11 | 11 | | test12 | 12 | -------------------------- -------------
The Problem
Sorting by the column integer, of course, presents no problems.
Query: SELECT alphanumeric, integer
FROM sorting_test
ORDER BY integer
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 2 |
| test3 | 3 |
| test4 | 4 |
| test5 | 5 |
| test6 | 6 |
| test7 | 7 |
| test8 | 8 |
| test9 | 9 |
| test10 | 10 |
| test11 | 11 |
| test12 | 12 |
-------------------------- -------------
However, when we try sorting by the alphanumeric column, we get unexpected results:
Query: SELECT alphanumeric, integer
FROM sorting_test
ORDER BY alphanumeric
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test10 | 10 |
| test11 | 11 |
| test12 | 12 |
| test2 | 2 |
| test3 | 3 |
| test4 | 4 |
| test5 | 5 |
| test6 | 6 |
| test7 | 7 |
| test8 | 8 |
| test9 | 9 |
-------------------------- -------------
Obviously, this is not the desired outcome. Since we’re sorting alphabetically, the entries are actually in the correct order, but we need to find a way to sort numerically.
This is called “natural sorting” in MySQL. Actually, it’s called natural sorting everywhere. What it means is, in layman’s terms, a human being would naturally sort the items with the count in order.
However, as far as MySQL is concerned the above output is alphabetical. If you swap the numbers for letters, the way MySQL sorts these items makes much more sense:
Query: SELECT alphanumeric, integer
FROM sorting_test
ORDER BY alphanumeric
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| testA | 1 |
| testAA | 10 |
| testAB | 11 |
| testAC | 12 |
| testB | 2 |
| testC | 3 |
| testD | 4 |
| testE | 5 |
| testF | 6 |
| testG | 7 |
| testH | 8 |
| testI | 9 |
-------------------------- -------------
The Solution
There are a whole lot of solutions out there if you hit up Google, and you can, of course, just use the natsort() function in PHP, but it’s simple enough to accomplish natural sorting in MySQL: sort by length first, then the column value.
Query: SELECT alphanumeric, integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 2 |
| test3 | 3 |
| test4 | 4 |
| test5 | 5 |
| test6 | 6 |
| test7 | 7 |
| test8 | 8 |
| test9 | 9 |
| test10 | 10 |
| test11 | 11 |
| test12 | 12 |
-------------------------- -------------
This works because the first nine entries are 5 characters long, with the tenth, eleventh, and twelfth entries at 6 characters; this creates two groups of entries that are then sub-sorted, resulting in our desired order. This is called “natural sorting”.
Summary
This is a quick tip, but a useful one. Do you have a better way of forcing natural sorting in MySQL? Let me know in the comments!