Why doesn’t ORDER BY ‘id’ ‘desc’ return a syntax error?


I was testing some apparently wrong code and was quite sure it should return a syntax error. But it didn’t. The following query works without errors (albeit doesn’t sort the table either, which at least meets my expectations):

SELECT * FROM dummy ORDER BY 'id' 'desc';

Interestingly, that

SELECT * FROM dummy ORDER BY id 'desc';

does produce a syntax error.

How does MySQL interpret two strings after ORDER BY? What does it take these strings for? Here is the MCVE


SELECT * FROM dummy ORDER BY 'id' 'desc';

evaluates to

SELECT * FROM dummy ORDER BY 'iddesc';

I.e. ORDER BY a (constant) string literal – which doesn’t affect the ORDER BY at all.

Note: This is ANSI/ISO SQL standard, a character literal can be built up by several parts, without having explicit concatenation.


根据答案测试了下,SELECT * FROM dummy ORDER BY 'id' 'desc'; 在MySQL确实能够执行成功,结果也不是按照id的值进行排序,如图1所示:

mysql order by

正是像答案说的那样实际情况是按照iddesc这个常量值进行排序。用SQL Server、Postgresql这两个数据测试了下发现两种写法都会报错。在实际开发中大家还是按照常见的标准语法写比较好,也可避免这类问题的出现。SQL语句见dbfiddle


上一篇 2022-02-21 20:51
下一篇 2022-02-22 22:02



您的电子邮箱地址不会被公开。 必填项已用*标注