Shulou(Shulou.com)06/01 Report--

MySQL order by sorting result is incorrect

Create a new test table:

CREATE TABLE `tb1` (

`id` bigint (20) NOT NULL AUTO_INCREMENT

`a` decimal (19pr 2) NOT NULL

`acid` bigint (20) NOT NULL

`prid` bigint (20) NOT NULL

PRIMARY KEY (`id`)

KEY `idx_ prid` (`prid`)

KEY `idx_ acid` (`acid`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

Field a has no index, insert test data:

INSERT INTO `tb1` (`id`, `a`, `acid`, `prid`)

VALUES (1pyrrine 2.00pyrr3), (2pjor3.00pyr2), (3pjre 4.00pyr2), (4pjor5.00pyr2), (5pyrrp2), (6pr 8.00pyrr2), (7ml10.00pyr2), (8pr 12.00pyr2), (9pr 16.00 2pr 3), (10jie 20.00 2pr 3), (11pr 6.00pyrus 2pr 4), (12jue 8.00pyrrine 2pen4), (13jue 10.00pr 4), (14rec 12.00pr 2pr 4), (15pr 5.00pr 2pr 2), (166.00 pr 2), (166.00 pr 2)

View table data:

([yoon] > select * from tb1

+-- +

| | id | a | acid | prid | |

+-- +

| | 1 | 2.00 | 3 | 2 | |

| | 2 | 3.00 | 3 | 2 |

| | 3 | 4.00 | 2 | 3 |

| | 4 | 5.00 | 2 | 3 | |

| | 5 | 6.00 | 2 | 3 |

| | 6 | 8.00 | 2 | 3 |

| | 7 | 10.00 | 2 | 3 | |

| | 8 | 12.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 10 | 20.00 | 2 | 3 | |

| | 11 | 6.00 | 2 | 4 |

| | 12 | 8.00 | 2 | 4 |

| | 13 | 10.00 | 2 | 4 | |

| | 14 | 12.00 | 2 | 4 |

| | 15 | 5.00 | 2 | 2 |

| | 16 | 6.00 | 2 | 2 |

+-- +

Order by sort by field a that is not an indexed field and has duplicate data:

([yoon] > select * from tb1 order by a desc

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

| | 13 | 10.00 | 2 | 4 | |

| | 7 | 10.00 | 2 | 3 | |

| | 12 | 8.00 | 2 | 4 |

| | 6 | 8.00 | 2 | 3 |

| | 11 | 6.00 | 2 | 4 |

| | 16 | 6.00 | 2 | 2 |

| | 5 | 6.00 | 2 | 3 |

| | 4 | 5.00 | 2 | 3 | |

| | 15 | 5.00 | 2 | 2 |

| | 3 | 4.00 | 2 | 3 |

| | 2 | 3.00 | 3 | 2 |

| | 1 | 2.00 | 3 | 2 | |

+-- +

Order by is used with limit:

([yoon] > select * from tb1 order by a desc limit 4

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

+-- +

Add an index to the a field:

([yoon] > alter table tb1 add index idx_a (a)

Query OK, 0 rows affected (0.05 sec)

([yoon] > select * from tb1 order by a desc limit 4

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

+-- +

Add another field, id, when sorting:

([yoon] > select * from tb1 order by a desc,id desc limit 4

+-- +

| | id | a | acid | prid | |

+-- +

| | 10 | 20.00 | 2 | 3 | |

| | 9 | 16.00 | 2 | 3 |

| | 14 | 12.00 | 2 | 4 |

| | 8 | 12.00 | 2 | 3 | |

+-- +

For a non-unique field, the result set is uncertain regardless of whether it contains an index or not. If the business logic has high requirements for paging or order by result sets, remember to sort with a unique key.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.