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 🙂