MySQL重新命名表

在本教學中，您將學習如何使用MySQL RENAME TABLE語句和ALTER TABLE語句重新命名表。

MySQL RENAME TABLE語句簡介

由於業務需求變化，我們需要將當前表重新命名為新錶，以更好地反映或表示新情況。 MySQL提供了一個非常有用的語句來更改一個或多個表的名稱。

要更改一個或多個表，我們使用RENAME TABLE語句如下：

RENAME TABLE old_table_name TO new_table_name;

舊表(old_table_name)必須存在，新表(new_table_name)必須不存在。 如果新表new_table_name存在，則該語句將失敗。

除了表之外，我們還可以使用RENAME TABLE語句來重新命名檢視。

在執行RENAME TABLE語句之前，必須確保沒有活動事務或鎖定表。

請注意，不能使用RENAME TABLE語句來重新命名臨時表，但可以使用ALTER TABLE語句重新命名臨時表。

在安全性方面，我們授予舊表的任何許可權必須手動遷移到新表。

在重新命名表之前，應該徹底地評估影響。 例如，應該調查哪些應用程式正在使用該表。 如果表的名稱更改，那麼參照表名的應用程式程式碼也需要更改。 此外，您必須手動調整參照該表的其他資料庫物件，如檢視，儲存過程，觸發器，外來鍵約束等。 我們將在下面的例子中更詳細地討論。

MySQL RENAME TABLE範例

首先，我們建立一個名為hrdb的新資料庫，它由兩個表組成：employees 和 departments。

建立資料庫 -

CREATE DATABASE IF NOT EXISTS hrdb;

建立表 -

USE hrdb;

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    id int AUTO_INCREMENT primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    department_id int not null,
    FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
);

其次，將樣本資料插入到 employees 和 departments 表中：

-- 插入資料到 departments 表中
INSERT INTO departments(dept_name)
VALUES('Sales'),('Markting'),('Finance'),('Accounting'),('Warehouses'),('Production');

-- 插入資料到 employees 表中
INSERT INTO employees(first_name,last_name,department_id) 
VALUES('John','Doe',1),
 ('Bush','Lily',2),
 ('David','Dave',3),
 ('Mary','Jane',4),
 ('Jonatha','Josh',5),
 ('Mateo','More',1);

第三，查詢在 employees 和 departments 表中的資料：

mysql> SELECT 
    department_id, dept_name
FROM
    departments;
+---------------+------------+
| department_id | dept_name  |
+---------------+------------+
|             1 | Sales      |
|             2 | Markting   |
|             3 | Finance    |
|             4 | Accounting |
|             5 | Warehouses |
|             6 | Production |
+---------------+------------+
6 rows in set

mysql> SELECT 
    id, first_name, last_name, department_id
FROM
    employees;
+----+------------+-----------+---------------+
| id | first_name | last_name | department_id |
+----+------------+-----------+---------------+
|  1 | John       | Doe       |             1 |
|  2 | Bush       | Lily      |             2 |
|  3 | David      | Dave      |             3 |
|  4 | Mary       | Jane      |             4 |
|  5 | Jonatha    | Josh      |             5 |
|  6 | Mateo      | More      |             1 |
+----+------------+-----------+---------------+
6 rows in set

重新命名檢視參照的表

如果重新命名一個被檢視參照的表，在重新命名表後，檢視就無效了，並且必須手動調整檢視。

例如，我們基於employees和departments表建立一個名為v_employee_info的檢視，如下所示：

CREATE VIEW v_employee_info as
    SELECT 
        id, first_name, last_name, dept_name
    from
        employees
            inner join
        departments USING (department_id);

檢視使用內連線子句來連線employees和departments表。

以下SELECT語句返回v_employee_info檢視中的所有資料。

mysql> SELECT 
    *
FROM
    v_employee_info;
+----+------------+-----------+------------+
| id | first_name | last_name | dept_name  |
+----+------------+-----------+------------+
|  1 | John       | Doe       | Sales      |
|  2 | Bush       | Lily      | Markting   |
|  3 | David      | Dave      | Finance    |
|  4 | Mary       | Jane      | Accounting |
|  5 | Jonatha    | Josh      | Warehouses |
|  6 | Mateo      | More      | Sales      |
+----+------------+-----------+------------+
6 rows in set

現在，將v_employee_info檢視中的employees表重新命名為people，並查詢檢視的資料。

RENAME TABLE employees TO people;

-- 查詢資料
SELECT 
    *
FROM
    v_employee_info;

MySQL返回以下錯誤訊息：

1356 - View 'hrdb.v_employee_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

