[DB-SIG] How to escape special field name, mysql?

[Carsten Haese]

On Tue, 2007-08-07 at 21:37 -0400, Carsten Haese wrote:
> On Wed, 2007-08-08 at 00:40 +0200, Paul Boddie wrote:
> > On Tuesday 07 August 2007 19:17, Carsten Haese wrote:
> > > On Tue, 2007-08-07 at 11:58 -0500, Lukasz Szybalski wrote:
> > > >
> > > > When i do:
> > > > insert into tablename(id,desc)VALUES(1,'some text')
> > > >
> > > > How do I escape 'desc'?
> > >
> > > insert into tablename(id,`desc`) ...
> > 
> > Obviously MySQL supports the above, but I believe that the standard way is to 
> > use double quotes:
> > 
> > insert into tablename (id, "desc") values (1, 'some text')
> 
> That is the PostgreSQL way. The standard way (at least as far as
> Informix understands it) is not to quote table/column names at all and
> let the parser worry about determining whether the word it's looking at
> is the name of a thing or a keyword.

I looked up the standard and must admit that Informix's behavior in this
regard is non-standard, at least according to SQL92. SQL92 states quite
unequivocally that "The identifier body of a regular identifier [...]
shall not be equal [...] to any reserved word." It furthermore states
that delimited identifiers are delimited by double quotes.

Maybe Informix is keeping an artifact from pre-SQL times for backwards
compatibility. Anyway, I just wanted to set the record straight. Paul
was correct in stating that the standard way of quoting identifiers is
to use double quotes.

-- 
Carsten Haese
http://informixdb.sourceforge.net