table alias (or 'symlink') in mysql

Question

in MySQL5.1, is there a way to make one table accessible by two different names? I'm thinking about somethink like a symlink on linux filesystems.

I know theres the

CREATE VIEW myview AS SELECT * FrOM mytable

thing, but I don't only need to SELECT from both names, but also delete etc ...

You might ask why I want to do this? It's about getting a commercial, closed-source app to work, which is crappily programmed (usually, the table names are all lower-case, but occasionally, they use capitalized names for the same table ...). Oh, that would be another idea: Is there a way to tell MySQL not to care about capitalization of table names (like on Windows filesystems?)? that would also do the trick ...

... found the answer: lower_case_table_names, a variable. Since this is the solution to my problem, but not the answer to my question (which is still interesting), i don't answer my own question yet ...

Thanks for your insight!

A.

Answer 1

Consider looking into the lower_case_table_names system variable.

Answer 2

An abstraction layer like MySQL Proxy could help here.

Answer 3

You can create updatable views in mysql 5.0, it seems. Check here, and search the page for 'updatable'.

I'm not a mysql innards expert, but I imagine that simply symlinking or hard-linking will end in madness. mysql probably caches table file state by filename, and if 2 files are pointing @ the same data, you may end up in a situation where mysql will change the data file at one 'name', but not the other, resulting in flagrant corruption.

Answer 4

I think mysql's Federated tables could be an answer to your underlying question. Not the best performance, but I think that if you created a federated table which points to the existing table on localhost it would work.