mysql - How "ORDER BY" ordered if there are same value?

Question

Let's say there's table:

name | score
A      100
B      98
C      99
D      99
E      99
F      98

and request sql = 'select * from table order by score desc'

I know order's gonna be 100, 99, 99, 99, 98, 98 but there are several values in 99 and 98.

I'm using MyIsam and want to make sure the order in the 99 and 98 is not changed even though I delete some element. For example, if the order was C->D->E and I delete D, then I expect to be C->E but no E->C. Is there any logic it works when there's same order value?

标签： mysqlsqlsql-order-bymyisam

Answer 1

SQL 表表示无序集。order by因此，当键具有相同的值时，没有“默认”排序。

从技术上讲，这意味着 SQL 中的排序是不稳定的。解决方法是添加一个或多个附加键，以便每个组合都是唯一的。

对于您的示例数据，这大概可以使用以下方法处理name：

order by score desc, name

如果可用，您还可以使用唯一标识每一行的任何其他列。这些键在score.