How do I escape reserved words used as column names MySQL Create Table

Question

I am generating tables from classes in  NET and one problem is a class may have a field name key which is a reserved MySQL keyword  How do I escape it in a create table statement   Note  The other problem below is text must be a fixed size to be indexed unique   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

User · Answer

You can use double quotes if ANSI SQL mode is enabled

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;

or the proprietary back tick escaping otherwise. (Where to find the ` character on various keyboard layouts is covered in this answer)

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;

(Source: MySQL Reference Manual, 9.3 Reserved Words)

User · Answer

If you are interested in portability between different SQL servers you should use ANSI SQL queries  String escaping in ANSI SQL is done by using double quotes      Unfortunately  this escaping method is not portable to MySQL  unless it is set in ANSI compatibility mode   Personally  I always start my MySQL server with the --sql-mode  ANSI  argument since this allows for both methods for escaping  If you are writing queries that are going to be executed in a MySQL server that was not setup   is controlled by you  here is what you can do    Write all you SQL queries in ANSI SQL Enclose them in the following MySQL specific queries   SET  OLD SQL MODE   SQL MODE  SET SESSION SQL MODE  ANSI   -- ANSI SQL queries SET SESSION SQL MODE  OLD SQL MODE     This way the only MySQL specific queries are at the beginning and the end of your  sql script  If you what to ship them for a different server just remove these 3 queries and you re all set  Even more conveniently you could create a script named  script mysql sql that would contain the above mode setting queries  source a script ansi sql script and reset the mode

User · Answer

You should use back tick character     eg   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

[mysql] How do I escape reserved words used as column names? MySQL/Create Table

Examples related to mysql

Examples related to escaping