MySQL Error 1064

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.

Comments:

Post a Comment:
Comments are closed for this entry.
About

gc

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today