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所示：

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