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"
});
 //连接到 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);
 //重命名前的记录
  sql = "DESCRIBE STUDENTS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
    console.log("--------------------------");
  });
 //将列 ID 重命名为"stud_id"
  sql = "ALTER TABLE STUDENTS RENAME COLUMN ID TO stud_id;"
  con.query(sql);
 //重命名后的记录
  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://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);
        }
    }
}                                 

输出

获得的输出如下所示 -

Connected successfully...!
Name renamed successfully...!
tutorial_Id int
tutorial_title varchar(50)
tutorial_author varchar(30)     

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'NOVELS'
old_column_name = 'PAGES'
new_column_name = 'PAGES_COUNT'
#创建游标对象
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.