Syntax error due to using a reserved word as a table or column name in MySQL -

i'm trying execute simple mysql query below:

insert user_details (username, location, key) values ('tim', 'florida', 42) 

but i'm getting following error:

you have error in sql syntax; check manual corresponds mysql server version right syntax use near 'key) values ('tim', 'florida', 42)' @ line 1

how can fix issue?

the problem

in mysql, words select, insert, delete etc. reserved words. since have special meaning, mysql treats syntax error whenever use them table name, column name, or other kind of identifier - unless surround identifier backticks.

as noted in official docs, in section 10.2 schema object names (emphasis added):

certain objects within mysql, including database, table, index, column, alias, view, stored procedure, partition, tablespace, , other object names known identifiers.

...

if identifier contains special characters or reserved word, must quote whenever refer it.

...

the identifier quote character backtick ("`"):

a complete list of keywords , reserved words can found in section 10.3 keywords , reserved words. in page, words followed "(r)" reserved words. reserved words listed below, including many tend cause issue.

• add
• and
• before
• by
• call
• case
• condition
• delete
• desc
• describe
• from
• group
• in
• index
• insert
• interval
• is
• key
• like
• limit
• long
• match
• not
• option
• or
• order
• partition
• references
• select
• table
• to
• update
• where

the solution

you have 2 options.

1. don't use reserved words identifiers

the simplest solution avoid using reserved words identifiers. can find reasonable name column not reserved word.

doing has couple of advantages:

• it eliminates possibility or developer using database accidentally write syntax error due forgetting - or not knowing - particular identifier reserved word. there many reserved words in mysql , developers unlikely know of them. not using these words in first place, avoid leaving traps or future developers.

• the means of quoting identifiers differs between sql dialects. while mysql uses backticks quoting identifiers default, ansi-compliant sql (and indeed mysql in ansi sql mode, noted here) uses double quotes quoting identifiers. such, queries quote identifiers backticks less portable other sql dialects.

purely sake of reducing risk of future mistakes, wiser course of action backtick-quoting identifier.

2. use backticks

if renaming table or column isn't possible, wrap offending identifier in backticks (`) described in earlier quote 10.2 schema object names.

an example demonstrate usage (taken 10.3 keywords , reserved words):

mysql> create table interval (begin int, end int); error 1064 (42000): have error in sql syntax. near 'interval (begin int, end int)'  mysql> create table `interval` (begin int, end int); query ok, 0 rows affected (0.01 sec)

similarly, query question can fixed wrapping keyword key in backticks, shown below:

insert user_details (username, location, `key`) values ('tim', 'florida', 42)";               ^   ^