Khóa học miễn phí MySQL – Rename Columns nhận dự án làm có lương

MySQL – Rename Columns

Table of content


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.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc