blocks|key|3287828|text|从我的脑海中|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3287829|CREATE+VIEW+dbo_customers+AS
SELECT+*+FROM+customers|code-block|syntax|javascript|3287830|也许不是最好的解决方案，但应该可以工作，因为视图是可更新的。肯定适用于只读模式。|3287831|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Off the top of my head

<pre><code>CREATE VIEW dbo_customers AS
SELECT * FROM customers
</code></pre>

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

blocks|key|3287836|text|您可以创建View。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3287837|CREATE+VIEW+dbo_customers+AS+SELECT+*+FROM+customers;|code-block|syntax|javascript|3287838|如果这对您不起作用，您可以尝试创建表的卷影副本，并使用触发器来保持表同步。|3287839|例如：|3287840|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|3287841|这不是一个确切的“化名”，也远远谈不上完美。但如果所有其他方法都失败了，这也是一个选择。|3287842|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/view-syntax.html^0|5|4|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|C|$]]|$1|M|3|N|5|F|7|15|8|@]|9|@]|C|$G|H]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

You can create a <a href="http://dev.mysql.com/doc/refman/5.1/en/view-syntax.html" rel="noreferrer">View</a>.

<pre><code>CREATE VIEW dbo_customers AS SELECT * FROM customers;
</code></pre>

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:

<pre><code>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
</code></pre>

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

blocks|key|3074464|text|您可以创建一个名为dbo_customers的view，它由customers表支持。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3074465|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/create-view.html^0|9|D|U|9|N|4|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@$9|S|A|T|1|U]]|E|$]]|$1|F|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

You could create a <a href="http://dev.mysql.com/doc/refman/5.0/en/create-view.html" rel="nofollow noreferrer">view</a> named <code>dbo_customers</code> which is backed by the <code>customers</code> table.

blocks|key|323596|text|通过合并表引擎，MySQL有一个更简单的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|323597|假设我们有一个名为rus_vacancies的表，并且需要它的英语等效项|offset|length|style|CODE|323598|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);|code-block|syntax|javascript|323599|现在，对于任何读写操作，表rus_vacancies等于表eng_vacancies|323600|一个限制-原始表必须有ENGINE=MyISAM+(可以通过“alter+table+rus_vacancies+ENGINE=MyISAM”轻松完成)|323601|entityMap^0|0|9|D|0|0|D|D|T|D|0|V|13|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|W|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|X|8|@$D|Y|E|Z|F|G]|$D|10|E|11|F|G]]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@$D|13|E|14|F|G]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|R|$]]

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

imagine we have table named <code>rus_vacancies</code> and need its English equivalent

<pre><code>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);
</code></pre>

now table <code>rus_vacancies</code> equals to table <code>eng_vacancies</code> for any read-write operations

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

blocks|key|325772|text|@OMG小马在评论中说：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|325773|325774|为什么不重命名该表？|blockquote|325775|325776|325777|...and，对我来说，这似乎是显而易见的答案。|325778|如果您为MySQL表customers创建了一个ODBC链接表，那么它将被命名为customers，然后您所要做的就是将该表重命名为dbo_customers。在我看来，完全没有必要在MySQL中为此目的创建视图。|325779|也就是说，我不希望Access应用程序在MySQL表名不相同的情况下使用SQL+Server表名--这只会让人感到困惑，并且会导致维护问题(即，尽可能让Access前端中的链接表与MySQL表具有相同的名称会更简单)。如果我处在您的位置，我会得到一个搜索和替换实用程序，并将整个Access前端中的所有SQL+Server表名替换为MySQL表名。您可能必须一次做一个表，但在我看来，随着Access前端的发展，现在做这件事所需的时间将远远超过所需的时间。|325780|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|R|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]|$1|G|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|V|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|W|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|O|$]]

@OMG Ponies ponies said in a comment:

<blockquote>
 Why not rename the table?
</blockquote>

...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.

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.

blocks|key|322988|text|我总是将Access中的“链接到SQL”表重命名为|type|unstyled|depth|inlineStyleRanges|entityRanges|data|322989|{dbo_NAME}到{NAME}。|offset|length|style|CODE|322990|链接会将表名创建为{dbo_NAME}，但access偶尔会出现dbo_前缀的问题。|322991|entityMap^0|0|0|A|B|6|0|9|A|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]|$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|K|$]]

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

<code>{dbo_NAME}</code> to <code>{NAME}</code>.

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

blocks|key|3074497|text|别名很好，但是MySQL没有也有这样的功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3074498|除了创建视图之外，一个可能满足您需求的选择是在本地使用FEDERATED+storage+engine。|offset|length|3074499|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';|code-block|syntax|javascript|3074500|目前有一些limitations+with+the+FEDERATED+storage+engine。这里有几个特别重要的问题：|3074501|3074502|联邦表不支持transactions|unordered-list-item|3074503|FEDERATED表不能与查询缓存|3074504|一起使用|3074505|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html|1|http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html^0|0|R|O|0|0|0|5|19|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@$D|16|E|17|1|18]]|A|$]]|$1|F|3|G|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1A|8|@]|9|@$D|1B|E|1C|1|1D]]|A|$]]|$1|M|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1F|8|@]|9|@]|A|$]]|$1|Q|3|R|5|P|7|1G|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1H|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]|12|$5|X|Y|Z|A|$10|13]]]]

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

One option that may serve your needs, besides creating a view, is to use the <a href="http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html" rel="nofollow">FEDERATED storage engine</a> locally.

<pre><code>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';
</code></pre>

There are currently some <a href="http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html" rel="nofollow">limitations with the FEDERATED storage engine</a>. Here are a couple especially important ones:

<ul>
<li>FEDERATED tables do not support transactions</li>
<li>FEDERATED tables do not work with the query cache</li>
</ul>

I am migrating an MS Access application (which has linked tables to a MSSQL Server) to 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. 

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

<pre><code>SELECT * FROM customers AS dbo_customers
</code></pre>

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

<pre><code>SELECT * FROM dbo_customers
</code></pre>

and have it return data from the customers table.

How do I create a table alias in MySQL

SQL数据库

SQL Server

数据库

MySQL

我正在将MS Access应用程序(已将表链接到MSSQL Server)迁移到MySQL。为了克服一些MSAccess表命名问题，我正在寻求一种解决方案，以添加一个指向MySQL数据库中现有表的MySQL表别名。理想情况下，我想在mysql中创建别名'dbo_customers‘，它也会指向mysql中的custom...

问如何在MySQL中创建表别名
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中创建表别名EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中创建表别名
EN