JIYIK CN >

In this article, we aim to explore different ways to rename columns in MySQL.

ALTER TABLEThe command is mainly used to change the format of a given MySQL table. It can be used to add columns, change the data type within a column, delete columns, and sometimes even rename the entire database.

ALTER TABLEThere are two main ways to change column names using the command.

• Statements with ALTER TABLEcommandsRENAME
• Statements with ALTER TABLEcommandsCHANGE

Before we get started, however, we need to create a dummy dataset to work with. Here, we create a table, student_details, and a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table and rows in it containing the first and last name of the students. To see the entries in the data, we use the following code.

SELECT * FROM student_details;

The above line of code will give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Let's aim to stu_firstNamerename the columns to something simple firstName.

RENAMERename columns using the

One of the most basic ways to change the name of a column in MySQL is by using RENAME COLUMNthe -CLIENT clause. The syntax for performing the above operation can be stated as follows:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Here, old_namerepresents the column name to be changed and new_namerepresents the new name of the column.

To change stu_firstNamethe column, we would use the following code:

ALTER TABLE student_details RENAME COLUMN stu_firstName TO firstName;

The above line of code will give the following output:

stu_id    firstName 	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

CHANGERename columns using the

A more complex way to change the name of a column is to use CHANGEthe clause. This clause also helps to adjust the data type of the column as well as the column name. The syntax to do this is as follows:

ALTER TABLE table_name CHANGE old_name new_name Data Type;

To stu_firstNamechange the column and its data type to VARCHAR(40), we will use the following code:

ALTER TABLE student_details CHANGE stu_firstName firstName VARCHAR(40);
-- Here VARCHAR(40) is the new data type

The above code snippet will give the following output using the new data types mentioned above.

stu_id    firstName 	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

So, with the help of the above two techniques, we can effectively change the name of any column of a table in MySQL. We can conclude that understanding ALTER TABLEthe command is a prerequisite for exploring complex statements.