SQL 什么时候应该使用数据库同义词？

When should database synonyms be used?

提问by aw crud

I've got the syntax down but I'm wondering if somebody can provide an illustrative use case where database synonyms are very useful.

我已经掌握了语法，但我想知道是否有人可以提供一个说明性用例，其中数据库同义词非常有用。

采纳答案by Jordan Parmer

It is excellent for staging mock tables when testing. For example, if your source tables contain millions of records and you want to test a small subset of data, you can use synonyms to re-direct the source table to a smaller table you control so you can stage various scenarios.

它非常适合在测试时暂存模拟表。例如，如果您的源表包含数百万条记录并且您想要测试一小部分数据，您可以使用同义词将源表重定向到您控制的较小表，以便您可以暂存各种场景。

In this way, you can update/delete the data in the mock table without affecting your source table. When you are ready to use the source table, all you need to do is re-direct the synonym.

这样，您可以在不影响源表的情况下更新/删除模拟表中的数据。当您准备好使用源表时，您需要做的就是重新定向同义词。

回答by a'r

Check out the Oracle documentation on synonyms.

查看有关同义词的Oracle 文档。

In addition to the other answers here, they are also commonly used for:

除了此处的其他答案外，它们还常用于：

• Providing easy to use names for remote tables, i.e. over database links
• Tables that you need to be accessible to all users, i.e. public synonyms

• 为远程表提供易于使用的名称，即通过数据库链接
• 您需要所有用户都可以访问的表，即公共同义词

回答by FrustratedWithFormsDesigner

I usually see synonyms used when the DBA wishes to separate database objects into different schemas, but wants/needs some of these objects to be visible to other schemas (but doesn't want to give direct access to them).

当 DBA 希望将数据库对象分成不同的模式，但希望/需要其中一些对象对其他模式可见（但不想直接访问它们）时，我通常会看到同义词。

An example I've seen most recently: Several web apps run by the same company. Users usually have access to more than one of these apps, and the will only have one user account to access these apps. User-account information is stored in a USER_ACCOUNTSschema, and all other apps are in their own schemas and access the USER_ACCOUNTSschema via synonyms.

我最近看到的一个例子：同一家公司运行的几个网络应用程序。用户通常可以访问多个这些应用程序，并且只有一个用户帐户可以访问这些应用程序。用户帐户信息存储在USER_ACCOUNTS架构中，所有其他应用程序都在自己的架构中，并USER_ACCOUNTS通过同义词访问架构。

回答by Adam Matan

When you have database object names hard-coded within existing code.

当您在现有代码中硬编码数据库对象名称时。

Using synonyms might spare you the agony of rewriting old code, sometimes from multiple sources, which has its own ideas of the table or database names.

使用同义词可能会让您免于重写旧代码的痛苦，有时来自多个来源，这些代码对表或数据库名称有自己的想法。

I have seen, for example, a code which was written for some production server. The coder has conveniently hard-coded the main table's name is test_data, which worked fine on his workstation. Using synonyms rather than rewriting his code got me home early.

例如，我见过为某个生产服务器编写的代码。编码员方便地对主表的名称进行了硬编码test_data，这在他的工作站上运行良好。使用同义词而不是重写他的代码让我早点回家。

回答by Quassnoi

Say, when you need to make a poorly written application (that does not issue ALTER SESSION SET CURRENT_SCHEMA) to work against another schema.

比如说，当您需要编写一个编写不佳的应用程序（不会发出ALTER SESSION SET CURRENT_SCHEMA）以针对另一个模式工作时。

The synonyms are mainly used as a workaround for cases like that. With a properly written application, you will hardly ever have to use them.

同义词主要用作此类情况的解决方法。使用正确编写的应用程序，您几乎不必使用它们。

回答by shindigo

In general, it is bad practice to embed schema names in SQL or PL*SQL. So if you are writing some code that must refer to a table in another schema like: "select id from OtherSchema.OtherTable" you are best off defining a synonym for the table (create synonym OtherTable for OtherSchema.OtherTable) and writing "select id from OtherTable".

通常，在 SQL 或 PL*SQL 中嵌入模式名称是不好的做法。因此，如果您正在编写一些必须引用另一个模式中的表的代码，例如：“从 OtherSchema.OtherTable 中选择 id”，您最好为该表定义一个同义词（为 OtherSchema.OtherTable 创建同义词 OtherTable）并编写“select id来自其他表”。

This way, if OtherTable moves to a different schema name, or you have another another installation of the system that uses a different schema name you can just redefine the synonyms instead of changing the code.

这样，如果 OtherTable 移动到不同的模式名称，或者您有另一个使用不同模式名称的系统安装，您只需重新定义同义词而不是更改代码。

It can also be used to switch your code between two schemas with the same structure by redefining the synonyms.

通过重新定义同义词，它还可以用于在具有相同结构的两个模式之间切换代码。