Sqlvarchar sorting CAST/CONVERT implementation statement

This article introduces how to sort data using normal sorting and finds that varchar string sorting cannot obtain the desired result. Next we will introduce how to use case or convert to solve this problem.

This article introduces how to sort data using normal sorting and finds that varchar string sorting cannot obtain the desired result. Next we will introduce how to use case or convert to solve this problem.

The sorting problem was found in the background today. So there is no way to change it. The following describes the varchar Sorting Problem of MySQL.

Example Table Structure:
Let's take a look at my table structure.

The Code is as follows:

Show create table cardserver G * *************************** 1. row *************************** Table: cardserver Create Table: create table 'cardserver '( 'Id' int (11) not null default '0 ', 'Ver 'int (11) default NULL, 'Createtime' datetime default NULL, 'Updatetime' datetime default NULL, 'Game _ id' int (2) not null default '0 ', 'Server _ id' varchar (2) not null default '', 'Server _ name' varchar (40) 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 a foreign key exists, I don't want to change the field type. Haha. At last, I chose to change the field type. Because what I want to explain in this log is the varchar Sorting Problem. So I will not explain how I changed the field type. If you are interested, you can search for my previous logs. (Cheat and click)
Symptom description:
Next, I will sort the order by server_id. Let's take a look at the sorted results:

The Code is as follows:
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 result I want should be like 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.

The Code is as follows:
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:

The Code is as follows:
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 solve this problem:

The Code is as follows:
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 a lot of ways to look at your own mind. This is also the meaning of the so-called grand tunnel Rome.