MySQL – Rename Columns
The ALTER TABLE statement in MySQL can be used to change the structure of a table. For instance, we can add, delete, or rename the columns, etc. using this statement.
Following are the two options that can be used with the ALTER TABLE statement to rename a column(s) of a table:
- RENAME COLUMN
- CHANGE COLUMN
Note: Renaming a column(s) of table requires ALTER and DROP privilages.
Using the RENAME COLUMN
In MySQL, we can change the name of one or multiple columns of a specified table using the ALTER TABLE RENAME COLUMN command.
Syntax
Following is the syntax to rename a column in MySQL table −
ALTER TABLE table_name RENAME COLUMN old_column1_name TO new_column1_name, RENAME COLUMN old_column2_name TO new_column2_name, ...;
Example
First of all, let us create a table named CUSTOMERS using the query below −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL );
Here, we are using the DESCRIBE command to display the information about the above created table structure −
DESCRIBE CUSTOMERS;
As we can see in the table below, we have three columns present in CUSTOMERS table −
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | NULL | ||
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL |
Now, we are renaming the column named ID to cust_id using following query −
ALTER TABLE CUSTOMERS RENAME COLUMN ID TO cust_id;
Output
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
Let us retrive the CUSTOMERS table description to verify whether the column ID is renamed to stud_id or not −
DESCRIBE CUSTOMERS;
As we observe in the output table, the ID column is renamed to stud_id successfully.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
cust_id | int | NO | NULL | ||
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL |
Example
Now, we are renaming the other two columns in CUSTOMERS table named NAME and AGE to cust_name and cust_age −
ALTER TABLE CUSTOMERS RENAME COLUMN NAME TO cust_name, RENAME COLUMN AGE TO cust_age;
Output
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
To verify whether column names have been renamed or not, execute the following query −
DESCRIBE CUSTOMERS;
As we observe in the output table, the above mentioned columns are successfully renamed.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
cust_id | int | NO | NULL | ||
cust_name | varchar(20) | NO | NULL | ||
cust_age | int | NO | NULL |
Using CHANGE COLUMN
In MySQL, we can change the name of one or more columns along with their datatypes using the ALTER TABLE … CHANGE COLUMN command.
Syntax
Following is the syntax of the ALTER TABLE … CHANGE commnad in MySQL −
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name Data Type;
Example
Consider the previously updated CUSTOMERS table and, let us change the name and the datatype of cust_id column −
ALTER TABLE CUSTOMERS CHANGE COLUMN cust_id ID varchar(10);
Output
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
Using the following query, we can verify whether the column cust_id has changed its name and datatype or not −
DESCRIBE CUSTOMERS;
The name of the column and datatype has been changed successfully.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | varchar(10) | NO | NULL | ||
cust_name | varchar(20) | NO | NULL | ||
cust_age | int | NO | NULL |
Example
Here, we are changing the names and datatypes of multiple columns (cust_name and cust_age) in the CUSTOMERS table −
ALTER TABLE CUSTOMERS CHANGE COLUMN cust_name NAME DECIMAL(18,2), CHANGE COLUMN cust_age AGE VARCHAR(20);
Output
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
Let us retrive the CUSTOMERS table description to verify whether the columns name and datatype are changed or not −
DESCRIBE STUDENTS;
As we observe in the output table, the names and datatypes of above mentioned columns are successfully changed.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | varchar(10) | NO | NULL | ||
NAME | decimal(18,2) | NO | NULL | ||
AGE | varchar(20) | NO | NULL |
Renaming a Column of a Table Using a Client Program
In addition to rename a column of a table in MySQL Database using MySQL query, we can also perform the ALTER TABLE operation on a table using a client program.
Syntax
Following are the syntaxes to rename a column of a MySQL table in various programming languages −
To rename a column of a table in MySQL database through a PHP program, we need to execute ALTER TABLE statement using the mysqli function query() as −
$sql = "ALTER TABLE table_name RENAME COLUMN old_column1_name TO new_column1_name"; $mysqli->query($sql);
To Rename a Column of a table into MySQL database through a Node.js program, we need to execute ALTER TABLE statement using the query() function of the mysql2 library as −
sql= "ALTER TABLE table_name RENAME COLUMN old_column1_name TO new_column1_name"; con.query(sql);
To Rename a Column of a table into MySQL database through a Java program, we need to execute ALTER TABLE statement using the JDBC function executeUpdate() as −
String sql = "ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name"; statement.executeUpdate(sql);
To Rename a Column of a table into MySQL database through a Python program, we need to execute ALTER TABLE statement using the execute() function of the MySQL Connector/Python as −
sql = "ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name" cursorObj.execute(sql);
Example
Following are the programs −
$dbhost = ''localhost $dbuser = ''root $dbpass = ''password $dbname = ''TUTORIALS $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf(''Connected successfully.
''); // rename column; $sql = "ALTER TABLE tut_tbl RENAME COLUMN tutorial_id TO tutorial_IDs"; if ($mysqli->query($sql)) { printf("Column renamed successfully!.
"); } if ($mysqli->errno) { printf("Columns could be renamed!.
", $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
Column renamed successfully!.
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); sql = "USE TUTORIALS" con.query(sql); sql = "CREATE TABLE STUDENTS (ID INT NOT NULL, NAME VARCHAR(40) NOT NULL, AGE INT);" con.query(sql); //Records before renaming sql = "DESCRIBE STUDENTS;" con.query(sql, function(err, result){ if (err) throw err console.log(result); console.log("--------------------------"); }); //Renaming column ID to "stud_id" sql = "ALTER TABLE STUDENTS RENAME COLUMN ID TO stud_id;" con.query(sql); //Records after renaming sql = "DESCRIBE STUDENTS;" con.query(sql, function(err, result){ if (err) throw err console.log(result); }); });
Output
The output produced is as follows −
Connected! -------------------------- [ { Field: ''ID'', Type: ''int'', Null: ''NO'', Key: '''', Default: null, Extra: '''' }, { Field: ''NAME'', Type: ''varchar(40)'', Null: ''NO'', Key: '''', Default: null, Extra: '''' }, { Field: ''AGE'', Type: ''int'', Null: ''YES'', Key: '''', Default: null, Extra: '''' } ] -------------------------- [ { Field: ''stud_id'', Type: ''int'', Null: ''NO'', Key: '''', Default: null, Extra: '''' }, { Field: ''NAME'', Type: ''varchar(40)'', Null: ''NO'', Key: '''', Default: null, Extra: '''' }, { Field: ''AGE'', Type: ''int'', Null: ''YES'', Key: '''', Default: null, Extra: '''' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class RenameColumn { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Rename column name String sql = "ALTER TABLE tutorials_tbl RENAME COLUMN ID TO tutorial_Id"; statement.executeUpdate(sql); System.out.println("Name renamed successfully...!"); ResultSet resultSet = statement.executeQuery("DESCRIBE tutorials_tbl"); while (resultSet.next()) { System.out.print(resultSet.getString(1)+ " " +resultSet.getString(2)); System.out.println(); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! Name renamed successfully...! tutorial_Id int tutorial_title varchar(50) tutorial_author varchar(30)
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) table_name = ''NOVELS'' old_column_name = ''PAGES'' new_column_name = ''PAGES_COUNT'' #Creating a cursor object cursorObj = connection.cursor() rename_column_query = f"ALTER TABLE {table_name} CHANGE {old_column_name} {new_column_name} INT" cursorObj.execute(rename_column_query) print(f"Column ''{old_column_name}'' is renamed to ''{new_column_name}'' in table ''{table_name}'' successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
Column ''tutorial_price'' is renamed to ''tutorial_amount'' in table ''tutorials_tbl_cloned'' successfully.