SQL varchar Sort Cast/convert Implementation statement

Source: Internet
Author: User
Tags datetime

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.