Don't use identifier quoting in ORM Doctrine-CodePudding

There is a Best Practices page in official Doctrine documentation.

https://www.doctrine-project.org/projects/doctrine-orm/en/2.14/reference/best-practices.html#don-t-use-identifier-quoting It's said: "Don't use identifier quoting". I don't quite understand that. Can you explain me what is identifier quoting and why it's not rocommended to use it? What to use instead? Please write some SQL queries for the example.

CodePudding user response：

Normally you can't make a table whose name conflicts with an SQL reserved keyword, or contains punctuation or whitespace.

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'

But you can delimit an identifier with back-ticks (or double-quotes in standard SQL) to allow tables with normally illegal names.

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

Doctrine is recommending that you solve this problem by just avoiding naming your tables in a way that requires them to be delimited. They are not specific about the edge cases they think will happen.

One could be if you try to create a table whose name contains a literal back-tick character. This is permitted too, but it requires care to escape the back-tick.

mysql> create table `my``table` (begin INT, end INT);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
 ---------------- 
| Tables_in_test |
 ---------------- 
| my`table       |
 ----------------

The other problem is that you must remember to delimit illegal table names in every query that references them. Since Doctrine generates a lot of SQL, it's possible they don't think they can do this consistently, and their solution is to put the responsibility on you to avoid the issue.

Their solution to avoid tables that have reserved words has a problem: every release of MySQL has new reserved words. A table that is allowed in MySQL 5.7 may conflict with a new reserved word in MySQL 8.0. If you can't delimit table names, then you must study the list of new reserved words in 8.0 and rename tables before you upgrade.

In fact, in the MySQL 8.0 era, they are more comfortable introducing new backward-incompatible features in minor releases, so there could be new reserved words introduced at any time. For example, FULL was made a keyword in MySQL 8.0.32, so it now generates a warning:

mysql> create table full ( i int);
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
 --------- ------ ---------------------------------------------------------------------------------------------- 
| Level   | Code | Message                                                                                      |
 --------- ------ ---------------------------------------------------------------------------------------------- 
| Warning | 4119 | Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier. |
 --------- ------ ----------------------------------------------------------------------------------------------