ERROR 1064 (42000) - You have an error in your SQL syntax
When working with MySQL, you might run into this error:
SQLSTATE[42000]: Syntax error or access violation: 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…
This error message normally means that you use a keyword or a reserved word of MySQL for one of your identifiers in your statement.
The identifiers might be database, table, index, column, alias, view, stored procedure, partition, etc.
The reserved words could be ADD, AND, BEFORE, BY, CALL, CASE, CONDITION, DELETE, DESC, DESCRIBE, FROM, GROUP, IN, etc. You can find the full list of reserved words here.
So when you use a reserved word for an identifier, such as table name, but without quoting, MySQL treats it as a keyword, then it comes to the error above. So there are two ways you can avoid that error:
- Don’t use reserved words as identifiers
- Use backticks to quote it whenever you refer to it.
The best practice is to not use the reserved words for your identifiers since naming an object with a non-reserved word in the first place is easier than finding where you forgot to put a backtick that caused the error. On the other hand, only MySQL uses backticks to quote while other ANSI-compliant SQL database systems use double quotes, and that makes it harder to port between databases.
Furthermore, every developer should be aware of the reserved words too. If you accidentally used reserved words for the identifiers, remember to wrap them with backticks then.
Example
We have this table interval:
CREATE TABLE interval (begin INT, end INT);
Here it comes the error:
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'
Now fix it:
CREATE TABLE `interval` (begin INT, end INT);
And you’re good to go.
In some special cases, a word that follows a period in a qualified name must be an identifier, so it need not be quoted even if it is reserved:
CREATE TABLE mydb.interval (begin INT, end INT);
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
