MySQL Natural Sort Order By on Non-Numeric Field Type

The MySQL natural sort order 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 of character fields using a method that will produce undesirable results with numeric data.

Example of MySQL Natural Sort Order : sorting 5, 20, and 50 as character data produces 20, 5, and 50.

I filled up the table with sample data. The following example query :

SELECT * FROM stud ORDER BY student_id;
student_id

1
18
19
120
150
21
3
a6
a7

The results exactly usable. If we simply modify the order by declaration slightly (add “+0” to the order by field).

SELECT * FROM stud ORDER BY student_id+0;
student_id

1
3
18
19
21
120
150
a6
a7

And you use laravel. simple below the code :

$query16 = "member_id+0";
$members = DB::table('members')
           ->orderByRaw($query16)
           ->get();

Also read : orderByRaw() in Eloquent?