这些查询都给出了我期望的结果:
SELECT sex
FROM ponies
ORDER BY sex COLLATE latin1_swedish_ci ASC
SELECT sex
FROM ponies
ORDER BY CONVERT(sex USING utf8) COLLATE utf8_general_ci ASC
| f |
| f |
| m |
| m |
+---+
但是这个查询给出了不同的结果:
SELECT sex FROM ponies ORDER BY sex ASC
| m |
| m |
| f |
| f |
+---+
配置如下:
SHOW VARIABLES LIKE 'collation\_%'
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
表排序规则是
latin1_swedish_ci
。MySQL服务器是5.5.16。
最佳答案
表排序规则
排序规则默认值按表存储。有一个服务器集默认值,但在创建表时应用于该表。
要查找特定表的排序规则,请运行以下查询:
SHOW TABLE STATUS LIKE 'ponies'\G
您应该看到这样的输出:
*************************** 1. row ***************************
Name: ponies
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 8
Avg_row_length: 20
Data_length: 160
Max_data_length: 5629499534213119
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2012-02-27 10:16:25
Update_time: 2012-02-27 10:17:40
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
您可以在结果中看到
Collation
设置。列排序规则
还可以覆盖表中特定列的排序规则设置。这样的create table语句将创建一个
latin1_swedish_ci
表,其中包含一个utf8_polish_ci
列:CREATE TABLE ponies (
sex CHAR(1) COLLATE utf8_polish_ci
) CHARACTER SET latin1 COLLATE latin1_swedish_ci;
查看结果的最佳方式如下:
SHOW FULL COLUMNS FROM ponies;
输出:
+-------+---------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+---------+----------------+------+-----+---------+-------+---------------------------------+---------+
| sex | char(1) | utf8_polish_ci | YES | | NULL | | select,insert,update,references | |
+-------+---------+----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)
关于mysql - MySQL默认对ORDER BY使用什么排序规则?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9470337/