How to sort problems with varchar types in MySQL

Today I found an interesting problem in sorting the country phone number list, and I wanted to sort the isdcode fields in order from small to large, so I wrote them.

The code is as follows	Copy Code
SELECT * from Gb_country_isdcode ORDER by isdcode ASC

The result is as follows, found unexpectedly is not I want the result, ASC sort is right, so I look for find, find AH find, finally found the reason;
Isdcode is varcher type, if the sort of direct with ASC is obviously not good, you must convert him to type int and then the normal sort, as long as the Isdcode + 0 can be
So write

The code is as follows	Copy Code
SELECT * from Gb_country_isdcode (isdcode+0) ASC

Side of the AH. Looks like the kind of data you want to compare the size of the AH. But why is +0 good?

It turns out that +0 is sorted by converting int type. This allows you to sort by size.

If it is not a telephone but how to do Chinese characters, we just need to do a simple conversion to sort

In MySQL, using order by to store the Chinese information of the field, the default results are not according to the sequence of Chinese pinyin, to be sorted by pinyin, the database needs to be set to UTF8, and then in order by the force of the field information to convert to GBK, The result of this is sorted in phonetic order. For example:

The code is as follows	Copy Code
SELECT * FROM table_name ORDER by CONVERT (column_name USING GBK);

Tried it in MySQL and the results were satisfactory.

The conclusion is: when the query, through the CONVERT function, the query data used by the character set gb2312 code can be, and then use the Chinese after the convert order. But if you really go to the table in the field of the character set to gb2312, it will involve a lot of coding problems, page transfer value Ah, from the database access Ah, very troublesome. As long as in the query, specify the character set, not really change the physical field to gb2312, very simple.