How do I create a table alias in MySQL

问题:

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:

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.

回答1:

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

回答2:

You can create a 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.

回答3:

@OMG Ponies ponies said in a comment:

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.

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.

回答4:

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

{dbo_NAME} to {NAME}.

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

回答5:

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 FEDERATED storage engine locally.

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 tables do not support transactions
• FEDERATED tables do not work with the query cache

回答6:

You could create a view named dbo_customers which is backed by the customers table.

回答7:

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

imagine we have table named rus_vacancies and need its English equivalent

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_vacancies equals to table eng_vacancies for any read-write operations

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

回答8:

You have to create a view for the table and then select from that.