MYSQL varchar Sort cast, convert function type conversion

When you build the table, a field type is created as varchar (2), in fact, it should be built as an integer (2), but today in the background to find the problem of sorting, varchar sort has a problem, so how to solve it?

Example table structure: First look at my table structure show create TABLE Cardserver \g*************************** 1. Row *************************** table:cardservercreate table:create Table ' cardserver ' (' id ' int (one-by-one) not NULL de Fault ' 0 ', ' ver ' int (one) default null, ' Createtime ' datetime default NULL, ' UpdateTime ' datetime default NULL, ' Game_i d ' int (2) NOT null default ' 0 ', ' server_id ' varchar (2) is not null default ' ', ' 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=GBK1 row in Set (0.00 sec) because of the presence of foreign keys, I do not want to change the field type, laborious ah. Oh. Although I finally chose to change the field type, this is something. Because I want to explain in this post is the varchar sort problem. So no longer explain how I changed the field type, people are interested to search my previous log. (cheat Click) phenomenon Description: Below, I server_id order from the database inside, we look at the results after the sorting: select server_id from cardserver where game_id = 1 ORDER by server_id D ESC Limit 10;+-----------+| server_id |+-----------+| 8 | | 7 | | 6 | | 5 | | 4 | | 3        | | 2 | | 10 | | 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 is available, so that the server_id+0 after the sorting, the problem solved. 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 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 [INT EGER] So we can also solve the problem with CAST: 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: 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: All roads lead to Rome, all roads pass my home. No matter what the method, solving the problem is a good way. Of course, since MySQL provides us with ready-made functions. Why don't we make the code look more beautiful? Oh. So MySQL varchar sort I recommend using the cast or convert functions.

MYSQL varchar Sort cast, convert function type conversion