Examples of UTF8-encoded Chinese sorting in PHP and MYSQL

This article mainly introduces the Chinese sorting method of UTF8 encoding in PHP and MYSQL. The example shows the sorting method based on pinyin, for more information about UTF8 encoding in PHP and MYSQL, see the following example. The specific implementation method is as follows:

Generally, there are three sorting methods for Chinese:

1. sorting by pinyin;
2. Sort by strokes;
3. sort by the radicals.

The default sorting method of the system is pinyin sorting, which is also common. we will introduce sorting by pinyin.

1. files in utf8 format need to be sorted in Chinese in the php array. Use gbk and gb2312. This is related to encoding in several formats. Gbk and gb2312 are encoded in pinyin.

The code is as follows:

Function utf8_array_asort (& $ array)
{
If (! Isset ($ array) |! Is_array ($ array ))
{
Return false;
}
Foreach ($ array as $ k => $ v)
{
$ Array [$ k] = iconv ('utf-8', 'gbk // IGNORE ', $ v );
}
Asort ($ array );
Foreach ($ array as $ k => $ v)
{
$ Array [$ k] = iconv ('gbk', 'utf-8 // IGNORE ', $ v );
}
Return true;
}

2. in MySQL, we often sort and query a field. However, when Sorting and Searching Chinese characters, the sorting and searching results of Chinese characters are often incorrect. This situation exists in many MySQL versions.

If this problem is not solved, MySQL cannot actually process Chinese characters. The reason for this problem is that MySQL is case insensitive when querying strings, in programming MySQL, the ISO-8859 character set is generally used as the default character set, therefore, the case-sensitivity conversion of Chinese encoding characters during the comparison process causes this phenomenon.

Solution:

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:

Select * from mytable order by CONVERT (chineseColumnName USING gbk );

I hope this article will help you design the PHP + MySQL program.