Made of Everything You're Not

Here’s a problem I’ve encountered too many times and I, still, keep running up against:

1
2
3

#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 near 
'primary , foo , bar ) VALUES ( '1', 'test', 'me' )' at line 2

This on a query that looks like so:

1

$sql = "INSERT INTO table (primary , foo , bar ) VALUES ( '1', 'test', 'me' )";

Can you see the issue (other than it’s using php)? Look closely…

MySQL

The query is syntactically sound; there’s nothing wrong with the formatting at all. But it fails with a 1064 error because of the use of ‘primary’ as a column name. Welcome to the world of MySQL reserved keywords.

Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names.

This may also be true for the names of built-in functions.

Reserved words are permitted as identifiers if you quote them as described in Section 8.2, “Schema Object Names”

This means that there are certain words you just can’t use for MySQL identifiers unless you escape them, in all SQL, with backticks (`). Which essentially means, to me at least, you shouldn’t use reserved keywords. As a rule, I just don’t use backticks in my code unless I have to; it’s always begrudgingly though.

As I said above, this isn’t a new issue to me (or most people for that matter) but I continually run up against it. It’s particularly insidious because the natural instinct is to evaluate the SQL looking for the missing comma or quote, which is a HUGE waste of time.

So which keywords are reserved? Thankfully, MySQL has a list. It’s a good idea to familiarize yourself with the terms on the page and just keep the knowledge in the back of your brain.

It will come up again.