Is there a SQL standard to escape a column name? If not what works for MySQL and SQLite? does it also work for SQL 开发者_开发知识库Server?

Quotation Mark "

The SQL:1999 standard specifies that double quote (") (QUOTATION MARK) is used to delimit identifiers.

<delimited identifier> ::= <double quote> <delimited identifier body> <double quote>

Oracle, PostgreSQL, MySQL, MSSQL and SQlite all support " as the identifier delimiter.

They don't all use " as the 'default'. For example, you have to be running MySQL in ANSI mode and SQL Server only supports it when QUOTED_IDENTIFIER is ON.

According to SQLite,

• 'foo' is an SQL string
• "foo" is an SQL identifier (column/table/etc)
• [foo] is an identifier in MS SQL
• `foo` is an identifier in MySQL

For qualified names, the syntax is: "t"."foo" or [t].[foo], etc.

MySQL supports the standard "foo" when the ANSI_QUOTES option is enabled.

For MySQL, use back ticks `.

For instance:

SELECT `column`, `column2` FROM `table`

For MS SQL use [ and ]

SELECT [COLUMN], [COLUMN 2] FROM [TABLE]

Putting some answers together:

MS SQL (a.k.a. T-SQL), Microsoft Access SQL, DBASE/DBF: SELECT [COLUMN], [COLUMN2] FROM [TABLE]

MySQL: SELECT `COLUMN`, `COLUMN2` FROM `TABLE`

SQLite, Oracle, Postgresql: SELECT "COLUMN", "COLUMN2" FROM "TABLE"

Please add/edit!

For DBASE/DBF use [ and ]

SELECT [DATE], [TIME], [ANY_OTHER_TO_BE_ESCAPED_COLUMN] FROM [TABLE]