Mysql Chinese pinyin sorting Implementation Method

Because mysql does not support Chinese, it is not difficult to sort Chinese by pinyin. Today we use the convert function to implement it, any encoding can be sorted in pinyin. For more information, see.

Add the "binary" attribute to a field that contains Chinese characters for binary comparison. For example, change "name char (10)" to "name char (10) binary ".
If you use the source code to compile MySQL, you can use the -- with -- charset = gbk parameter when compiling MySQL, so that MySQL can directly support Chinese search and sorting (latin1 by default ). You can also use extra-charsets = gb2312 and gbk to add multiple character sets.
If you do not want to modify the table structure or re-compile MySQL, you can also use the CONVERT function in the order by section of the query statement.

For example

The Code is as follows:	Copy code
Select * from mytable order by CONVERT (chineseColumnName USING gbk );

The default verification set of UTF8 is utf8_general_ci, which is not in Chinese. You need to force MySQL to be sorted in Chinese.

The Code is as follows:	Copy code
Select * from core_vender_info order by convert (vender_abbrev USING gbk) COLLATE gbk_chinese_ci

Instance

The Code is as follows:	Copy code
Create table 'test111cnnet '( 'Bid' int (4) unsigned not null auto_increment, 'Namereid' varchar (20) character set gbk default null, Primary key ('id ') );

Save some data

The Code is as follows:	Copy code
Insert into 'test1' ('id', 'namereid') values ('lil '); Insert into 'test1' ('id', 'namereid') values ('dun '); Insert into 'test1' ('id', 'namereid') values ('stopped ');

Sort Query

The Code is as follows:	Copy code
Mysql> select namerean fromtest111cnnetorder by namerean; Deng (d) Li (l) Station (z)

It seems that mysql Chinese sorting is no problem, because our namerean is gbk encoding, so the result is satisfactory, then if it is uft-8 will not work, so we constructed,

The Code is as follows:	Copy code
Select namerean from test111cnnet order by convert (namerean using gb2312) asc;

In this way, No matter why the current field is encoded, it can be sorted by Chinese pinyin.

For more details, see http://www.bKjia. c0m/database/mysql/mysql.htm