Benefits of using backtick (`) in MySQL queries?

Question

In MySQL we can create queries with or without the backtick (`) symbol. Example:

1. SELECT * FROM TEST;
2. SELECT * FROM `TEST`;

Both works fine in mysql-console.

Is there any technical difference between them?

Is there any benefit using (`) over over simple queries?

Answer 1

They are called quoted identifiers and they tell the parser to handle the text between them as a literal string. They are useful for when you have a column or table that contains a keyword or space. For instance the following would not work:

create table my table (id int);

But the following would:

create table `my table` (id int);

Also, the following would get an error if status was a keyword:

select status from some_table

But the following would be parsed correctly:

select `status` from some_table

I hope this helps you.

Answer 2

If you want to use something around object identifiers, use at least the standard double quotes: "

This works in MySQL, PostgreSQL, SQL Server, Oracle, etc. etc. For MySQL you might need the SQL mode ansi_quotes, depending on the default configuration:

SET sql_mode = 'ANSI_QUOTES';

Backticks ` are only used in MySQL, you learn a type of SQL that won't work in any other brand of DBMS.

Answer 3

It means you can have spaces in table names. Not particular appealing, of course. Same with SQL Server's [].

Answer 4

It can be helpful if you have a column with name that is reserved,

eg: You can query a statement like this:

select * from tablename group by `group`;