Differences and points of attention and solutions for MySQL database sorting by varchar string type and sorting by int integer type

When you build your own table, create a field type as varchar (2), which should be built as an int (2). Because I'm only allowed to output numbers. This would have been nothing, just occupy a bit of space, too lazy to change. But today there is a problem with sorting in the background. So, there is no way to change it. Here's a brief look at MySQL's varchar sort problem, warning.

Below, I server_id order from the database, we look at the results after the sorting:

Select server_id from Cardserver where game_id = 1 order by SERVER_ID desc limit ten;

+-----------+
|server_id|
+-----------+
|8|
|7|
|6|
|5|
|4|
| 3|
|2|
|Ten|
|1|
+-----------+

Obviously, the result I want is 10,8,7,6,5. But this 10 is in the back of 2. Sorted by string. I'm actually trying to rank it as a numerical value.

Manual Conversion Type:

The following method can be used, so that the server_id+0 after the sorting, the problem solved.

Selectserver_id fromCardserverwheregame_id=1Order byserver_id+0descLimitTen;

+-----------+
|server_id|
+-----------+
|Ten |
|8|
|7|
| 6|
|5|
|4|
|3|
|2|
|1 |
+-----------+

Use the MySQL function Cast/convert:

MySQL provides us with two types of conversion functions: cast and convert, how can we let go of the ready-made stuff?

The CAST () and CONVERT () functions can be used to get a value of one type and produce a value of another type.
This type can be one of the following values:
binary[(N)]
char[(N)]
DATE
Datetime
DECIMAL
Signed [INTEGER]
Time
UNSIGNED [INTEGER]

So we can also use cast to solve the problem:

Selectserver_id fromCardserverwheregame_id=1Order byCAST(server_id as signed)descLimitTen;

You can also use convert to fix this problem:

Select server_id from Cardserver where game_id = 1 order by CONVERT (server_id,signed) desc Limit Ten ;

This article from the "Alpine" blog, reproduced please contact the author!

Differences and points of attention and solutions for MySQL database sorting by varchar string type and sorting by int integer type