MySQL INSERT Syntax Error key
This entry was posted on Nov 24 2008
Problem
I was working on the Vote page for the Rice Computer Science Club for a Tshirt design and I got this error.
Error inserting into databse: 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 'key) VALUES ('asdfasd','asdfasdf','283a7384578c2')' at line 1
And here’s the whole query…
INSERT INTO users (netid,name,key) VALUES ('asdfasd','asdfasdf','283a7384578c2')
The
users table structure:-- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `userid` int(11) NOT NULL auto_increment, `netid` varchar(7) NOT NULL default '', `name` varchar(40) NOT NULL default '', `key` varchar(64) NOT NULL default '', PRIMARY KEY (`userid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
For the life of me, I couldn’t figure out what was going on. I read through the entire MySQL Manual on doing INSERTS. As far as I could tell, I was doing everything exactly right.
Solution
Turns out it’s the word “key”. There’s obviously other meanings for “key” than just the name of my table column, and unless you distinguish the column name from the other “key”, MySQL will get confused.
Surround your potentially-confusing column names with `s, like so:
INSERT INTO users (netid,name,`key`) VALUES ('asdfasd','asdfasdf','283a7384578c2')
` <- the apostrophe-ish mark sharing a key with the ~.
Hey! Found this post after experiencing a similar problem. It was solved using those characters as well. However, my column does not use the word key so I am still at a loss as to what was causing the problem. Have you found any resources as to what root causes could potentially result in this error? I need to avoid this in the future as it was very time consuming to figure out the problem!