MySQL Varchar,char type sort cast CONVERT

Simple to say about MySQL's varchar sorting problem, a cautionary tale.

Sample Table structure:

Let's take a look at my table structure

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 Defaul T ' 0 ', ' ver ' int (one) 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 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)

Description of the phenomenon:

Below, I from the database in order to server_id, we look at the sort of results:

The code is as follows	Copy Code
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, I want the result to be 10,8,7,6,5 like this. 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:

The following method can be used, so that the server_id+0 after the sorting, the problem solved.

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

MySQL Sorted alphabetically

The client_name character encoding of a field in a datasheet is utf8_general_ci;

The code is as follows	Copy Code
SELECT * from ' client_info_msg ' ORDER by CONVERT (client_name USING gbk) COLLATE gbk_chinese_ci ASC;

The character encoding for a field name is Latin1_swedish_ci;

SQL code

The code is as follows	Copy Code
SELECT * from ' TBL ' ORDER by Birary (name) ASC

One, 1 CREATE TABLE name (name VARCHAR (10));

For this table, by default, the results of the following two queries are the same:

The code is as follows	Copy Code
SELECT * from TABLE NAME WHERE name= ' clip '; SELECT * from TABLE NAME WHERE name= ' Clip ';

MySQL default query is case-insensitive, if you need to distinguish between him, must be in the table, binary marked sensitive properties.

The code is as follows	Copy Code
CREATE TABLE NAME ( Name VARCHAR (BINARY) );

2 Implement SELECT * from TABLE NAME where BINARY name= ' Clip ' in SQL statement;

3 Setting the character set:

utf8_general_ci– is case insensitive

utf8_bin– Case Sensitive

Second, MySQL is not case-sensitive under Windows, the script file into MySQL after the table name will automatically be converted to lowercase, and then want to put the database export to the Linux server to use when the error. Because in Linux the table name is case-sensitive and cannot find a table, many of them are said to change the MySQL settings under Linux so that it is not case-sensitive, but there is no way to turn to the case of Windows sensitivity. In fact, the method is the same, the corresponding change in Windows MySQL settings on the line.

Specific actions:

Add a row to the MySQL profile My.ini:

The code is as follows	Copy Code
Lower_case_table_names = 0

0: Case-sensitive, 1: case-insensitive

MySQL under Linux database name, table name, column name, alias capitalization rule is this:

1, the database name and table name is strictly case-sensitive case;

2, the alias of the table is strictly case-sensitive;

3, column name and column alias in all cases are ignored case;

4, the variable name is also strictly case-sensitive; MySQL is case-insensitive under windows

So MySQL varchar sort I recommend using the cast or CONVERT function.