A column called Status in MySQL

Question

I've been using MySQL and I need a column called “Status” in a table.

I know this word “Status” is a keyword in MySQL, and I would like to know if I will have problems with it if I write SQL statements like:

select t.Id, t.Name, t.Status from Table t

Or in triggers:

Set new.Status = 1;

if (new.Status <> old.Status) then
  /* do something */
end if

Or should I rename it for another word?

Answer 1

TL;DR: Avoid keyword identifiers and rename them if you can, quote them meticulously otherwise.

---

You’re correct that STATUS is a keyword, likely owing to SHOW STATUS.

If renaming the column isn’t much of a chore for you, changing the name is the best choice. That way, you avoid running into problems later or save people trouble who use your database structure.

If you want to keep it however (because “status” is such a handy name), it’s perfectly okay. While purists rant, it ensures clean code because you have to use backticks (`) or ANSI quotes (i.e. ") according to the manual on quoting identifiers.

SELECT `t`.`Status` FROM `Table` t -- properly quoted

SELECT t.status FROM `Table` t -- multi-part identifier

SELECT STATUS FROM `Table` t -- will still work, but tease any pedant

Of course this kind of identifier quoting works in procedural logic as well (like in trigger scripts).