Skip to content
Advertisement

Why do I need to quote the table name `5e09c9c09e9ba0cceccb3701` in MySQL?

Context

I encounter a bug in our application related to MySQL table names. Our application generates table names as a 24 hexadecimal digits number (i.e.: 5e09c9c09e9ba0cceccb3701).

Everything went fine for a while, but suddenly we started to see SQL syntax errors with certain table names. The bug was easy to fix : we needed to protect the table name.

SQL syntax error :

select * from 5e09c9c09e9ba0cceccb3701;

No error :

select * from `5e09c9c09e9ba0cceccb3701`;

Question

Why table names starting with 5e09 need to be protected ?

I went through the MySQL documentation for reserved words or hexadecimal literal notation, but I cannot find any explanation for the special interpretation of 5e09 in a SQL statement.

And, if anyone knows, what other special prefix might also break the syntax of an SQL statement ?

Advertisement

Answer

I’d guess that 5E09 is parsed as a number (https://www.wolframalpha.com/input/?i=5E09) and the parser struggles with concatenating this number with the rest of the table name (speculation), since mysql allows table names starting with a number, but requires that it not solely consists of digits.

See https://dev.mysql.com/doc/refman/8.0/en/identifiers.html, quoting:

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

In other words, all table names starting with [0-9]E[0-9] should show the same behavior.

Just spun up a local mysql instance, the pattern seems to match:

  • 5e0x doesn’t work
  • 5f0x does work
  • 5eax does work

I’d consider filing this as a bug, albeit a low-priority one 🙂

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement