One column, two names, mysql

Question

I am trying to standardize column naming in my mysql database. During the transition, I need to be able to access a column of a given table using both the new and the old column names. Specifically, these two queries need to return the same results.

SELECT `old` FROM `table`;
SELECT `new` FROM `table`;

Also, any new data inserted into the new column name should be available through the old column name; and viceversa.

Answer 1

I believe you are looking for a view. You can define the view as:

create view v_table as
    select t.*, `old` as `new`
    from `table` t;

Assuming no naming conflict, this will give you both.

Now, you might want to go a step further. You can rename the old table and have the view take the name of the old table:

rename table `table` to `old_table`;
create view t as
    select t.*, `old` as `new`
    from `old_table` t;

That way, everything that references table will start using the view with the new column name.