MySQL Error 1064
By gc on Nov 05, 2009
Seeing following error when adding new records to mysql DB:
ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, ......
The sql command looks like "insert into table1 (user_id, math, writing, read, ...) values (...". Goggling this error brings up lots of links to forum discussions. I read a few but none tells the root cause. Somehow realized that "read" (a column in table1) is a reserved word in sql. Once I remove this column, this command can be executed without problem.
I then quickly resolved this issue by changing column name 'read' to 'reading'. If you want to keep the column name, there are two choices. One is to quote this column name with backtick (“`”):
insert into table1 (user_id, math, writing, `read`, ...) values (...
If the ANSI_QUOTES SQL mode is enabled, it is also allowable to quote identifiers within double quotes ("SET sql_mode='ANSI_QUOTES'). Or you can prefix the column name with table name:
insert into table1 (user_id, math, writing, table1.read, ...) values (...
More details can be found in MySQL 5.0 Reference Manual::8.3 Reserved Words and MySQL 5.0 Reference Manual::8.2 Schema Object Names.