如果在MySQL查询的排序列中所有数值都为空或为NULL,会发生什么?

huangapple go评论65阅读模式
英文:

What happens if all the Values in the SORT column in MySQL Query are blank or Empty or NULL?

问题

在这个查询中,

SELECT colA, colB, colC, colD FROM myTable ORDER BY colC ASC;

在这种情况下,如果在myTable表的colC列中所有值都为空/空白/NULL,MySQL将如何行为呢?我知道查询仍然会执行而不会出现错误。但我对如果该列的所有值都为空/空白/NULL时排序将如何行为感到好奇。在结果集上排序将在哪一列上进行呢?

SELECT colA, colB, colC, colD FROM myTable ORDER BY colC ASC;

英文:

I have a query like this,

SELECT colA, colB, colC, colD FROM myTable
ORDER BY colC ASC;

Here, in myTable, all the values in the column colC are Empty/Blank/NULL. Now, how MySQL will behave in this scenario? I know the query will still execute without errors. But I am curious about how the SORTING will behave if all values of that mentioned column are Empty/Blank/NULL. On what column, the SORTING will happen on the result set?

SELECT colA, colB, colC, colD FROM myTable
ORDER BY colC ASC;

答案1

得分: 1

如果colC中的所有值都相同,则行将以任意顺序返回。
请注意,"arbitrary" 并不一定是随机的 - 每次运行查询时可能不太可能获得不同的行顺序,但如果用于order by子句的列在查询返回的所有行中包含相同的值,则数据库无法保证任何特定的行顺序。

请注意,null 不是 blank 也不是 empty,而是 unknown - 例如,字符串列如 charvarchar 可能包含空字符串 - 但空字符串不等同于 null

英文:

If all the values in colC are the same, the rows will be returned in an arbitrary order.
Please note that arbitrary isn't necessarily random - its not likely you'll get a different order of rows each time you run the query, however there's no way for the database to guarantee any particular order of the rows if the column used in the order by clause contains the same values in all the rows returned by the query.

Please note that null is not blank nither empty, but rather unknown - a string column such as char or varchar might contain an empty string - but an empty string is not equivalent to null.

答案2

得分: 0

colC中的空值/空白/NULL值的行将根据使用的MySQL特定实现或版本,可能出现在排序结果集的开头或结尾。

英文:

The rows with empty/blank/NULL values in colC will likely appear together at either the beginning or the end of the sorted result set, depending on the specific implementation or version of MySQL being used.

huangapple
  • 本文由 发表于 2023年6月26日 18:46:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/76555946.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定