英文:
Can I get an ORDER BY with several columns but still deterministic?
问题
我知道一个确定性的ORDER BY是当你按照一个UNIQUE INDEX列排序时，这个说法正确吗？
我想按照name和percentage这两列进行排序。
有可能有2个或更多的学生具有相同的name和percentage，因此，在这种情况下，ORDER BY将是非确定性的，因为MySQL会定义哪个放在前面，哪个放在后面（而不是我），这个说法正确吗？
所以，我的第三个问题（主要问题）是，如果我想按多列排序但仍然保持确定性的顺序，我应该在所有列的末尾使用一个UNIQUE INDEX列，例如：
ORDER BY name, percentage, id？
考虑到id是primary key。
我仍然不确定我是否正确理解了确定性和非确定性。
英文:
I know that a deterministic ORDER BY is when you sort by a UNIQUE INDEX column, 1) is this true?.
I want to sort by the columns name and percentage.
There is a possibility that 2 or more students have the same name and the same percentage, therefore, the ORDER BY in that case would be non-deterministic because MySQL would define which one to put first and which one to put after (not me), 2) is this true?
So, my third question is (the main one), if I want to sort by several columns but still have a deterministic order, should I use a UNIQUE INDEX column at the end of all my columns, e.g.
ORDER BY name, percentage, id?
Considering that the id is the primary key
I still don't know if I understand deterministic and non-deterministic correctly.
答案1
得分: 3
- 不一定。如果结果在
ORDER BY列中不包含任何重复项，那么顺序将是确定的。 - 是的，如果存在重复项，结果将是不确定的。
- 是的，将唯一列添加到
ORDER BY可以使其变得确定性。
这是否重要取决于您的应用程序。
如果您正在进行基于查询的复制并且使用INSERT INTO table SELECT ...，那么确定性排序可能很重要，否则主服务器和复制服务器之间可能会出现不一致的AUTO_INCREMENT ID。
英文:
- Not necessarily. If the results don't contain any duplicates in the
ORDER BYcolumns, the order will be deterministic. - Yes, if there are duplicates the the results are non-deterministic.
- Yes, adding a unique column to the
ORDER BYmakes it deterministic.
Whether this matters depends on your application.
It can be important if you're doing query-based replication and you use INSERT INTO table SELECT .... Without deterministic ordering, you may get inconsistent AUTO_INCREMENT IDs between the master and replicas.
答案2
得分: 0
回答您的问题：
- 是的
- 是的，但更具体地说：MySQL使用快速排序算法来对数据进行排序，这导致了随机性。
- 如果您需要确定性查询，那么是的，这是一个不错的方式。
英文:
To answer your questions:
- Yes
- Yes, but to be more concrete: It's the quick sort algorithm which mysql uses to sort the data that causes the randomness.
- If you need a deterministic query then yes, this is a good way.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论