MySQL Table Alias

Question

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!

Answer 1

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.

Answer 2

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.

Answer 3

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.