Sorting of Chinese fields (MySQL and PostgreSQL)

Create the same test table and data in MySQL (5.5.8) and PostgreSQL (9.0:

Create Table test_gbk_char (
Id int,
Username varchar (64)
);
Insert into test_gbk_char values (1, 'qingxia '), (2, 'Man Yu'), (3, 'chu hong ');

 

After completing the preceding steps, check the character sets and their verification rules:

MySQL (I did not specify the validation rules and use the default validation rules when creating a table ):

+ Bytes + | variable_name | value | + bytes + | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | bytes | binary | utf8 | | character_set_server | utf8 | character_set_system | utf8 |

Bytes ----------------------------------------------------------------------------------------

| Utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

 

PostgreSQL:

Encoding = 'utf8'
Tablespace = pg_default
Lc_collate = 'Chinese (simplified), singapore'

Lc_ctype = 'Chinese (simplified), Singapore'

Sort query:

MySQL:

Select * From test_gbk_char order by username; 2 Man Yu 3 Chu Hong 1 Qingxia

PostgreSQL:

Select * From test_gbk_char order by username; 3; "Chu Hong" 2; "Man Yu" 1; "Qingxia"

 

From the above results, we can see that the results provided by PostgreSQL are what I want. So what are the reasons for this difference? In fact, the answer has been given above. This is mainly because of the differences in verification rules.

The default utf8 validation rule is utf8_general_ci, which is not sorted by Chinese. If you want the results in MySQL to be the same as those in PostgreSQL, there are multiple methods, such as modifying the table and field character set validation rule definition. If you do not want to modify the table structure, you can perform conversion during query:

 

Select * From test_gbk_char order by convert (username using GBK) Collate gbk_chinese_ci;

 

3 Chu Hong 2 Man Yu 1 Qingxia

Note: The above comparison query only aims to explain the effect of different character verification on Chinese fields, not to mention the similarities and differences in sorting between MySQL and PostgreSQL.

 

 

 

Refer:

Http://ggsonic.iteye.com/blog/664921