Question 20065 in Mysql Question posted in
The official MySQL documentation can be found here.

My MySQL doesn't seem to use " as field quotes in the ORDER BY clause?

If I run the folowing query in MySQL 8.0.28:

SELECT 2 as "A A"
UNION ALL
SELECT 1 

ORDER BY "A A"

I get

A A
2
1

If I run

SELECT 2 as "A A"
UNION ALL
SELECT 1 

ORDER BY `A A`

I get

A A
1
2

like I expect.

Does it mean MySQL doesn’t use "double quoted fields" in the ORDER BY, and my ORDER BY "A A" is being interpreted as if it were ORDER BY 'A A' (i.e. order by constant string)? Can this behavior be altered by a setting?

Tags:

3

Answers


  1. 0

    Strings inside double quotes are treated as strings, not as a column alias.

    Use backticks when you have special charcters (like spaces) in the column aliases.

    Login or Signup to reply.
  2. 0

    In MySQL, double quotes are using to enclose string literals, whereas backticks are used to enclose identifiers such as column names ,table names.

    Since the backticks provide an extra level of safety, they are used extensively in program-generated SQL statements, where the identifier names might not be known in advance.

    Many other database systems use double quotation marks (") around such special names. For portability, you can enable ANSI_QUOTES mode in MySQL and use double quotation marks instead of backticks to qualify identifier names.

    for more detail: backticks

    Login or Signup to reply.
  3. 0

    This is an unfortunate ambiguity in MySQL. Column aliases can be defined either with identifier syntax or by string literal syntax. This is described in https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

    By default in MySQL, double-quotes are the same as single-quotes by default. They are both string literal delimiters.

    This is string literal syntax in MySQL:

    SELECT 2 as "A A"
...

    This is identifier syntax in MySQL:

    SELECT 2 as `A A`
...

    But when you reach the ORDER BY, you must use identifiers. If you order by a string literal, then it counts as a constant value. Sorting by a constant value results in a tie for all rows, so the order ends up being whatever order the rows were read in, not the order you wanted.

    You can make double-quotes act as identifier delimiters by changing the SQL mode.

    mysql> select "abc";
+-----+
| abc |
+-----+
| abc |
+-----+

mysql> set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');
Query OK, 0 rows affected (0.00 sec)

mysql> select "abc";
ERROR 1054 (42S22): Unknown column 'abc' in 'field list'

    The error is expected, this is just to demonstrate that abc is treated as an identifier instead of a string after I changed the SQL mode.

    You can of course make the change to SQL mode persistent by putting it in your my.cnf file (not the CONCAT() usage but spell out the whole SQL mode string with other appropriate SQL modes):

    [mysqld]
sql_mode = ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search