Using backquote/backticks for mysql queries

Question

Hi,

I've been having a dilemma for a while now, so I said I'd see what you guys thing before I rest my case.

Ever since I started playing with MySQL I was building query how I though was "the right way", with backticks.

Example:

SELECT `title` FROM `table` WHERE ( `id` = 3 )

As opposed to:

SELECT title FROM table WHERE ( id = 3 )

I think I got this practice from the Phpmyadmin exports, and from what I understood, even Rails generates its queries like this.

But nowadays I see less and less queries build like this, and also, it seems the code seems messier and more complicated with backticks in queries. Even with SQL helper functions things would be simpler without them; so this is why I'm considering leaving them behind.

But first, I wanted to find out if there is other implication in this practice that should affect my decision, such as SQL (MySQL in my case) interpretation speed, etc.

So what do you think?

Answer 1

Well, if you ensure that you never accidentally use a keyword as an identifier, you don't need the backticks. :-)

Answer 2

My belief was that the backticks were primarily used to prevent erroneous queries which utilized common SQL identifiers, i.e. LIMIT and COUNT.

Answer 3

Backticks also allow spaces and other special characters (except for backticks, obviously) in table/column names. They're not strictly necessary but a good idea for safety.

If you follow sensible rules for naming tables and columns backticks should be unnecessary.

Answer 4

backticks are used to escape reserved keywords in your mysql query, e.g. you want to have a count column—not that uncommon.

you can use other special characters or spaces in your column/table/db names

they do not keep you safe from injection attacks (if you allow users to enter column names in some way—bad practice anyway)

they are not standardized sql and will only work in mysql; other dbms will use " instead