Varchar and char sorting in MySql CAST CONVERT

In mysql, if I use common data sorting methods, we may not get the expected results, but mysql provides us with the CAST CONVERT function, which can CONVERT numbers accordingly, I used this function to sort Chinese characters in the past.

Let's briefly introduce the varchar Sorting Problem of MySQL.

Example 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 (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 foreign keys exist, 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 of server_id in the database. Let's take a look at the sorted 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, the expected result is 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:	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 | + ----------- +

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:	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 solve 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 by letter

The character encoding of A client_name field in the data table 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 of a field name is latin1_swedish_ci;

SQL code

The Code is as follows:	Copy code
Select * from 'tbl' order by birary (name) asc

1. create table name (name VARCHAR (10 ));

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

The Code is as follows:	Copy code
SELECT * from table name where name = 'clip '; SELECT * from table name where name = 'clip ';

By default, MySql queries are case-insensitive. If you need to differentiate them, Binary indicates sensitive attributes when creating a table.

The Code is as follows:	Copy code
Create table name ( Name VARCHAR (10) BINARY );

2. SELECT * from table name where binary name = 'clip' in an SQL statement ';

3. Set the character set:

Utf8_general_ci-case insensitive

Utf8_bin-case sensitive

2. MySQL is case-insensitive in windows. After the script file is imported into MySQL, the table name is automatically converted to lowercase, as a result, an error occurs when you want to export the database to a linux server. Because the table names in linux are case-sensitive and cannot be found, many of them indicate that changing the MySQL settings in linux makes them case-insensitive, but is there a way to make windows case sensitive. In fact, the method is the same. Modify the MySQL settings in windows accordingly.

Specific operations:

Add a line to the MySQL configuration file my. ini:

The Code is as follows:	Copy code
Lower_case_table_names = 0

0: Case Sensitive, 1: case insensitive

In Linux, the database name, table name, column name, And alias are case-sensitive:

1. The database name and table name are case sensitive;

2. Table aliases are case sensitive;

3. The column name and column alias are case-insensitive in all cases;

4. Variable names are also case sensitive. MySQL is case insensitive in Windows.

Therefore, we recommend that you use the CAST or CONVERT function to sort MySql varchar.