How to Escape Reserved Keywords in MySQL `CREATE TABLE` Statements?

Escaping Reserved Keywords as Column Names in MySQL Create Table Statements

When creating database tables from class fields, it's possible to encounter reserved keywords as field names. These keywords need to be escaped to avoid SQL syntax errors.

MySQL provides two options for escaping reserved keywords in create table statements:

Using Double Quotes

If ANSI SQL mode is enabled, you can use double quotes to enclose reserved keywords. 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

If ANSI SQL mode is not enabled, or if you prefer the MySQL-specific syntax, you can use back ticks to escape reserved keywords. 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;

Note that the back tick character (`) is not the same as the single quotation mark ('). The back tick is typically located below the ESC key on most keyboard layouts.

Remember, the escaping rules apply not only to reserved keywords but also to any identifiers that start with a number or contain special characters. By following these guidelines, you can ensure that your table creation statements are syntactically correct even when dealing with sensitive column names.

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