MySQL varchar sorting in numerical order

When creating a table by yourself, create a field type as varchar (2). In fact, it should be set to int (2. Because I can only output numbers. There's nothing to do with this. It's nothing more than taking up some space. I'm too lazy to change it. However, I found a Sorting Problem in the background today. So there is no way to change it. The following describes the varchar Sorting Problem of MySQL.

Next, I will sort the order of server_id in the database. Let's take a look at the sorted results:

Select server_id from cardserver where game_id = 1 order
By server_id DESC limit 10; + ----------- +
| Server_id |
+ ----------- +
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 10 |
| 1 |
+ ----------- +

 

Obviously, the expected result is 10, 8, 7, 6, and 5. But this 10 is behind 2. Sort by string. In fact, I want to arrange it as a numerical value.

Manual Conversion Type:

You can use the following method to sort server_id + 0 and then solve the problem.

Select server_id from cardserver where game_id = 1 order
By server_id + 0 DESC limit 10;

+ ----------- +
| Server_id |
+ ----------- +
| 10 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+ ----------- +

 

Use the MySQL function cast/convert:

MySQL provides two types of conversion functions: Cast and convert. How can we let go of the ready-made stuff?

Cast () and convert () functions can be used to obtain values of one type and generate values 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:

Select server_id from cardserver where game_id = 1 order
By cast (server_id as signed) DESC limit 10;

You can also use convert to solve this problem:

Select server_id from cardserver where game_id = 1 order
By convert (server_id, signed) DESC limit 10;