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
語句重命名錶。