MySQL column names

Question

Im trying to write a query in MySQL however one fo my column names is 'comment' however when entered into a WHERE clause it shows up bold and doesn't get used as a column name does anyone know how to change that?

this is the query

SELECT DISTINCT propertyNo from Viewing 
WHERE comment  IS NULL 

UNION 

SELECT propertyNo FROM PropertyForRent
WHERE rent < 600
ORDER BY propertyNO ASC;

You have to post the code you want to change before someone can tell you how to change it. — Scott Hunter, Nov 14 '19 at 19:37
COMMENT is a keyword but not a reserved word: https://dev.mysql.com/doc/refman/5.5/en/keywords.html#keywords-5-5-detailed-C In any case enclose it in backticks. — forpas, Nov 14 '19 at 19:44
`COMMENT` is a [key word](https://dev.mysql.com/doc/refman/8.0/en/keywords.html) and must be quoted to be used as an identifier. — PM 77-1, Nov 14 '19 at 19:47
As other comments and answers have already stated, you can delimit field names with backticks. I'd go a step further, and some would disagree, and say it's a good practice to always delimit your identifiers that way. It makes for a more consistent style when using them is necessary, and future proofs your queries better (it isn't often, but new keywords are added occasionally). — Uueerdo, Nov 14 '19 at 21:27

score 1 · Answer 1 · answered Nov 14 '19 at 19:43

1

You need to quote it:

WHERE `comment`  IS NULL

This is covered in the Schema Object Names sections of the MySQL 5.7 Reference Manual.

answered Nov 14 '19 at 19:43

Sean Bright

118,630
17
138
146

Tural Ali · Answer 2 · 2019-11-14T22:22:37.313

1

Always use backticks and quotation marks when you write your SQL.

With ` you write variable names

With ' you write variable values

For example

SELECT * FROM `test` WHERE `x` = 'blahblah'

edited Nov 14 '19 at 22:22

answered Nov 14 '19 at 19:43

Tural Ali

22,202
18
80
129

1

I'd agree with the backticks part; but quoting numeric values is unnecessary (unless the field containing them isn't numeric) and can lead to weird behavior. – Uueerdo Nov 14 '19 at 21:23

stephangroen · Answer 3 · 2019-11-15T07:42:20.013

COMMENT is a keyword in MySQL. See: https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-C

That's why your editor shows it as bold. You can escape this by using back ticks:

SELECT DISTINCT `propertyNo` from `Viewing` 
WHERE `comment`  IS NULL

Try to always use back ticks when referring to columns or table names. More info on that subject: https://dba.stackexchange.com/questions/23129/benefits-of-using-backtick-in-mysql-queries/23130

MySQL column names

3 Answers3