Mysql 简明教程

MySQL - Rename Tables

可能出现这种情况：用户和数据库管理员可能希望更改关系数据库中表的名称，以使表的名称更适合特定情况。

There can be a situation where both users and database administrators might want to change the name of a table in a relational database to make the table’s name more suitable for a specific situation.

MySQL 提供了两种不同的方式来重命名 MySQL 表。我们可以使用 RENAME TABLE 或 ALTER TABLE 语句。在本教程中，我们将通过合适的示例来理解它们。

MySQL provides two different ways to rename an MySQL table. We can use either the RENAME TABLE or ALTER TABLE statement. In this tutorial, we will understand them with suitable examples.

MySQL RENAME TABLE Statement

MySQL RENAME TABLE 语句用于使用另一个名称重命名数据库中的现有表。

The MySQL RENAME TABLE statement is used to rename an existing table in a database with another name.

Syntax

以下是 MySQL RENAME TABLE 语句的基本语法：

Following is the basic syntax of the MySQL RENAME TABLE statement −

RENAME TABLE table_name TO new_name;

其中，table_name 是现有表的名称，new_name 是要分配的新名称。

Where, table_name is the name of an existing table and new_name is the new name which you want to assign.

Example

让我们首先使用 CREATE 语句在 MySQL 数据库中创建一个名为 CUSTOMERS 的表，如下所示：

Let us start by creating a table with name CUSTOMERS in MySQL database using CREATE statement as shown below −

CREATE TABLE CUSTOMERS (
	ID INT,
	NAME VARCHAR(20),
	AGE INT
);

在这里，我们使用以下查询将上述创建的 CUSTOMERS 表重命名为 BUYERS ：

Here, we are renaming the above-created CUSTOMERS table to BUYERS using the following query −

RENAME TABLE CUSTOMERS to BUYERS;

Output

该表已在没有错误的情况下重命名。

The table has been renamed without any errors.

Query OK, 0 rows affected (0.01 sec)

Verification

执行以下查询以检索 CUSTOMERS 表的描述：

Execute the following query to retrieve the description of the CUSTOMERS table −

DESC CUSTOMERS;

它显示错误，因为我们已将 CUSTOMERS 表名更改为 BUYERS，并且我们的数据库中没有 CUSTOMERS 表。

It display an error because, we have changed the CUSTOMERS table name to BUYERS and there is no CUSTOMERS table in our database.

ERROR 1146 (42S02): Table 'tutorials.customers' doesn't exist

Renaming Multiple Tables

使用 MySQL RENAME TABLE 语句，我们还可以在单个查询中重命名多个表。

Using the MySQL RENAME TABLE statement, we can also rename multiple tables in a single query.

Syntax

以下是使用 MySQL RENAME TABLE 语句重命名多个表的语法：

Following is the syntax for renaming multiple tables using MySQL RENAME TABLE statement −

RENAME TABLE old_table1 TO new_table1,
   old_table2 TO new_table2,
   old_table3 TO new_table3;

Example

在以下示例中，我们创建了三个不同的名为 Cust1 、 Cust2 和 Cust3 的表：

In the following example, we are creating three different tables named Cust1, Cust2, and Cust3 −

CREATE TABLE Cust1(ID INT);
CREATE TABLE Cust2(ID INT);
CREATE TABLE Cust3(ID INT);

在这里，我们使用以下查询验证上述表是否已创建：

Here, we are verifying whether the above tables are created or not using the following query −

SHOW TABLES;

正如我们在下面的输出中看到的，上述表已成功创建。

As we can see in the output below, the above tables have been successfully created.

现在，让我们使用以下查询重命名所有上创建的表：

Now, let us rename all the above-created tables using the following query −

RENAME TABLE Cust1 TO Buyer1, Cust2 TO Buyer2, Cust3 TO Buyer3;

Output

所有三个表均已重命名，没有任何错误。

All three tables has been renamed without any errors.

Query OK, 0 rows affected (0.03 sec)

Verification

让我们再次验证表列表，以查找表名是否已被更改 −

Let us verify the list of the tables again to find whether the table names have been changed or not −

SHOW TABLES;

正如我们在下面的输出中看到的那样，所有三个表均已成功重命名。

As we can see the output below, all three tables have been successfully renamed.

Renaming a Table using ALTER TABLE statement

在 MySQL 中，我们还可以使用 RENAME with ALTER TABLE 语句来修改现有表的名称。

In MySQL, we can also use the RENAME with ALTER TABLE statement to modify the name of an existing table.

Syntax

以下是使用 ALTER TABLE 语句重命名表的语法 −

Following is the syntax to rename a table with ALTER TABLE statement −

ALTER TABLE existing_table_name RENAME TO new_table_name

Example

在以下查询中，我们创建了一个名为 PLAYERS 的表。

In the following query, we are creating a table named PLAYERS.

CREATE TABLE PLAYERS (
	ID INT,
	NAME VARCHAR(20),
	AGE INT
);

现在，让我们使用以下查询将上述创建的表重命名为新名称 TEAMS −

Now, let us rename the above-created table with a new name TEAMS using the following query −

ALTER TABLE PLAYERS RENAME TO TEAMS;

Output

该表已在没有错误的情况下重命名。

The table has been renamed without any errors.

Query OK, 0 rows affected (0.02 sec)

Verification

执行以下查询以检索 PLAYERS 表的描述 −

Execute the following query to retrieve the description of the PLAYERS table −

DESC PLAYERS;

它将显示错误，因为我们将 PLAYERS 表重命名为 TEAMS，并且我们的数据库中没有 PLAYERS 表。

It will display an error because, we have renamed the PLAYERS table to TEAMS and there is no PLAYERS table in our database.

ERROR 1146 (42S02): Table 'tutorials.players' doesn't exist

Renaming Table Using a Client Program

除了使用 MySQL 查询在 MySQL 数据库中重命名表之外，我们还可以使用客户端程序对表执行 RENAME TABLE 操作。

In addition to renaming a table in MySQL Database using MySQL query, we can also perform the RENAME TABLE operation on a table using a client program.

Syntax

以下是使用各种编程语言在 MySQL 数据库中重命名表的语法 −

Following are the syntaxes to rename table in MySQL database in various programming languages −

Example

以下是这些程序 −

Following are the programs −