CODEWITHSUNDEEP
mysqlsqlsyntax-errormariadbddl
user5574289
user5574289

Reputation:

Why do I have to use BACKTICKS when identifier is not a reserved word or with spaces?

No ERROR on backticks.

DB [XXX]> create temporary table `123456e6` (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

ERROR when not using backticks.

DB [XXX]> create temporary table 123451e6 (`id` char (8));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123451e6 (`id` char (8))' at line 1

My identifier 123451e6 is valid as per page here and is not a reserved word or with spaces or contains any special character.

Upvotes: 2

Views: 125

Answers (2)

Mario
Mario

Reputation: 36487

It's not valid because something in the form of 12345e6 evaluates to a number, specifically 12345 * 106 in this example. Therefore you'll have to explicitly mark it as a string.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311228

You've encountered a subtle case of the "digit only" rule:

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

The e character is used for scientific notation of numbers, and MySQL interprets 123451e6 as "123451 * 106" or 123451000000. Any other letter but "e" would cause the string to adhere to the "not digits only" rule and could be used unquoted. E.g.:

MariaDB [db]> create temporary table 123451f6 (`id` char (8));
Query OK, 0 rows affected (0.02 sec)

Upvotes: 4

Related Questions