mysql advanced (8) how to sort varchar type

How to sort the varchar type in MySQL

asc upgrade

desc descending order

The default order by in MySQL can only sort numbers and date types, but for varchar Character type sorting seems to be useless. Now I will introduce to you how to solve the varchar type sorting problem.

I discovered an interesting problem when sorting the national telephone number table today. I wanted the isdcode field to be sorted from small to large, so I wrote it like this

SELECT * FROM gb_country_isdcode ORDER BY isdcode asc

The results are as follows, and I found that it was not the result I wanted. The asc sorting was correct, so I searched and searched and searched, and finally found the reason;

isdcode is a varcher type Yes, it is obviously not possible to use asc directly for sorting. It must be converted to int type and then it can be sorted normally. Just isdcode + 0 is enough

So I write it like this

SELECT * FROM gb_country_isdcode ORDER BY (isdcode+0) asc

It seems that the kind of data you want is relatively large. . But why is +0 good? It turns out that after +0, the INT type is converted to sorting. This way you can sort by size.

What if it is not a phone number but a Chinese character? We only need to perform a simple conversion to sort the Chinese characters.

Use order by in mysql to store Chinese information fields. The default results are not sorted in the order of Chinese pinyin. If you want to sort by the pinyin of Chinese characters, you need to set the character set of the database. to UTF8, and then force the field information to be converted to GBK when ordering by, so that the results are sorted in pinyin order. For example:

SELECT * FROM table_name ORDER BY CONVERT(column_name USING gbk);

I tried it in mysql and the result was very satisfactory.

The conclusion is: when querying, just encode the character set gb2312 used in the queried data through the convert function, and then use the Chinese sorting after convert. But if you really change the character set of the fields in the table to gb2312, it will involve a lot of encoding issues, such as page value transfer and access from the database, which is very troublesome. As long as you specify the character set when querying, it does not really change the physical field to gb2312, it is very simple.

The above is the content. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!