MYSQL varchar sort CAST, convert and usage

First Reader Network reference

The code is as follows	Copy Code
CONVERT (expr USING transcoding_name)

In MySQL, transcoding names are the same as the corresponding character set names.

Examples:

The code is as follows	Copy Code
SELECT CONVERT (_latin1 ' Müller ' USING UTF8); INSERT into utf8table (utf8column)

SELECT CONVERT (Latin1field USING UTF8) from latin1table; CONVERT (.....) USING ...) is implemented according to the standard SQL specification.

also use CAST () to convert a string to a different character set. The syntax is:
CAST (character_string as character_data_type character SET Charset_name)

Example:

The code is as follows	Copy Code
SELECT CAST (_latin1 ' test ' as CHAR CHARACTER SET UTF8);

Convert () provides a way to convert data between different character sets. The syntax is:

Double-click the code to select 1 CONVERT (expr USING transcoding_name)

In MySQL, the conversion code name is the same as the corresponding character set name.

Example:

The code is as follows	Copy Code
SELECT CONVERT (_latin1 ' Müller ' USING UTF8); INSERT into Utf8table (utf8column) SELECT CONVERT (Latin1field USING UTF8) from latin1table; CONVERT (.....) USING ...)

Implemented according to standard SQL specifications.

In traditional SQL mode, if you convert a "0" date string to a date type, the CONVERT () function returns NULL. A warning is also generated in the MySQL5.1.

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: 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 | +-----------+

Note:

is cast (xxx as type), CONVERT (XXX, type).

The types that can be converted are limited. This type can be one of the following values:

• Binary, with BINARY prefix effect: binary   
character, with parameters: CHAR ()     
• Date: date & nbsp;  
• Time: time    
• Date-time: datetime    
• Floating-point number: DECIM al     
• Integer: signed    
• Unsigned integer: UNSIGNED