MYSQL varchar sort CAST, convert and usage

Source: Internet
Author: User
Tags datetime

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

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.