MySQL Order By Problem, Why is 1000 being seen as Smaller than 2?

I have a strange problem,

I am trying to order the output of a set of records by a field called开发者_JAVA技巧 displayOrder. Now even though record A has a displayOrder of 2 and record B has a displayOrder of 1000, record B still shows up before record A. Here's my select statement:

SELECT * FROM items ORDER BY displayOrder ASC

It works fine until I have a record greater than 9, then 10, 11, 12, etc are seen as smaller than 2, 3, 4 because they start with the number 1. Any way to fox this?

Because you choose wrong field type. Integers must be stored in the int type column.

Alphabetically, it is first. A string you compare a letter at a time to figure out which is alpahabetical first (unlike numbers, the one with the most digits isn't always the largest). You will need to use a numerical field type.

It looks like the column type is wrong, as it is sorting alphabetically instead of numerically. If you can't change the column type for some reason, there is a workaround:

SELECT * FROM items ORDER BY displayOrder + 0 ASC