How to sort varchar types in MySQL

By default, order by in mysql can only sort numbers and date types, but it does not seem to be useful for varchar sorted type sorting. Next I will introduce to you how to solve the varchar sort problem.

Today, I found an interesting problem when sorting the national telephone number list. I want to sort isdcode fields in the ascending order.

The Code is as follows:	Copy code
SELECT * FROM gb_country_isdcode order by isdcode asc

The result is as follows. It turns out that it is not the result I want. asc is in the correct order, so I can find it, find it, and finally find the reason;
Isdcode is of the varcher type. If asc is used directly for sorting, it is obviously not possible to convert isdcode to the int type and then sort the isdcode normally.
As a result

The Code is as follows:	Copy code
SELECT * FROM gb_country_isdcode order by (isdcode + 0) asc

Yes. It seems that the data you want is relatively small .. But why is it better to add 0?

It turns out that after + 0, INT type sorting is converted. In this way, you can sort by size.

What if it is not a phone number but a Chinese character,Sort Chinese characters by simple conversion.

In mysql, the order by clause is used to store Chinese characters. The default result is not sorted by Chinese character or pinyin, you need to set the character set of the database to UTF8, and then forcibly convert the field information to GBK when order by. In this way, the results are sorted in pinyin order. For example:

The Code is as follows:	Copy code
SELECT * FROM table_name order by convert (column_name USING gbk );

I tried it in mysql and the results were very satisfactory.

The conclusion is: During the query, the convert function is used to encode the gb2312 character set used for the queried data, and sort the data in Chinese after convert. However, if you change the character set of fields in the table to gb2312, many encoding problems will be involved. It is very troublesome to pass the page value and access it from the database. You only need to specify the character set during the query, but it is not really easy to change the physical field to gb2312.