How do I create a table alias in MySQL

Question

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.

Darbie

Why not rename the table? – Axon

In some cases renaming the table won't work. For example, if you're working with two ORM systems with different naming conventions, you need an alias for both to work well. In that case, by the way, the accepted solution was good enough for me :) – Deportment

@OMGPonies I am in the situation that a big rename of many database objects is planned for the future while new queries are still being written. A rename of many tables in multiple prod databases for a multitenant app is a big deal, so we are planning to do it incrementally first renaming or (aliasing) logically so SQL code can be updated before the big table renames. – Solstice

Answer 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

Acidify

I should have said in my initial post that I must be able to update the data also. I don't think a view will meet my needs -- unless views are somehow update-able? – Darbie

I really don't think this is the simplest solution at all. – Kernite

Its absolutely the simplest David. It will behave almost identically to the original table. You can write to it, join it up in various queries, and so on. Performance wise, I'm not so sure, however from the point of view of your software and queries, the view will be indistinguishable from a real table for MOST purposes. – Transatlantic

Why would this be a bad solution at all? It seems to answer the question perfectly. Is it inefficient? I'm working with a legacy system where the original developer, as "security-by obscurity", named the user info table a long string of random characters. I'd love to be able to access it as "users". (For various reason, no, I can't just rename the existing table.) – Tallinn

If you use this in a stored procedure, then multiple people cannot access the stored procedure at the same time. – Deherrera

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

Rhodarhodamine

Answer 3

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")

Sera

Answer 4

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

Caught

Answer 5

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

Kernite

a regular use case is you checkout to an older commit and need you database work with the older codebase. – Glaucous

Answer 6

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.

Interstratify

Answer 7

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

Natishanative

Answer 8

I'd like to mention a bad solution I explored (and abandoned), which was to use hardlinks on the .frm, .MYD and .MYI files corresponding to my table in /var/lib/mysql/{name_of_my_database}/.

It does, however, NOT work. For InnoDB tables, it simply cannot (even if you hardlink the .idb file) because tables are also referenced in ibdata1.

For MyISAM tables, it kind of works, except it doesn't because in memory, the tables are still distinct and thus do not share cache. So if you write a row to original_table, it won't immediately appear in aliased_table. You would have to flush tables first… which defeats the purpose and even causes data loss (if you insert a row in both the original and the alias before flushing, only one row is kept).

I thought my experiment was worth mentioning as a cautionary tale.

Imphal