SQL varchar Sort Cast/convert Implementation statement

Is today in the background to find a sort of problem. So, there's no way to change it. Here is a brief look at MySQL varchar sorting problem, warning.

Sample Table structure:
Let's take a look at my table structure

The code is as follows

Copy Code

show "create Table Cardserver G *************************** 1. Row ***************************         table:cardserver Create table:create Table ' cardserver ' (   ' id ' int (one) not NULL default ' 0 ',   ' ver ' int (one) default null,   ' createtime ' datetime default NULL,   ' updatetime ' datetime de Fault null,   ' game_id ' int (2) NOT null default ' 0 ',   ' server_id ' varchar (2) is not null default ', & nbsp ' server_name ' varchar not NULL default ',   PRIMARY key  (' id '),   UNIQUE KEY ' Game_id_server_ Id ' (' game_id ', ' server_id '),   UNIQUE KEY ' game_id_server_name ' (' game_id ', ' server_name ') Engine=innodb DEFAULT CHARSET=GBK

1 row in Set (0.00 sec) because there is a foreign key, so I do not want to change the field type, laborious ah. Oh. Although finally I chose to change the field type, this is something. Because what I want to illustrate in this log is the varchar sort problem. So no longer explain how I changed the field type, you are interested in searching my previous log. (Cheat click)
Phenomenon Description:
below, I from the database in order to server_id, we look at the sort of results:

code is as follows	copy code
select server_id from cardserver where game_id = 1 order BY server_id DESC limit 10;< br> +-----------+ | server_id | +-----------+ | 8         | | 7         | | 6         | | 5         | | 4         | | 3         | | 2         | | 10        | | 1         |

+-----------+ Obviously, I want the result to be 10,8,7,6,5. But this 10 line is in the back of 2. is arranged according to a string. In fact, I want to think of it as a numerical row.
Manual Conversion Type:
You can use the following method, so that the server_id+0 after sorting, the problem is resolved.

code is as follows	copy code
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         | +-----------+

Using the MySQL function Cast/convert:
MySQL provides us with two types of conversion functions: cast and convert, ready-made things how can we let go?
The CAST () and CONVERT () functions can be used to get a value of one type and to 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:

The code is as follows	Copy Code
Select server_id from cardserver where game_id = 1 order by CAST (server_id as signed) DESC limit 10; +-----------+ | server_id | +-----------+ | 10 | | 8 | | 7 | | 6 | | 5 | | 4 | | 3 | | 2 | | 1 | +-----------+

You can also use convert to fix this problem:

The code is as follows	Copy Code
Select server_id from cardserver where game_id = 1 order by CONVERT (server_id,signed) desc limit 10; +-----------+ | server_id | +-----------+ | 10 | | 8 | | 7 | | 6 | | 5 | | 4 | | 3 | | 2 | | 1 | +-----------+

Summary: There are many ways to see their own brain, the so-called road to Rome is also the meaning of the.