MySQL Natural Sort Order By on Non-Numeric Field Type

I recently worked on a project where I had to sort a set of rows returned from a MySQL query. The problem is that most of the data in the field being sorted is numeric yet the field type is varchar since some of the entries contained characters.

The reason that this is a problem is that MySQL sorts character fields using a method that will produce undesirable results with numeric data. For example, sorting 4, 10, and 50 as character data produces 10, 4, and 50. In most applications, this is highly undesirable.

The solution to this is to force a sorting order that is commonly referred to as a natural sort. Natural sort is just a term that refers to how humans would commonly sort a set of information (numbers as numbers and non-numeric characters alphabetically). Fortunately, this isn’t difficult to achieve in MySQL.

To further illustrate the problem, here is a simple table description:

> DESC SampleData;

Field
Type
data_char 
varchar(5)

I filled up the table with sample data. The following example query and resulting data shows the problem clearly:

> SELECT * FROM SampleData ORDER BY data_char;

data_char
10
11
120
21
3
a1

As you can see, the results aren’t exactly usable. If we simply modify the order by declaration slightly (add “+0” to the order by field), you can force MySQL to sort the field naturally.

> SELECT * FROM SampleData ORDER BY data_char+0;

data_char
3
10
11
21
120
a1

There you have it. To force a natural sort, just add a 0 onto the field you wish to be naturally sorted.