A column called Status in MySQL

I've been using MySQL and I ne开发者_运维知识库ed 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?

---

Status can be its own column if you wrap it, in MySQL, with ``.

SELECT `t`.`Status`. FROM `t`

But for the sake of avoiding confusion later on, it may be better for you to distinguish it in some other way.

---

I would never recommend to use keywords as column names, but it should not be a problem if you enclose it in [] like [Status].

---

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).

---

You should use `` around then in mysql and in sql you should use []

---

rename your column, end of discussion