0

When using backticks with MySQL so column names don't collide with reserved words:

SELECT `count`, `type`
FROM    table1

Do you still need to use backticks to prevent it colliding with reserved words when you're doing this:

SELECT  table1.count, table1.type
FROM    table1

Should you still add the backticks like this:

SELECT  table1.`count`, table1.`type`
FROM    table1

Any help appreciated.

1

Your syntax (without backticks) will work on

See SQL at the bottom of the post.

(tl;dr) The answer to the question "MySQL: Do you need to use backticks on column names when using the dot to refer to a table name" is "No, you do not need to use backticks!".

See also

However, I beg of you, if you value your own sanity, do NOT EVER use SQL keywords. COUNT and TYPE are such words. It makes debugging a nightmare and is completely unnecessary anyway. Use identifiers such as my_count or thing_type instead. Just because you can drive your car over a cliff, doesn't mean that you should!

Most (all?) modern RDBMSs have (at least) 64 character(*) identifiers. That_is_more_than_enough_dont_you_think?

Furthermore (and very importantly) if you use the superfluous (and highly annoying) backtick, you'll be bunched when cutting and pasting your SQL to different servers - my advice is to avoid at all costs. It's no longer required and is tricky to type!

Get some SQL naming conventions off the web (example), pick one and stick to it! This is somewhat of a religious topic (an "ecumenical matter" in the immortal words of Fr. Jack!). Read around and decide for yourself!

My own preference is for table names to be singular (a table is a collection anyway), SQL in UPPER CASE and identifiers_in_lower_case_with_underscores - makes reading easy!

You (and those who come after you) will be eternally grateful!

(*) from here, Oracle was 30 bytes up and including 12.1, but it's now 128 bytes - almost a tweet! :-)

Now, consider the following DDL, DML, query and response:

(simple, legible and portable!)

=========== DDL and DML ==================

CREATE TABLE table1
(
  my_count INTEGER,
  my_type  VARCHAR (10),
  count    INTEGER,
  type     VARCHAR (10)
);

INSERT INTO table1 VALUES 
(1, 'type1', 34, 'type1_bis'),
(2, 'type2', 23, 'type2_bis'),
(3, 'type3', 56, 'type3_bis'),
(4, 'type4', 78, 'type4_bis'),
(5, 'type5', 90, 'type5_bis');

And a simple query:

SELECT 
  t.my_count,  -- see second MySQL sample!
  t.my_type,
  t.count,
  t.type
FROM
  table1 t;

Results:

my_count    my_type     count       type
1             type1        34  type1_bis
2             type2        23  type2_bis
3             type3        56  type3_bis
4             type4        78  type4_bis
5             type5        90  type5_bis
1
  • 1
    And... Don't clutter column names with the table name: my_table_my_column. After all, if you need to qualify the column, there is the "dot" syntax that this Question started with!
    – Rick James
    Nov 5 '17 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.