How do I Rename a Column in MySQL?

MySQL is a popular database management system that provides the backbone for a huge portion of today’s internet. Developed and maintained by Oracle, MySQL is an open-source RDBMS (relational database management system) with a client-server model. It offers secure database storage, fast performance, and powerful features.

This guide will showcase how to rename a column in MySQL.

Renaming a column in MySQL

In MySQL, a database generally consists of multiple tables containing rows and columns that hold various data. For demonstration, we will rename the column name of a particular table.

For demonstration, I’ll be using this sample database. It’s a very big database that should suffice for almost any testing situation. For convenience, I’ll use phpMyAdmin to access the database and run queries. Learn more about installing and configuring phpMyAdmin and MySQL.

Basic structure
To rename a column, the query structure will look like this.

$ ALTER TABLE <table_name> CHANGE <old_column_name> <new_column_name> <data_type>

If there are multiple columns to be renamed, we can do that in a single query as well. In such a case, the query structure will look like this.

$ ALTER TABLE <table_name> CHANGE <old_column_name_1> <new_column_name_1> <data_type_1>, CHANGE <old_column_name_2> <new_column_name_2> <data_type_2>

Alternatively, if you want to rename a table, then the query structure will look like this.

$ RENAME TABLE <old_table_name> TO <new_table_name>

You can also rename multiple tables in a single go.

$ RENAME TABLE <old_table_1> TO <new_table_name_1>, <old_table_name_2> TO <new_table_name_2>

MySQL also allows using RENAME between multiple databases.

$ RENAME TABLE <current_database>.<table_name> TO <other_database>.<table_name>

Renaming table column
Let’s have a look at the sample database table countries.

$ SELECT * from countries;

From this table, let’s rename country_name (varchar(40)) to country_label (varchar(40)). The query will look something like this.

$ ALTER TABLE countries CHANGE country_name country_label varchar(40);

Verify the change.

$ SHOW COLUMNS FROM countries;

Renaming multiple columns
We’ll be renaming multiple columns in the table countries in the next example. Let’s change country_id to country_code, country_label to country_name, and region_id to region­_code.

ALTER TABLE countries
CHANGE country_id country_code char(2),
CHANGE country_label country_name varchar(40),
CHANGE region_id region_code int;

Verify the changes.

$ SHOW COLUMNS FROM countries;

Renaming tables

Now, let’s try renaming the table. We’ll be renaming the table countries to independent_regions. First, note the current table names.

$ SHOW TABLES;

Run the following query to rename the target table.

$ RENAME TABLE countries TO independent_regions;

Verify the change.

$ SHOW TABLES;

Renaming multiple tables
Now, let’s try changing multiple table names at the same time. Make a note of all the current tables.

$ SHOW TABLES;

Next, run the following query.

RENAME TABLE
countries TO independent_regions,
jobs TO assigned_tasks,
departments TO sections;

Verify the changes.

$ SHOW TABLES;

Final thoughts

Congratulations! You’ve successfully renamed an existing column in MySQL. This tutorial also showcases renaming an existing table. For both renaming columns and tables, you need to understand the fundamentals of the statement ALTER TABLE.

Every MySQL table is based on a key design. In MySQL terminology, it’s known as a schema. It contains the necessary data about the pattern of the data placement in the table. This guide explores the concept of schema with practical examples.

Happy computing!

from https://ift.tt/3lbadu4