MySQL syntax variation

Question

I am learning Php/MySQL from online tutorials (not that I expect you to watch it, but as a reference: http://www.youtube.com/watch?v=rJ3tDQfJt4k)

I am using MySQL 5.5.27 through phpMyAdmin 3.5.2.2

In the tutorial, this code is given (and works):

INSERT INTO 'posts' ('title', 'contents') VALUES('this is the first post', 'Yes it is.')

However, I receive this error message: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use..."

This syntax (which I found through trial and error) works for me:

INSERT INTO posts (title, contents) VALUES('this is the first post', 'Yes it is.')

I would like someone to explain why this is the case, and how to identify correct syntax - or to be linked to some documentation which would make this distinction comprehensible for a beginner. (I am unlikely to be able to follow the manual at this point in my learning.) Thanks for any help you can provide.

Answer 1

By default, identifiers can be escaped with backquotes, not single quotes. The default condition is due to the sql_mode mysqld starts with.

You could use double quotes if you configure ANSI_QUOTES for the sql_mode.

I wrote about this in the DBA StackExchange : How to convert MySQL Keyword to normal column

Answer 2

MySQL identifiers (column and table names) are optionally encased by backticks "`", not single quotes. The video is a little blurry and I can see why you would mistake them. Try:

INSERT INTO `posts` (`title`, `contents`) VALUES('this is the first post', 'Yes it is.')