A column called Status in MySQL

问题:

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?

回答1:

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.

回答2:

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

回答3:

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

回答4:

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

回答5:

rename your column, end of discussion