MySQL复制表3种方式_mysql 复制表

作者：小小林熬夜学编程 | 2024-04-25 14:20:02

踩

mysql 复制表

本节目标

1.掌握MySQL复制表的几种方式
复制表，即备份表，给我们在操作表的时候，留一条后路

复制表的三种方式：
1.只复制表结构
create table tableName like someTable;

2.只复制表数据
create table tableName select * from someTable;

3.复制表结构+数据
create table tableName like someTable;
insert into tableName select * from someTable;
在这里插入图片描述

实战环节：

0.前期准备

创建一个t1表和索引

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

插入两条数据

insert into t1(name) values('张三');
insert into t1(name) values('李四');
1
2

假如索引建在名字上

create index idx_name on t1(name);
1

结果如下：

mysql> create table t1(
    -> id int not null auto_increment primary key,
    -> name varchar(50)
    -> );
Query OK, 0 rows affected (5.73 sec)

mysql> insert into t1(name) values('张三');
Query OK, 1 row affected (0.54 sec)

mysql> insert into t1(name) values('李四');
Query OK, 1 row affected (0.26 sec)

mysql> create index idx_name on t1(name);
Query OK, 0 rows affected (2.34 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看索引

show index from t1;
mysql> show index from t1\G;
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.08 sec)

ERROR:
No query specified
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

正题：

1.只复制表结构

create table t2 like t1;
1

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

使用select * from t2;
发现数据真的没复制过来
mysql> select * from t2;
Empty set (0.17 sec)

索引和表结构如何呢？复制过来没有？我们看一看
show index from t2;
desc t2;
show create table t2;

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

ERROR:
No query specified

mysql> desc t2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (1.44 sec)

mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

我们发现，索引和表结构确确实实复制过来了。

2.仅复制数据

create table t3 select * from t1;
1

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

查看表结构，发现主键索引并没有复制过来

mysql> show create table t3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
1
2
3
4
5
6
7
8
9
10

查看索引

mysql> show index from t3;
Empty set (0.05 sec)
1
2

查看表数据，发现已经复制成功

mysql> select * from t3;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

3.完整复制
步骤一：复制表结构create table t4 like t1;
步骤二：向新表插入数据insert into t4 select * from t1;

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

mysql> insert into t4 select * from t1;
Query OK, 2 rows affected (0.21 sec)
Records: 2  Duplicates: 0  Warnings: 0
1
2
3
4
5
6

查看建表语句

show create table t4;
1

查看索引

show index from t4;
1

查看数据

select * from t4;
1

结果如下：

mysql> show create table t4\G;
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show index from t4\G;
*************************** 1. row ***************************
        Table: t4
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: t4
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.03 sec)

ERROR:
No query specified

mysql> select * from t4;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

全部复制过来了

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/485731