Syntax error due to using a reserved word as a table or column name

If we try to execute a simple MySQL query or any database query that contains reserved word as a table or column name, we come across syntax errors.

The problem is that we are using a reserved word as a table or column name which is ‘GROUP‘. In MySQL, certain words like KEY, INSERT, READ etc. are reserved words. Since they have a special meaning, MySQL treats it as a syntax error whenever you use them as a table name, column name, or other kind of identifier.

A complete list of reserved words can be found in section 9.3 Reserved Words.

Defenses from syntax error using a reserved word as a table or column name:

We have two options.

1. Don’t use reserved words as identifiers

The simplest solution is simply to avoid using reserved words as identifiers. You can probably find another reasonable name for your column that is not a reserved word.

For example instead of using ‘GROUP‘ as column name in the table, we can use ‘role’ as column name.

Doing this has a couple of advantages:

(i) It eliminates the possibility that you or another developer using your database will accidentally write a syntax error due to forgetting – or not knowing – that a particular identifier is a reserved word.

There are many reserved words in MySQL and most developers are unlikely to know all of them.

By not using these words in the first place, you avoid leaving traps for yourself or future developers.

(ii) The means of quoting identifiers differs between SQL dialects. While MySQL uses backticks for quoting identifiers by default, ANSI-compliant SQL (and indeed MySQL in ANSI SQL mode, as noted here) uses double quotes for quoting identifiers. As such, queries that quote identifiers with backticks are less easily portable to other SQL dialects.

2. Use backticks

If renaming the table or column isn’t possible, wrap the offending identifier (the reserved word as a table or column name) in backticks (`)

Thus the query from the question can be fixed by wrapping the keyword ‘GROUP‘ in backticks, as shown below:

Purely avoiding the reserved words as identifiers is usually a wiser course of action than backtick-quoting for the sake of reducing the risk of future mistakes.