Rename column in MySQL using an alias

Actually the problem arises since we have named a column authorization which is ok on MySQL but fails miserably on Postgres, authorization is a reserved keyword there.

We want to have all schemas in sync even on different databases, so we want to rename authorization in the MySQL catalogue(s) to something neutral.

It would be great if the renaming of the MySQL table columns would work seamlessly with older and newer versions of the applicatation at least for a couple of days so that the transition is smooth.

Does anybody know how to do this? A nice idea would be to have some sort of alias/redirection, e.g. create a new column _authorization that is actually the same column as authorization but under the new name. Queries using the new name _autorizat开发者_JAVA百科ion will work as well as queries using the old name. Then we can update the application. If all servers have the latest binaries, we can drop the alias and rename the column.

Any ideas? Any help is greatly appreciated.

A nice idea would be to have some sort of alias/redirection...

No such functionality exists. The closest is to use a view based on the table, because it's easier to rename a column in a view as there isn't any underlying data to change. Otherwise:

Rename the column in MySQL using the ALTER TABLE statement:

ALTER TABLE [your table]
     CHANGE authorization [new_col_name] [column_definition]

I also had to use an alias to a column (e.g. a particular app needed 'id' column instead of 'entry_id').
i was able to create a view (MySQL 5.1+ recommended for views) from the table with an extra alias to the culprit column. for the 'authorization' column from your example:

CREATE VIEW maintable_view AS 
  SELECT *, authorization AS authcol FROM MAINTABLE

if you run a update-query on the view, and set 'authcol' to a new value,
this will update the 'authorization' column in 'maintable'.

UPDATE maintable_view SET authcol=22 WHERE id=3

http://dev.mysql.com/doc/refman/5.1/en/create-view.html