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

[Paul DuBois]

Carsten Haese wrote:
> 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.
> 

Note that although MySQL does support using double quotes for quoted
identifierss (if the ANSI_QUOTES SQL mode is enabled), a difference
between MySQL and standard SQL in this case is that quoted delimiters
in standard SQL are case sensitive, whereas in MySQL they are not.