I just recently had a client whose data import requirements changed mid-project. We started off with an INTEGER field for this particular value because it made the most sense. Once I got the real data from the client I found out that that there are duplicate pages (representing multiple items on a page) and some items span more than one page. My first reaction was “Great, now I have to refactor the entire table structure to handle these items.” It turns out that the cleanest solution was to change the INTEGER field to a VARCHAR field. This would allow for items to be marked as 1a, 1b or 1c indicating that the items were on the same page, and wouldn’t disrupt the client’s data (they are creating a simple CSV from other software). Perfect, that works as expected and we now have multi-items per page! Then I went to the listing page. Everything was sorted, by the unnatural sort algorithm.
The solution was to use
CAST(page AS unsigned) ASC, page ASC
in the ORDER BY. This accomplished a “natural sort” algorithm and works really well.