mysql sort by Chinese field
- 2020-05-07 20:32:03
- OfStack
If this problem is not resolved, MySQL will not be able to actually handle Chinese. The reason for this problem is that MySQL is case-insensitive when querying strings, and ISO-8859 character set is generally used as the default character set when compiling MySQL. Therefore, the case conversion of Chinese coded characters during the comparison process results in this phenomenon.
Solutions:
For fields that contain Chinese, add the "binary" attribute as a base 2 comparison, for example changing "name char(10)" to "name char(10)binary".
If you compile MySQL with source code, you can compile MySQL with the --with--charset=gbk parameter, so MySQL will directly support Chinese search and sort (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 section of the query statement. For example, select * from mytable order by CONVERT(chineseColumnName USING gbk);
Solutions:
For fields that contain Chinese, add the "binary" attribute as a base 2 comparison, for example changing "name char(10)" to "name char(10)binary".
If you compile MySQL with source code, you can compile MySQL with the --with--charset=gbk parameter, so MySQL will directly support Chinese search and sort (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 section of the query statement. For example, select * from mytable order by CONVERT(chineseColumnName USING gbk);