Mysql 简明教程
MySQL - Rename Columns
MySQL 中的 ALTER TABLE 语句可用来更改表结构。比如，我们可以使用此语句添加、删除或重命名列等。
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.
以下是可以与 ALTER TABLE 语句配合使用的两个选项，用于重命名表的一列或多列：
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: 重命名表的一列或多列需要 ALTER 和 DROP 权限。
Note: Renaming a column(s) of table requires ALTER and DROP privilages.
Using the RENAME COLUMN
在 MySQL 中，我们可以使用 ALTER TABLE 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
以下是 MySQL 表中重命名列的语法：
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
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：
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
);在这里，我们使用 DESCRIBE 命令来显示上面创建的表结构的相关信息：
Here, we are using the DESCRIBE command to display the information about the above created table structure −
DESCRIBE CUSTOMERS;如我们在下表中看到的，我们有三个列存在于 CUSTOMERS 表中 −
As we can see in the table below, we have three columns present in CUSTOMERS table −
现在，我们使用以下查询将名为 ID 的列重命名为 cust_id −
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: 0Verification
让我们检索 CUSTOMERS 表描述以验证是否已将 ID 列重命名为 stud_id −
Let us retrive the CUSTOMERS table description to verify whether the column ID is renamed to stud_id or not −
DESCRIBE CUSTOMERS;正如我们在输出表中观察到的，ID 列已成功重命名为 stud_id。
As we observe in the output table, the ID column is renamed to stud_id successfully.
Example
现在，我们正在重命名 CUSTOMERS 表中名为 NAME 和 AGE 的另外两列，重命名为 cust_name 和 cust_age −
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;Using CHANGE COLUMN
在 MySQL 中，我们可以使用 ALTER TABLE … 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
以下是 MySQL 中 ALTER TABLE … CHANGE 命令的语法 −
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
考虑先前更新的 CUSTOMERS 表，让我们更改 cust_id 列的名称和数据类型 −
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: 0Verification
使用以下查询，我们可以验证列 cust_id 是否已更改其名称和数据类型 −
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.
Example
在这里，我们正在更改 CUSTOMERS 表中 multiple 列（cust_name 和 cust_age）的名称和数据类型 −
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: 0Verification
让我们检索 CUSTOMERS 表描述以验证列名和数据类型是否已更改 −
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.
Renaming a Column of a Table Using a Client Program
除了使用 MySQL 查询重命名 MySQL 数据库中表的一列之外，我们还可以使用客户端程序对表执行 ALTER TABLE 操作。
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.