Should I use backticks or not when escaping keywords in MySQL?

Question

Should all table names in MySQL be enclosed in backticks (`) to prevent collisions with reserved keywords? The reason I ask is because their use makes the SQL less portable as not all databases allow backticks.

So would avoiding table and column names containing keywords be a better course of action? If so what can be done to mitigate the risk of MySQL adding a new keyword in the next version that might collide with your schema.

Is there a best practice regarding this?

Answer 1

The most portable way (between the systems) is to use double quotes, however, it would require enabling ANSI_QUOTES which is off by default on most installations.

So while keeping arguably useful compatibility between different engines (and incompatibility does not limit itself to backticks only but to zillion other things different between MySQL and other systems) you are killing the compatibility between different setups of MySQL which is by far more important.

Avoiding the reserved keywords is always the best solution.

Answer 2

This is a matter of opinion. But portable code outweighs their use. As you noted, backticks can allow you to use reserved words, which is never a good thing. That, for me, already proves they do more harm than good.

Answer 3

So would avoiding table and column names containing keywords be a better course of action?

In short, yes. And there isn't much you can do with respect to future keywords except avoiding obvious candidates, e.g. with, window, over, etc.

Answer 4

One common practice is to prefix all your table names with a few letters and an underscore. It prevents collisions if you need to house two different applications in the same database, and you'll likely never run into reserved words.