为了创建一个像旧表一样的新表及其数据、触发器和索引，我们需要运行以下两个查询
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)
登录后复制
下面的查询将创建与表employee 具有相似结构的表employee1。可以通过运行 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)
登录后复制
通过这种方式，我们也可以克隆表及其数据、触发器和索引。
以上就是如何克隆/复制表及其数据、触发器和索引？的详细内容，更多请关注每日运维网(www.mryunwei.com)其它相关文章！
