这是MySQL排序错误吗? [英] Is this a MySQL sorting bug?
问题描述
我遇到了奇怪的服务器行为MySQL 5.1.50.记录排序不正确.
I was faced with strange server behavior MySQL 5.1.50. It sorts records incorrectly.
例如，我创建了一个表test:
For example I have created a table test:
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `title`) VALUES
(1, 'record1'),
(2, 'record2'),
(3, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),
(4, 'ABCDEFGHIJKLMNOPQRSTUVWXYY');
并进行查询:
mysql>设置名称utf8; 查询正常，受影响的0行(0.00秒)
mysql> set names utf8; Query OK, 0 rows affected (0.00 sec)
mysql>从测试顺序中按标题asc选择*;
mysql> select * from test order by title asc;
+----+----------------------------+
| id | title |
+----+----------------------------+
| 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ |
| 4 | ABCDEFGHIJKLMNOPQRSTUVWXYY |
| 1 | record1 |
| 2 | record2 |
+----+----------------------------+
组4行(0.00秒)
mysql>从测试顺序中按标题desc选择*;
mysql> select * from test order by title desc;
+----+----------------------------+
| id | title |
+----+----------------------------+
| 2 | record2 |
| 1 | record1 |
| 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ |
| 4 | ABCDEFGHIJKLMNOPQRSTUVWXYY |
+----+----------------------------+
组4行(0.00秒)
如您所见，记录3和4不会改变位置
As you see records 3 and 4 don't change places
我进行了这样的查询，并且某些字母不会更改顺序，例如A和a.
I did such query and some letters does not change an order, for example, A and a.
mysql> SELECT * FROM test ORDER BY标题COLLATE utf8_unicode_ci ASC;
mysql> SELECT * FROM test ORDER BY title COLLATE utf8_unicode_ci ASC;
+----+---------+
| id | title |
+----+---------+
| 1 | A |
| 27 | a |
| 28 | b |
| 2 | B |
| 29 | c |
| 3 | C |
| 4 | D |
| 30 | d |
| 31 | e |
| 5 | E |
| 6 | F |
| 32 | f |
| 33 | g |
| 7 | G |
| 34 | h |
| 8 | H |
| 35 | i |
| 9 | I |
| 36 | j |
| 10 | J |
| 11 | K |
| 37 | k |
| 12 | L |
| 38 | l |
| 39 | m |
| 13 | M |
| 40 | n |
| 14 | N |
| 41 | o |
| 15 | O |
| 42 | p |
| 16 | P |
| 17 | Q |
| 43 | q |
| 44 | r |
| 18 | R |
| 19 | S |
| 45 | s |
| 20 | T |
| 46 | t |
| 21 | U |
| 47 | u |
| 48 | v |
| 22 | V |
| 49 | w |
| 23 | W |
| 50 | x |
| 24 | X |
| 25 | Y |
| 51 | y |
| 26 | Z |
| 52 | z |
+----+---------+
mysql> SELECT * FROM test ORDER BY标题COLLATE utf8_unicode_ci DESC;
mysql> SELECT * FROM test ORDER BY title COLLATE utf8_unicode_ci DESC;
+----+---------+
| id | title |
+----+---------+
| 52 | z |
| 26 | Z |
| 25 | Y |
| 51 | y |
| 50 | x |
| 24 | X |
| 49 | w |
| 23 | W |
| 48 | v |
| 22 | V |
| 47 | u |
| 21 | U |
| 20 | T |
| 46 | t |
| 45 | s |
| 19 | S |
| 18 | R |
| 44 | r |
| 17 | Q |
| 43 | q |
| 16 | P |
| 42 | p |
| 41 | o |
| 15 | O |
| 40 | n |
| 14 | N |
| 39 | m |
| 13 | M |
| 12 | L |
| 38 | l |
| 11 | K |
| 37 | k |
| 10 | J |
| 36 | j |
| 9 | I |
| 35 | i |
| 8 | H |
| 34 | h |
| 7 | G |
| 33 | g |
| 32 | f |
| 6 | F |
| 5 | E |
| 31 | e |
| 4 | D |
| 30 | d |
| 29 | c |
| 3 | C |
| 2 | B |
| 28 | b |
| 1 | A |
| 27 | a |
+----+---------+
我认为这是排序规则的错误.
I think that it is the bug with collation.
也许有人碰到过这种服务器行为吗?
May be someone ran into such conduct of server?
推荐答案
如果尝试怎么办?
SELECT *
FROM test
ORDER BY title ASC
COLLATE utf8_unicode_ci;
您尝试过吗:
INSERT INTO test (id, title)
VALUES
(101, 'A'),
(102, 'B'),
(103, 'C'),
...
(126, 'Z');
SELECT *
FROM test
ORDER BY title ASC
;
要查看问题(或错误)是否与长字符串或排序规则有关?
To see if the problem (or bug) is related to long strings or to collation?
这篇关于这是MySQL排序错误吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
