1

I need to return a multidimensional array from a query with the id keys named key. (needed for object keys in amazon S3 API)

example:

Array ( [0] => Array ( [key] => 8 ) [1] => Array ( [key] => 7 ) [2] => Array ( [key] => 6 ) )

The problem: key is a reserved name in MySQL. I have to use the name key.

The following query gives an error

SELECT `file_name` AS key FROM (`uploads`)

but adding backticks around key doesn't give errors

SELECT `file_name` AS `key` FROM (`uploads`)

Is it safe to continue like this or is there a better way? I was thinking to rename the id key in the array in PHP but doing it in the query seems faster

Lorenz Lo Sauer
  • 23,698
  • 16
  • 85
  • 87
CyberJunkie
  • 21,596
  • 59
  • 148
  • 215
  • 1
    yes thats what the backticks are for – fire Sep 22 '12 at 17:52
  • 1
    It's safe - it wouldn't work otherwise. But it's a good idea not to use reserved words if you can, because it makes the code that bit harder to follow. – andrewsi Sep 22 '12 at 17:53
  • I recount instances wherein a SQL Function returned results with pure numeric column-names. The situation aggravates with the wrappers of various scripting environment such as PHP. Consider using aliases through `AS aliasname` in those cases. – Lorenz Lo Sauer Sep 22 '12 at 18:09
  • I'll recommend not to use reserved words, but if you **must** use them, then **always** use backticks. – gtgaxiola Sep 22 '12 at 17:52

2 Answers2

2

As stated, use backticks.

From the MYSQL Docs

Reserved words are permitted as identifiers if you quote them as described in Section 9.2, “Schema Object Names”:

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)
Lorenz Lo Sauer
  • 23,698
  • 16
  • 85
  • 87
1

To be away from reserved keyword around table field in query always considered as the best way...If you are using in reserved keyword in query, then backtick allow you to use reserved keyword...

As backtick is not defined in ANSI SQL standard, it'll probably create problem when you migrate from MySQL environment...

Akash KC
  • 16,057
  • 6
  • 39
  • 59