MySQL KeyWords WorkAround

[TimMcConechy]

Hi,
I am having a problem with a few "key words" on mysql.
LEFT,DATABASE,F IELDS,LOAD,SEPA RATOR ,SQL,KEY

These are currently column names in our application tables.
On other systems (oracle,sql server ect I can get around the problem
by puting quotes around the identifiers. According to the mysql help
this should be possible too. So I tried:

mysql> CREATE TABLE TEST ('LEFT' INT);
ERROR 1064 (42000): You have an error in your SQL syntax. Check the
manual that
corresponds to your MySQL server version for the right syntax to use
near ''LEF
T' INT)' at line 1


It seems this type of quotes " does not work nor does '.

So the question is how do I get around this problem.

BR
Tim

[Aggro]

Re: MySQL KeyWords WorkAround

TimMcConechy wrote:
[color=blue]
> mysql> CREATE TABLE TEST ('LEFT' INT);[/color]

It is not ' you should use, but a ` (They don't even look the same, do
they? ;)

# Like this:
CREATE TABLE TEST33 (`LEFT` INT);

If you don't know where to print that character, just copy-paste it from
this message.

[Chris Hope]

Re: MySQL KeyWords WorkAround

TimMcConechy wrote:
[color=blue]
> Hi,
> I am having a problem with a few "key words" on mysql.
> LEFT,DATABASE,F IELDS,LOAD,SEPA RATOR ,SQL,KEY
>
> These are currently column names in our application tables.
> On other systems (oracle,sql server ect I can get around the problem
> by puting quotes around the identifiers. According to the mysql help
> this should be possible too. So I tried:
>
> mysql> CREATE TABLE TEST ('LEFT' INT);
> ERROR 1064 (42000): You have an error in your SQL syntax. Check the
> manual that
> corresponds to your MySQL server version for the right syntax to use
> near ''LEF
> T' INT)' at line 1
>
>
> It seems this type of quotes " does not work nor does '.
>
> So the question is how do I get around this problem.[/color]

Use backticks `
CREATE TABLE TEST (`LEFT` INT);

Remember to always use them in your queries as well eg
SELECT `LEFT` FROM TEST

You really shouldn't use reserved words for column names if you can help it
though.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/