MySQL Cookbook, 2nd Edition

Handling Special Characters in Identifiers

Problem

You need to construct SQL statements that refer to identifiers containing special characters.

Solution

Quote the identifiers so that they can be inserted safely into statement strings.

Discussion

Handling Special Characters and NULL Values in Statements discusses how to handle special characters in data values by using placeholders or quoting methods. Special characters also can be present in identifiers such as database, table, and column names. For example, the table name some table contains a space, which is not allowed by default:

mysql>CREATE TABLE some table (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax near 'table (i INT)'

Special characters are handled differently in identifiers than in data values. To make an identifier safe for insertion into an SQL statement, quote it by enclosing it within backticks:

mysql>CREATE TABLE `some table` (i INT);
Query OK, 0 rows affected (0.04 sec)

If a quoting character appears within the identifier itself, double it when quoting the identifier. For example, quote abc`def as `abc``def`.

In MySQL, backticks are always allowed for identifier quoting. If the ANSI_QUOTES SQL mode is enabled, the double-quote character also is legal for quoting identifiers. Thus, both of the following statements are equivalent with the ANSI_QUOTES SQL mode enabled:

CREATE TABLE `some table` (i INT);
CREATE TABLE "some table" (i INT);

If it’s necessary to know which identifier quoting characters are ...