parsing - How can I fix MySQL error #1064? -

when issuing command mysql, i'm getting error #1064 "syntax error".

1. what mean?

2. how can fix it?

tl;dr

error #1064 means mysql can't understand command. fix it:

• read error message. tells exactly in command mysql got confused.

• check manual. comparing against mysql expected @ point, problem obvious.

• check reserved words. if error occurred on object identifier, check isn't reserved word (and, if is, ensure it's quoted).

1. aaaagh!! #1064 mean?

   error messages may look gobbledygook, they're (often) incredibly informative , provide sufficient detail pinpoint went wrong. understanding mysql telling you, can arm fix problem of sort in future.

   as in many programs, mysql errors coded according type of problem occurred. error #1064 syntax error.

   • what "syntax" of speak? witchcraft?

     whilst "syntax" word many programmers encounter in context of computers, in fact borrowed wider linguistics. refers sentence structure: i.e. the rules of grammar; or, in other words, rules define constitutes valid sentence within language.

     for example, following english sentence contains syntax error (because indefinite article "a" must precede noun):

     this sentence contains syntax error a.

   • what have mysql?

     whenever 1 issues command computer, 1 of first things must "parse" command in order make sense of it. "syntax error" means parser unable understand being asked because not constitute valid command within language: in other words, the command violates grammar of programming language.

     it's important note computer must understand command before can it. because there syntax error, mysql has no idea 1 after , therefore gives before looks @ database , therefore schema or table contents not relevant.

2. how fix it?

   obviously, 1 needs determine how command violates mysql's grammar. may sound pretty impenetrable, mysql trying hard here. need is…

   • read message!

     mysql not tells exactly parser encountered syntax error, makes suggestion fixing it. example, consider following sql command:

     update my_table id=101 set name='foo' 

     that command yields following error message:

     error 1064 (42000): have error in sql syntax; check manual corresponds mysql server version right syntax use near 'where id=101 set name='foo'' @ line 1

     mysql telling seemed fine word where, problem encountered. in other words, wasn't expecting encounter where @ point.

     messages ...near '' @ line... mean end of command encountered unexpectedly: is, else should appear before command ends.

   • obey orders!

     mysql recommending "check manual corresponds our mysql version right syntax use". let's that.

     i'm using mysql v5.6, i'll turn that version's manual entry update command. first thing on page command's grammar (this true every command):

     update [low_priority] [ignore] table_reference     set col_name1={expr1|default} [, col_name2={expr2|default}] ...     [where where_condition]     [order ...]     [limit row_count] 

     the manual explains how interpret syntax under typographical , syntax conventions, our purposes it's enough recognise that: clauses contained within square brackets [ , ] optional; vertical bars | indicate alternatives; , ellipses ... denote either omission brevity, or preceding clause may repeated.

     we know parser believed in our command okay prior where keyword, or in other words , including table reference. looking @ grammar, see table_reference must followed set keyword: whereas in our command followed where keyword. explains why parser reports problem encountered @ point.

   a note of reservation

   of course, simple example. however, following 2 steps outlined above (i.e. observing exactly in command parser found grammar violated , comparing against manual's description of what expected @ point), virtually every syntax error can readily identified.

   i "virtually all", because there's small class of problems aren't quite easy spot—and parser believes language element encountered means 1 thing whereas intend mean another. take following example:

   update my_table set where='foo' 

   again, parser not expect encounter where @ point , raise similar syntax error—but hadn't intended where sql keyword: had intended identify column updating! however, documented under schema object names:

   if identifier contains special characters or reserved word, must quote whenever refer it. (exception: reserved word follows period in qualified name must identifier, need not quoted.) reserved words listed @ section 9.3, “reserved words”.

   [ deletia ]

   the identifier quote character backtick (“`”):

   mysql> select * `select` `select`.id > 100;

   if ansi_quotes sql mode enabled, permissible quote identifiers within double quotation marks:

   mysql> create table "test" (col int); error 1064: have error in sql syntax... mysql> set sql_mode='ansi_quotes'; mysql> create table "test" (col int); query ok, 0 rows affected (0.00 sec)