5

Tip of the day: Use backticks, like a boss! Even in your handwriting!

Update: If you take the above advice, you don't need to read the following anymore! Seriously!

Question: I'm a little bit annoyed by this. I can create a table containing columns which are named key and value, but when I want to work with these columns I will see a very nice syntax error explaining that these are reserved keywords for MySQL.

My question is: does anybody know why it is like this? Why I'm not receiving the syntax error in the first place? Is it backed by any reason?

Mahdi
  • 9,247
  • 9
  • 53
  • 74
  • 6
    Those are reserved words. If you surround them by backticks then you can use them. On the other hand, it's a bad idea to use MySQL's reserved words for any kind of tables or columns. – N.B. Mar 01 '13 at 14:21
  • 1
    @N.B. only `KEY`, value is not a keyword. – John Woo Mar 01 '13 at 14:21
  • @N.B. I just realised that when the question was posted! – Mahdi Mar 01 '13 at 14:24
  • YOu do know that key value tables are often the worst solution for storing data, right? I would only use if if there is no possible way to know the fields in adviacnce such as when storing lab results (every new lab test would have differnt column needs). For 99% of what people use key values for they are unnecessary and cause performacne problems and make writing queries far more difficult. – HLGEM Mar 01 '13 at 14:59
  • @HLGEM I'm using `key-value` pairs for storing configuration data (in this case) but as you said it's not a solution for every problem. – Mahdi Mar 01 '13 at 18:15
  • possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) – nietonfir May 06 '14 at 20:49

1 Answers1

9

Only KEY is a reserved keyword :D.

just wrap the column name KEY with backtick so you can use it, eg

SELECT `key`
FROM   tableName

or supply the table with alias,

SELECT a.key
FROM   tableName a
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    hehe if you don't like to use backtick, supply an alias for the table `:D` or otherwise, do not use keywords. – John Woo Mar 01 '13 at 14:26
  • 2
    yes! if you spend 3 hours debugging the simplest update query in the world without any success, you'll be happy to use backticks everywhere you can, after that! – Mahdi Mar 01 '13 at 14:31
  • literally retarded. i couldn't figure out for the longest time. then i realize you can't use key, so i switch to index instead. it's also not allowing index. is index a reserved word too?!?! – oldboy Aug 04 '17 at 06:23