Utf8 Chinese sorting solution in MySQL

In MySQL, the default Character Set utf8 is used. The sorting order of Chinese characters by Pinyin is messy, not the result we want.

The solution is as follows:

1. If you do not want to change the table definition and default encoding, convert the fields to gbk encoding before sorting:

SELECT * FROM table order by convert (chinese_field USING gbk );

The premise is that the gbk character set is installed when MySQL is installed. Otherwise, an error is returned:

#1115-Unknown character set: 'gbk'

Add gbk encoding when compiling the source code. If the source code has been installed, re-compile and install it. Re-compile and install it will not affect the existing settings of MySQL, including data.

2. Change the character set of the field to gbk and use order by to sort the field directly.

MySQL 5.x supports defining character sets for a column.

3. Some people on the Internet say that adding the field definition to the binary Attribute can also achieve the effect. They did not perform tests and did not dare to comment.

Character Set verification rules are also mentioned in MySQL documents:

SELECT * FROM t1 order by a COLLATE utf8_bin;

However, this only affects the collation and does not affect the character set itself.