What is the best/recommended way to rename tables (with production running) in azure mysql database

Neha Dwivedi 1 Reputation point
2021-12-07T10:02:36.607+00:00

I want to understand what would be the best way to rename tables keeping the service up. I am thinking of using views. I am planning to follow the following steps:

  1. Create views for all the tables.
  2. Change the queries in my application to refer to the views instead of table names.
  3. rename tables using rename or alter table
  4. Change the queries back to use the new table names.
  5. Delete the views.

Is it a good solution to go ahead? Is there any better way to do this?

Thanks

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
682 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Oury Ba-MSFT 15,276 Reputation points Microsoft Employee
    2021-12-09T18:25:44.693+00:00

    Hi @Neha Dwivedi Thank you for posting your Question on Microsoft Q&A.

    What are you trying to achieve here?

    If the intent is to perform online renaming of tables to ensure there is no locking on the tables while rename operations are performed, views may not help since views in turn will query the underlying tables which are being renamed but if the goal is to abstract the queries from the underlying tables and change the views on the fly to point to the new tables, views is the right path forward.

    Hope that help

    Regards,
    Oury