How to Escape Reserved Words in MySQL CREATE TABLE Statements?

Escaping Reserved Words in MySQL CREATE TABLE Statements

In MySQL, reserved words cannot be used as column names without proper escaping. This can be encountered when generating tables from classes with field names that match reserved keywords. To resolve this, there are two methods available depending on the SQL mode configuration.

Using Double Quotes (ANSI SQL Mode)

If ANSI SQL mode is enabled, double quotes can be used to escape reserved words. For example:

CREATE TABLE IF NOT EXISTS misc_info
(
   id    INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
   "key" TEXT UNIQUE NOT NULL,
   value TEXT NOT NULL
)
ENGINE=INNODB; 

Using Back Ticks (Non-ANSI SQL Mode)

If ANSI SQL mode is disabled, the proprietary back tick character can be used instead. For example:

CREATE TABLE IF NOT EXISTS misc_info
(
   id    INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
   `key` TEXT UNIQUE NOT NULL,
   value TEXT NOT NULL
)
ENGINE=INNODB; 

By using one of these methods, reserved words can be used as column names without causing syntax errors. Note that proper escaping is essential to ensure the validity and integrity of the created table.

The above is the detailed content of How to Escape Reserved Words in MySQL CREATE TABLE Statements?. For more information, please follow other related articles on the PHP Chinese website!