转义列名称的 SQL 标准？

是否有转义列名的 SQL 标准？如果不是什么工程的 MySQL 和 SQLite？它也适用于 SQLServer 吗？

84765 次浏览
小开

For MySQL, use back ticks `.

For instance:

SELECT `column`, `column2` FROM `table`
小开

For MS SQL use [ and ]

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

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 DBASE/DBF use [ and ]

SELECT [DATE], [TIME], [ANY_OTHER_TO_BE_ESCAPED_COLUMN] 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!


提交答案