Let MySQL support the implementation method of Chinese sorting
- 2020-05-12 06:18:54
- OfStack
Let MySQL support Chinese sorting
The ISO-8859 character set is generally used as the default character set when compiling MySQL. Therefore, the case conversion of Chinese coded characters in the comparison process has caused this phenomenon. One solution is to add the "binary" attribute to the field containing Chinese and make it a base 2 comparison.
For example, change "name char(10)" to "name char(10)binary".
When compiling MySQL, use the --with--charset=gbk parameter, so MySQL will directly support Chinese search and sorting.
mysql order by Chinese sorting
In MySQL, we often sort queries for 1 field, but when sorting and searching for Chinese characters, the sorting and searching results of Chinese characters are often wrong. This is true in many versions of MySQL.
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. When encoding MySQL, the ISO-8859 character set is generally used as the default character set.
Solutions:
For fields containing Chinese, add the "binary" attribute as a base 2 comparison, for example, change "name char(10)" to "name char(10)binary".
If you compile MySQL using source code, you can compile MySQL using the --with--charset=gbk parameter, so MySQL will directly support Chinese search and sorting (the default is latin1). You can also use extra-charsets =gb2312,gbk to add multiple character sets.
If you don't 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);
The default proofreading set of UTF8 is utf8_general_ci, which is not in Chinese. You need to force MySQL to sort in Chinese.
select * from core_vender_info order by convert(vender_abbrev USING gbk) COLLATE gbk_chinese_ci
The ISO-8859 character set is generally used as the default character set when compiling MySQL. Therefore, the case conversion of Chinese coded characters in the comparison process has caused this phenomenon. One solution is to add the "binary" attribute to the field containing Chinese and make it a base 2 comparison.
For example, change "name char(10)" to "name char(10)binary".
When compiling MySQL, use the --with--charset=gbk parameter, so MySQL will directly support Chinese search and sorting.
mysql order by Chinese sorting
In MySQL, we often sort queries for 1 field, but when sorting and searching for Chinese characters, the sorting and searching results of Chinese characters are often wrong. This is true in many versions of MySQL.
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. When encoding MySQL, the ISO-8859 character set is generally used as the default character set.
Solutions:
For fields containing Chinese, add the "binary" attribute as a base 2 comparison, for example, change "name char(10)" to "name char(10)binary".
If you compile MySQL using source code, you can compile MySQL using the --with--charset=gbk parameter, so MySQL will directly support Chinese search and sorting (the default is latin1). You can also use extra-charsets =gb2312,gbk to add multiple character sets.
If you don't 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);
The default proofreading set of UTF8 is utf8_general_ci, which is not in Chinese. You need to force MySQL to sort in Chinese.
select * from core_vender_info order by convert(vender_abbrev USING gbk) COLLATE gbk_chinese_ci