mysql复制表_MySQL实战：复制表的几种方式

链接：blog.csdn.net/shentian885/article/details/106582925/

只复制表结构：只复制表结构，包括主键、索引，但不会复制表数据

create table tableName like someTable;

只关注表数据：复制表的大体结构及全部数据，不会复制主键、索引等

create table tableName select * from someTable;

完整复制(表结构+数据)：分两步完成，先复制表结构，再插入数据。

create table tableName like someTable;
insert into tableName select * from someTable;

实战案例

创建表数据

create table t1(
  id int not null auto_increment primary key,
  name varchar(50)
);

insert into t1(name) values('zhangsan');
insert into t1(name) values('lisi');

create index idx_name on t1(name);

查看表数据

mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
|  2 | lisi |
| 1 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1 | 0 | PRIMARY | 1 | id | A |           2 | NULL |   NULL | | BTREE | |               | YES | NULL |
| t1 | 1 | idx_name | 1 | name | A |           2 | NULL |   NULL | YES | BTREE | |               | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

只复制表结构

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t2 |          0 | PRIMARY |            1 | id | A | 0 |     NULL | NULL |      | BTREE |         | | YES | NULL |
| t2 |          1 | idx_name |            1 | name | A | 0 |     NULL | NULL | YES | BTREE |         | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

只复制数据

mysql> create table t3 select * from t1;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+----+----------+
| id | name |
+----+----------+
|  2 | lisi |
| 1 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)
mysql> show create table t3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
  `id` int NOT NULL DEFAULT '0',
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show index from t3;
Empty set (0.00 sec)
mysql>

完整复制

mysql> create table t4 like t1;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t4 select * from t1;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t4;
+----+----------+
| id | name |
+----+----------+
|  2 | lisi |
| 1 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)
mysql> show create table t4;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t4 | CREATE TABLE `t4` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show index from t4;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t4 | 0 | PRIMARY | 1 | id | A |           2 | NULL |   NULL | | BTREE | |               | YES | NULL |
| t4 | 1 | idx_name | 1 | name | A |           2 | NULL |   NULL | YES | BTREE | |               | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
mysql>

往期推荐

• MySQL多表查询FROM和JOIN的用法与性能优化

• MySQL SQL查询语句的用法和优化

• MySQL事务隔离级别和Spring事务关系介绍

如果感觉推送内容不错，不妨右下角点个在看，感谢支持！