When do you need to use back ticks with MySQL?

Back ticks are often misunderstood.

I am trying to learn the best way to write queries.

You might not be sure how to use those funny little `ticks

`back ticks`, `backticks`

I have used single quotes, double quotes and back ticks without any real thought.

What is the standard for this?

Back ticks should be used for table and column identifiers, but are only needed when the identifier is MySQL reserved keyword.
Also if the identifier contains white space or other special characters.

When possible it’s recommended not to use keywords and special characters as table and column identifiers to avoid having to use back ticks.

If you use PHPMyAdmin to generate some queries you will see that all the column and table identifiers are escaped with back ticks by default.

For example a SELECT query would look like this:

SELECT `id`, `created_by_user_id`, `modified_by_user_id` FROM `products` WHERE 1

As none of these column and table identifiers contain any special characters and don’t match any of the MySQL reserved keywords, the same query will run just fine without the back ticks:

SELECT id, created_by_user_id, modified_by_user_id FROM products WHERE 1

Replace the back ticks with single quotes (or double quotes) and you will get an error as now each identifier wrapped in single quotes is treated as a string:

--DON'T DO THIS, AS IT WILL RAISE THE ERROR BELOW:
SELECT 'id', 'created_by_user_id', 'modified_by_user_id' FROM 'products' WHERE 1
--#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 ''products'

If you liked this tip, be sure to subscribe below and be the first to get my next tip…

1 thought on “When do you need to use back ticks with MySQL?”

Leave a Reply