CODEWITHSUNDEEP
mysqlview
David
David

Reputation: 857

MySQL Table Alias

I'm migrating an application from a case insensitive database to MySQL. My DBA does not permit changing any Mysql configuration settings so using the "lower_case_table_names" is not an option.

Does MySQL have any aliasing ability to have multiple table names point to the same table?

For example, if USER and usersB were aliased to the same table, then these two queries would insert into the same table:

Insert into USER VALUES (2, 3....);

and

Insert into usersB VALUES (2, 3, ....);

Views come close to the desired functionality, but I would like to have update and alter structure commands work against all of the aliases.

Thanks!

Upvotes: 0

Views: 911

Answers (4)

g r
g r

Reputation: 63

A view is not the same as a table alias (that you can create with the CREATE ALIAS statement in DBMSs that support it) at least until foreign key constraints are supported between tables and views and the client can determine if a view is updatable. MySQL does not. A table alias that can point to a table in a different database is very useful. Feature request.

Upvotes: 0

Wrikken
Wrikken

Reputation: 70540

One (well three) words: MERGE storage engine. See: http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html

It's absolutely no problem to have only one table in a MERGE, even none would be allowed.

Edit: nevermind, ALTER TABLE wouldn't work for this engine (the next operation to the MERGE table would fail.

Upvotes: 0

MJB
MJB

Reputation: 7686

You can use views and have UPDATEs (or any DML) work properly, if you maintain the code for the view. Unfortunately, you cannot have ALTER statements (or any DDL) work against all views. However, when altering the base table, you could regenerate all the views with a single script to make sure that the system is in sync.

As I understood your question, you are talking about views with no joins or unions or even filters -- just create view USER as select * from usersB. Insert, updates, and deletes would work in your example. ALTERs would not.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332771

Does MySQL have any aliasing ability to have multiple table names point to the same table?

There is no such SQL convention - an INSERT statement inserts record(s) into one, and only one, table. User defined types might, but MySQL doesn't support them.

A view can be used to present a unified resultset - IE:

CREATE VIEW vw_users AS
   SELECT * FROM USER
   UNION 
   SELECT * FROM USERSB

...and are potentially updateable, but will not work for your desired functionality.

Upvotes: 1

Related Questions