Add the "binary" property to the field containing Chinese as a binary comparison, such as changing "name char (10)" to "name char" binary.
If you use the source code to compile MySQL, you can compile MySQL using the--WITH--CHARSET=GBK parameter, so MySQL will directly support Chinese lookup and sorting (the default is Latin1). You can also use EXTRA-CHARSETS=GB2312,GBK to add multiple character sets.
If you do not want to modify the table structure or recompile MySQL, you can also use the CONVERT function in the order by part of the query statement.
Like what
The code is as follows |
Copy Code |
SELECT * FROM MyTable ORDER by CONVERT (Chinesecolumnname USING GBK); |
UTF8 The default proofing set is Utf8_general_ci, which is not in Chinese. You need to force MySQL to sort by 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, ' Namerean ' varchar character set GBK default NULL, Primary KEY (' ID ') ) ; |
Save some data
The code is as follows |
Copy Code |
Insert INTO ' test1 ' (' id ', ' namerean ') values (' Li '); Insert INTO ' test1 ' (' id ', ' namerean ') VALUES (' Deng '); Insert INTO ' test1 ' (' id ', ' namerean ') VALUES (' Station '); |
Sort Query
The code is as follows |
Copy Code |
Mysql>select Namerean Fromtest111cnnetorder by Namerean; Deng (d) Lee (L) Station (z) |
This looks like MySQL Chinese sort is no problem, because our Namerean is GBK code, so the result is satisfactory, then if it is uft-8, so we construct a
The code is as follows |
Copy Code |
Select Namerean from Test111cnnet ORDER by convert (Namerean using gb2312) ASC; |
This way, no matter how the current field is encoded, he can sort it well by Chinese pinyin.
For more details please see: http://www.111cn.net/database/mysql/mysql.htm