MySQL - 重新命名列

要透過 Node.js 程式重新命名 MySQL 資料庫中表的列，我們需要使用 **mysql2** 庫的 **query()** 函式執行 **ALTER TABLE** 語句，如下所示：

sql= "ALTER TABLE table_name
RENAME COLUMN old_column1_name TO new_column1_name";  
con.query(sql);

要透過 Java 程式重新命名 MySQL 資料庫中表的列，我們需要使用 **JDBC** 函式 **executeUpdate()** 執行 **ALTER TABLE** 語句，如下所示：

String sql = "ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name";
statement.executeUpdate(sql);

要透過 Python 程式重新命名 MySQL 資料庫中表的列，我們需要使用 MySQL **Connector/Python** 的 **execute()** 函式執行 **ALTER TABLE** 語句，如下所示：

sql = "ALTER TABLE table_name 
RENAME COLUMN old_column_name TO new_column_name"
cursorObj.execute(sql);

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);
  });
});

輸出

生成的輸出如下所示：

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://: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);
        }
    }
}                                 

輸出

獲得的輸出如下所示：

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()                                            

輸出

以上程式碼的輸出如下：

Column 'tutorial_price' is renamed to 'tutorial_amount' in table 'tutorials_tbl_cloned' successfully.