order by子句在mysql中如何工作，排序显示了奇怪的行为 [英] How order by clause works in mysql, ordering shows wierd behaviour

问题描述

我有一个简单的查询

SELECT * FROM table_name order by non_unique_column LIMIT 0,50

这给了我一些记录的列表.

This gives me list of records in some order.

但是当我从表中删除*并将some column names放入时，它正在更改列表顺序.

But when I am removing * and putting some column names from the table, then it is changing the list order.

对于select子句中列的some组合，它给出了不同的顺序.

For some combination of columns in select clause it is giving different order.

我找不到select子句中哪种列名模式给出不同的顺序.

I am not able to find, what pattern of column names in select clause gives different ordering.

所以我问有人有这种问题吗?还是可以建议在这种情况下order by子句如何工作?

So I am asking does any one had this kind of problem? or can any one suggest how the order by clause works in this scenario?

注意::如果我在select子句中添加所有列，则结果与*相同.

Note: If I am adding all the columns in select clause it gives me same result as *.

请随时提出任何可能的建议.

Please feel free to suggest any possible suggestion.

谢谢.

更新:另外，当我将诸如LIMIT 0,50之类的限制条款更改为LIMIT 0,15时，我将获得不同的记录集.

Update: Also when I am changing limit clause like LIMIT 0,50 to LIMIT 0,15, I am getting different sets of records.

Update2:

我创建了一个sqlfiddle:

I have created a sqlfiddle :

http://sqlfiddle.com/#!2/8f2340/3

请从以下查询中查看结果

Please see the result from below queries

SELECT `M_ID`, `CON_FRST_NM`, `CON_PREFIX`, `CON_LST_NM`, `CON_MOB`,`CON_FAX` FROM `sample_table` order by `CON_PREFIX` LIMIT 0,60 

SELECT `M_ID`, `CON_FRST_NM`, `CON_PREFIX`, `CON_LST_NM`, `CON_MOB` FROM `sample_table` order by `CON_PREFIX` LIMIT 0,60 

查询中唯一的区别是第二个查询的select子句中没有CON_FAX.

The only difference in the query is CON_FAX is not present in select clause of second query.

请帮助我，为什么会出现这种差异.

Please help me out why this difference is coming.

另外，请参阅以下带有不同limit子句的查询结果. 注意:这是上述查询中的第二个查询.

And also please see the result of below queries with different limit clause. Note: It is second query from above queries.

SELECT `M_ID`, `CON_FRST_NM`, `CON_PREFIX`, `CON_LST_NM`, `CON_MOB` FROM `sample_table` order by `CON_PREFIX` LIMIT 0,60 

SELECT `M_ID`, `CON_FRST_NM`, `CON_PREFIX`, `CON_LST_NM`, `CON_MOB` FROM `sample_table` order by `CON_PREFIX` LIMIT 0,15

谢谢.

个人意见:我认为这是一个错误/缺陷，因为即使我还没有定义任何唯一的order by子句，并且mysql根据其计算结果应用了一些随机排序，我觉得这不应该取决于select子句中的内容.我的问题是对于给定的记录集，无论我选择什么列，它都应返回相同的有序记录.

推荐答案

此问题是因为您为订​​单指定了重复的值(又称先生"或太太")，因此无法保证选择的订单这是因为他们都是先生或太太.

The issue with this is because there is a duplicated value that you specify for your order by aka its either Mr. or Mrs. there is no guaranteed select order with this because they are all Mr. or Mrs.

如果您想确保它始终按特定顺序排列，则还应将主键作为第二个顺序包含在内，以使它们保持相同..aka

if you want to ensure that it is always going to be in a specific order then you should also include the primary key as a second ordering to keep it all the same.. aka

ORDER BY CON_PREFIX ASC, M_ID ASC

目前的状态ORDER BY CON_PREFIX确实为您提供了应该给您的东西，前缀按升序排列，与导致这种情况的限制没有任何关系，您只是没有告诉MySQL您还想要什么?数据返回给您.

as it stands right now ORDER BY CON_PREFIX is giving you exactly what it should be giving you, the prefixes in ascending order, there is nothing related to the limit for causing this, you simply just havent told MySQL how else you want the data to be returned to you.

回答您在问题中的PERSONAL OPINION编辑. 您所描述的内容是不合理的，请以这种方式考虑，因为指定了排序依据，因此用于提取数据的任何默认顺序现在都消失了.如果子句上没有排序依据，则mysql具有通用的选择模式，但是一旦在查询中放置排序依据，该模式就会消失

to respond to your PERSONAL OPINION edit in the question.... what you are describing is unreasonable, think of it this way any default order that would be used to pull out data is now gone because you are specifying an order by. if there wasn't any order by on the clause then mysql has a generic select pattern but again that is gone once you put an order by on the query

这篇关于order by子句在mysql中如何工作，排序显示了奇怪的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！