简单的SELECT语句失败，并显示“接近附近使用语法"，"ORA-00906"，“接近或附近存在语法错误".或“关键字附近的语法" [英] 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.dll中发生了类型为'System.Data.SqlClient.SqlException'的未处理的异常
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.
问题和建议不仅限于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保留关键字的列表.
PostgreSQL保留关键字的列表.
值得注意的陷阱"包括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语句失败，并显示“接近附近使用语法"，"ORA-00906"，“接近或附近存在语法错误".或“关键字附近的语法"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