我們可以使用CHECK TABLE語句來檢查v_employee_info檢視的狀態如下：

CHECK TABLE v_employee_info;
mysql> CHECK TABLE v_employee_info;
+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table                | Op    | Msg_type | Msg_text                                                                                                                               |
+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+
| hrdb.v_employee_info | check | Error    | Table 'hrdb.employees' doesn't exist                                                                                                   |
| hrdb.v_employee_info | check | Error    | View 'hrdb.v_employee_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
| hrdb.v_employee_info | check | error    | Corrupt                                                                                                                                |
+----------------------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set

需要手動更改v_employee_info檢視，以便它參照people表而不是employees表。

重新命名由儲存過程參照的表

如果要重新命名由儲存過程參照的表，則必須像對檢視一樣進行手動調整。

首先，將people表重新命名為employees表。

RENAME TABLE people TO employees;

然後，建立一個名為get_employee的新儲存過程，該過程參照employees表。

DELIMITER $$

CREATE PROCEDURE get_employee(IN p_id INT)

BEGIN
 SELECT first_name
 ,last_name
 ,dept_name
 FROM employees
 INNER JOIN departments using (department_id)
 WHERE id = p_id;
END $$

DELIMITER;

接下來，執行get_employee儲存過程從employees表來獲取id為1的員工的資料，如下所示：

CALL get_employee(1);

執行上面查詢語句，得到以下結果 -

mysql> CALL get_employee(1);
+------------+-----------+-----------+
| first_name | last_name | dept_name |
+------------+-----------+-----------+
| John       | Doe       | Sales     |
+------------+-----------+-----------+
1 row in set

Query OK, 0 rows affected

之後，我們再次將employees表重新命名為people表。

RENAME TABLE employees TO people;

最後，呼叫get_employee儲存過程來獲取id為2的員工資訊：

CALL get_employee(2);

MySQL返回以下錯誤訊息：

1146 - Table 'hrdb.employees' doesn't exist

要解決這個問題，我們必須手動將儲存過程中的employees表更改為people表。

重新命名參照外來鍵的表

departments表使用department_id列連結到employees表。 employees表中的department_id列是參照departments表的department_id列作為外來鍵。

如果重新命名departments表，那麼指向departments表的所有外來鍵都不會被自動更新。 在這種情況下，我們必須手動刪除並重新建立外來鍵。

RENAME TABLE departments TO depts;

我們刪除ID為1的部門，由於外來鍵約束，people表中的所有行也應刪除。 但是，我們將department表重新命名為depts表，而不會手動更新外來鍵，MySQL會返回錯誤，如下所示：

DELETE FROM depts 
WHERE
    department_id = 1;

執行上面語句，得到以下以下錯誤提示 -

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`hrdb`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `depts` (`department_id`))

重新命名多個表

也可以使用RENAME TABLE語句來一次重新命名多個表。 見下列宣告：

RENAME TABLE old_table_name_1 TO new_table_name_2,
             old_table_name_2 TO new_table_name_2,...

以下語句將 people 和 depts 重新命名為 employees 和 departments 表：

RENAME TABLE depts TO departments,
             people TO employees;

注意RENAME TABLE語句不是原子的。所以如果在任何時候發生錯誤，MySQL會將所有重新命名的表都回滾到舊名稱。

使用ALTER TABLE語句重新命名表

我們可以使用ALTER TABLE語句重新命名一個表，如下所示：

ALTER TABLE old_table_name
RENAME TO new_table_name;

RENAME TABLE語句不能用於重新命名臨時表，這時就可以使用ALTER TABLE語句來重新命名一個臨時表。

重新命名臨時表範例

首先，我們建立一個臨時表，其中包含來自employees表的last_name列的所有唯一的姓氏：

CREATE TEMPORARY TABLE lastnames
SELECT DISTINCT last_name from employees;

第二步，使用RENAME TABLE重新命名姓氏表：

RENAME TABLE lastnames TO unique_lastnames;

MySQL返回以下錯誤訊息：

Error Code: 1017. Can't find file: '.\hrdb\lastnames.frm' (errno: 2 - No such file or directory)

第三，使用ALTER TABLE語句來重新命名姓氏表。

ALTER TABLE lastnames
RENAME TO unique_lastnames;

第四，從unique_lastnames臨時表查詢資料：

SELECT 
    last_name
FROM
    unique_lastnames;

+-----------+
| last_name |
+-----------+
| Doe       |
| Lily      |
| Dave      |
| Jane      |
| Josh      |
| More      |
+-----------+
6 rows in set

在本教學中，我們向您展示了如何使用MySQL RENAME TABLE和ALTER TABLE語句重新命名表。