如何克隆/複製表及其資料、觸發器和索引?
為了建立一個新表,使其與舊錶及其資料、觸發器和索引完全相同,我們需要執行以下兩個查詢
CREATE TABLE new_table LIKE old_table; INSERT new_table SELECT * from old_table;
示例
mysql> Create table employee(ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(20)); Query OK, 0 rows affected (0.21 sec) mysql> Describe employee; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | NAME | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.07 sec) mysql> Insert into employee(name) values('Gaurav'),('Raman'); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> Select * from employee; +----+--------+ | ID | NAME | +----+--------+ | 1 | Gaurav | | 2 | Raman | +----+--------+ 2 rows in set (0.00 sec)
下面的查詢將建立表 employee1,它具有與表 employee 相似的結構。可以透過執行 DESCRIBE 查詢對其進行檢查。
mysql> create table employee1 like employee; Query OK, 0 rows affected (0.19 sec) mysql> describe employee1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | NAME | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.14 sec)
現在,下面的查詢將向 employee1 中插入與 employee 中相同的值,如下所示
mysql> INSERT INTO employee1 select * from employee; Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from employee1; +----+--------+ | ID | NAME | +----+--------+ | 1 | Gaurav | | 2 | Raman | +----+--------+ 2 rows in set (0.00 sec)
透過這種方式,我們可以克隆表及其資料、觸發器和索引。
廣告