简单的 SELECT 语句失败，出现“syntax to use near"、“ORA-00906"、“syntax error at or near"或“关键字附近的语法" [英] Simple SELECT statement fails with "syntax to use near", "ORA-00906", "syntax error at or near" or "syntax near the keyword"
问题描述
我有一个非常简单的 SQL 语句
I have a very simple SQL statement
SELECT * FROM Table;
但是，我的查询引擎返回一个语法错误.为什么?
but, my query engine returns a syntax error. Why?
错误详情:
System.Data.SqlClient.SqlException"类型的未处理异常发生在 >System.Data.dll
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in >System.Data.dll
附加信息:关键字表格"附近的语法不正确.
Additional information: Incorrect syntax near the keyword 'Table'.
这怎么可能?我检查了连接字符串，它是正确的.我检查了我的表名，它也是正确的.
How is this possible? I checked the connection string and it is correct. I checked my table name and it is also correct.
我做错了什么?
推荐答案
好的，Table 是所有 SQL 变体中的保留关键字.
Okay, Table is a reserved keyword in all variants of SQL.
如果你想调用一个表Table，并在语句中使用它，你必须告诉你的sql引擎它是一个标识符.为此，您需要使用标识符限定符.
If you want to call a table Table, and use it in a statement, you have to tell your sql engine that it is an identifier. To do this you need to use Identifier Qualifiers.
对于(MS SQL Server)TSQL使用方括号
SELECT * FROM [Table];
对于 MySQL 使用 `
SELECT * FROM `Table`;
对于 Oracle 和 PostgreSQL 使用引号，这些都符合标准.
for Oracle and PostgreSQL use quotation marks, these are standards compliant.
SELECT * FROM "Table";
对于 SQLite，您可以使用上述任何一种，但最好使用引号.
for SQLite you can use any of the above, but quotation marks are prefered.
标识符限定符告诉引擎这是一个标识符(对象的名称).不是关键字的名称，即使它们碰巧相同.如果没有您的指导，查询引擎可能会感到困惑并报告错误，或者更糟糕的是，执行一些意想不到的事情.
The Identifier Qualifiers tell the engine that this is an identifier (the name of an object.) Not the name of a keyword, even if they happen to be the same. Without your guidance the query engine can get confused and report an error, or worse, do something unexpected.
使用标识符限定符是一种很好的做法，即使标识符不是关键字.他们更好地为所有解析器定义语句，包括肉体.
Using Identifier Qualifiers is good practice, even if the identifers are not keywords. They better define statements for all parsers, including the fleshy kind.
在关键字之后命名对象通常被认为是不好的做法.所以你应该尽量避免使标识符与关键字相同.保留关键字描述表格内容的情况很少见，请参阅脚注.
Naming objects after keywords is generally considered bad practice. So you should try to avoid making identifers the same as keywords. The occasions when a reserved keyword is descriptive of the contents of a table are rare, see the footnote.
例如您的表格不是表格的 Table.
e.g. your table is not a Table of tables.
问题和建议不仅限于Tables，所有数据库对象都需要标识符，包括Schema、Views 和许多类型存在、标准和特定于供应商.
The problem and advice is not limited to Tables, Identifiers are required for all database objects inluding Schema, Views and the many types that exist, standard and vendor-specific.
另一种好的做法是在 Table 标识符前加上 Schema 标识符，这对查询引擎有一点帮助.当包含 Schema 标识符时，标识符应该是限定的，
Another form of good practice is to prefix Table indentifiers with a Schema identifier, this helps the query engine a little.
When including the Schema identifier, the identifer should be qualified,
对于(MS SQL Server)TSQL使用方括号
SELECT * FROM [dbo].[Table];
对于 MySQL 使用 `
SELECT * FROM `dbo`.`Table`;
对于 Oracle、PostgreSQL 和 SQLite 使用引号
SELECT * FROM "dbo"."Table";
即使您的 Schema 不是以关键字命名的，也应该如此.
even if your Schema is not named after a keyword, as should be the case.
供您参考，帮助您避免冲突.
For your reference, to help you avoid conflicts.
Oracle 保留关键字的列表.
值得注意的陷阱"包括 USER 和 ERROR，这似乎是在设计系统时出现的.
Notable "gotcha's" include USER and ERROR, which seem to come up when designing systems.
脚注:
在某些情况下，对对象名称使用保留词可能在语义上是正确的.
There are occasions when using reseved words for object names may be semantically correct.
考虑一个为家具店设计的信息系统示例.在这种情况下，一张桌子(厨房、花园、餐厅、药剂师等)可能是正确的.所以，你可以说 Table 是正确的标识符.
Consider the contrived example of an information system for a furniture shop. In this scenario, a table of tables (kitchen, garden, dining, apothecary etc.) may be correct. So, you could argue Table was the correct identifier.
如果你总是使用标识符限定符，你就不会被烧毁.
If you always use Identifier Qualifiers, you won't get burned.
这篇关于简单的 SELECT 语句失败，出现“syntax to use near"、“ORA-00906"、“syntax error at or near"或“关键字附近的语法"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
