如何在 MySQL 中创建表别名

How do I create a table alias in MySQL

提问by rswolff

I am migrating an MS Access application (which has linked tables to a MSSQL Server) to MySQL.

我正在将 MS Access 应用程序（已将表链接到 MSSQL 服务器）迁移到 MySQL。

As a means to overcome some MSAccess table naming problems, I am seeking a solution to add a MySQL table alias that will point to an existing table in the MySQL database. Ideally I would like to create the alias 'dbo_customers' in mysql that would point to the customers table also in mysql.

作为克服一些 MSAccess 表命名问题的一种方法，我正在寻找一种解决方案来添加一个 MySQL 表别名，该别名将指向 MySQL 数据库中的现有表。理想情况下，我想在 mysql 中创建别名“dbo_customers”，该别名也指向 mysql 中的客户表。

To be clear I am notwanting to alias a table name inside a query like this:

需要明确的是，我不希望别名这样的查询中的表名：

SELECT * FROM customers AS dbo_customers

But rather I would like to be able issue the following query:

但我希望能够发出以下查询：

SELECT * FROM dbo_customers

and have it return data from the customers table.

并让它从客户表中返回数据。

回答by DrewM

Off the top of my head

离开我的头顶

CREATE VIEW dbo_customers AS
SELECT * FROM customers

Maybe not the best solution but should work as the view is updatable. Will definitely work for Read Only

也许不是最好的解决方案，但应该可以工作，因为视图是可更新的。肯定适用于只读

回答by Atli

You can create a View.

您可以创建一个View。

CREATE VIEW dbo_customers AS SELECT * FROM customers;

If that doesn't work for you, you could try creating a shadow-copy of the table, and use Triggers to keep the tables synced.

如果这对您不起作用，您可以尝试创建表的影子副本，并使用触发器保持表同步。

For example:

例如：

CREATE TABLE t1( id serial primary key, field varchar(255) not null );
CREATE TABLE dbo_t1( id serial primary key, field varchar(255) not null );

-- INSERT trigger
CREATE TRIGGER t1_dbo_insert AFTER INSERT ON t1
FOR EACH ROW BEGIN
    INSERT INTO dbo_t1 SET field = NEW.field;
    -- No need to specify the ID, it should stay in-sync
END

-- UPDATE trigger
CREATE TRIGGER t1_dbo_update AFTER UPDATE ON t1
FOR EACH ROW BEGIN
    UPDATE dbo_t1 SET field = NEW.field WHERE id = NEW.id;
END

-- DELETE trigger
CREATE TRIGGER t1_dbo_delete AFTER DELETE ON t1
FOR EACH ROW BEGIN
    DELETE FROM dbo_t1 WHERE id = OLD.id;
END

Not exactly an 'alias', and far from perfect. But it is an option if all else fails.

不完全是“别名”，也远非完美。但如果所有其他方法都失败了，这是一个选择。

回答by mitlas

there is a simpler solution for MySQL via MERGE table engine:

通过 MERGE 表引擎，MySQL 有一个更简单的解决方案：

imagine we have table named rus_vacanciesand need its English equivalent

想象我们有一个命名的表rus_vacancies并且需要它的英文等效项

create table eng_vacancies select * from rus_vacancies;
delete from eng_vacancies;
alter table eng_vacancies ENGINE=MERGE;
alter table eng_vacancies UNION=(rus_vacancies);

now table rus_vacanciesequals to table eng_vacanciesfor any read-write operations

现在 tablerus_vacancies等于 tableeng_vacancies用于任何读写操作

one limitation - original table must have ENGINE=MyISAM (it can be easily done by "alter table rus_vacancies ENGINE=MyISAM")

一个限制 - 原始表必须有 ENGINE=MyISAM （它可以通过“ alter table rus_vacancies ENGINE=MyISAM”轻松完成）

回答by David-W-Fenton

@OMG Ponies ponies said in a comment:

@OMG 小马小马在评论中说：

Why not rename the table?

为什么不重命名表？

...and it seems the obvious answer to me.

......这对我来说似乎是显而易见的答案。

If you create an ODBC linked table for the MySQL table customers it will be called customers and then all you have to do is rename the table to dbo_customers. There is absolutely no need that I can see to create a view in MySQL for this purpose.

如果您为 MySQL 表客户创建一个 ODBC 链接表，它将被称为客户，然后您要做的就是将表重命名为 dbo_customers。我绝对不需要为此目的在 MySQL 中创建视图。

That said, I'd hate to have an Access app that was using SQL Server table names when the MySQL tables were not named the same thing -- that's just confusing and will lead to maintenance problems (i.e., it's simpler for the linked tables in the Access front end to have the same names as the MySQL tables, wherever possible). If I were in your position, I'd get a search and replace utility and replace all the SQL Server table names with the MySQL table names throughout the entire Access front end. You'd likely have to do it one table at a time, but in my opinion, the time it takes to do this now is going to be more than made up for in clarity going forward with development of the Access front end.

也就是说，当 MySQL 表的名称不同时，我不希望有一个使用 SQL Server 表名的 Access 应用程序——这只是令人困惑，并且会导致维护问题（即，在Access 前端尽可能与 MySQL 表具有相同的名称）。如果我处于您的位置，我会得到一个搜索和替换实用程序，并在整个 Access 前端将所有 SQL Server 表名替换为 MySQL 表名。您可能必须一次完成一张表，但在我看来，现在完成此操作所需的时间将远远超过 Access 前端开发的清晰度。

回答by murlan

I always rename my "linked to SQL" tables in Access from

我总是将 Access 中的“链接到 SQL”表重命名为

{dbo_NAME}to {NAME}.

{dbo_NAME}到{NAME}。

The link creates the table name as {dbo_NAME}but access occasionally has problems with the dbo_ prefix.

该链接创建了表名，{dbo_NAME}但访问偶尔会遇到 dbo_ 前缀的问题。

回答by Marcus Adams

Aliases would be nice, yet MySQL does NOThave such a feature.

别名将是不错，但MySQL的不具有这样的功能。

One option that may serve your needs, besides creating a view, is to use the FEDERATED storage enginelocally.

除了创建视图之外，可以满足您需求的一种选择是在本地使用FEDERATED 存储引擎。

CREATE TABLE dbo_customers (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    PRIMARY KEY  (id),
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@localhost:9306/federated/customers';

There are currently some limitations with the FEDERATED storage engine. Here are a couple especially important ones:

FEDERATED 存储引擎目前有一些限制。这里有几个特别重要的：

• FEDERATED tables do not support transactions
• FEDERATED tables do not work with the query cache

• FEDERATED 表不支持事务
• FEDERATED 表不适用于查询缓存

回答by Ben S

You could create a viewnamed dbo_customerswhich is backed by the customerstable.

您可以创建一个由表支持的名为的视图。dbo_customerscustomers