表名周围的引号究竟有什么作用?

2021-12-05 00:00:00 sql oracle

我认为引号 (") 只是一种分组标记，但我正在调试一些 NHibernate 代码并注意到虽然

I thought that the quotation mark (") was simply a type of grouping marker but I'm debugging some NHibernate code and notice that while

SELECT * FROM site WHERE site_id = 3;

工作正常

SELECT * FROM "site" WHERE site_id = 3;

因表或视图不存在错误而失败.

fails with a table or view does not exist error.

是什么?

推荐答案

在 Oracle 中为标识符加上双引号会导致 Oracle 将标识符视为区分大小写，而不是使用默认的不区分大小写.如果创建的表(或列)在名称周围使用双引号，则必须始终使用双引号引用标识符并正确指定大小写(所有大写标识符除外，其中双引号没有意义)).

Putting double-quotes around an identifier in Oracle causes Oracle to treat the identifier as case sensitive rather than using the default of case-insensitivity. If you create a table (or a column) with double-quotes around the name, you must always refer to the identifier with double quotes and by correctly specifying the case (with the exception of all upper case identifiers, where double-quotes are meaningless).

在幕后，Oracle 总是进行区分大小写的标识符匹配.但它总是在进行匹配之前将没有双引号的标识符转换为大写.如果在标识符周围加上双引号，Oracle 会跳过转换为大写.

Under the covers, Oracle is always doing case-sensitive identifier matching. But it always casts identifiers that are not double-quoted to upper case before doing the matching. If you put double-quotes around an identifier, Oracle skips the casting to upper case.

所以如果你做类似的事情

So if you do something like

CREATE TABLE my_table( 
  col1 number,
  col2 number
)

你可以

SELECT * FROM my_table
SELECT * FROM MY_TABLE
SELECT * FROM My_Table
SELECT * FROM "MY_TABLE"

不过类似

SELECT * FROM "my_table"

会失败.

另一方面，如果你做类似的事情

On the other hand, if you do something like

CREATE TABLE "my_other_table"( 
  col1 number,
  col2 number
)

你做不到

SELECT * FROM my_other_table
SELECT * FROM MY_OTHER_TABLE
SELECT * FROM My_Other_Table
SELECT * FROM "MY_OTHER_TABLE"

但是这个

SELECT * FROM "my_other_table"

会起作用

相关文章