比尔云BierYun--阿里云最新优惠活动
阿里云优惠码丨阿里云代金券

MySQL 复制表结构和表数据

MySQL 复制表结构和表数据

1、前言

在功能开发完毕,在本地或者测试环境进行测试时,经常会遇到这种情况:有专门的测试数据,测试过程会涉及到修改表中的数据,经常不能一次测试成功,所以,每次执行测试后,原来表中的数据其实已经被修改了,下一次测试,就需要将数据恢复。

我一般的做法是:先创建一个副本表,比如测试使用的user表,我在测试前创建副本表user_bak,每次测试后,将user表清空,然后将副本表user_bak的数据导入到user表中。

上面的操作是对一个table做备份,如果涉及到的table太多,可以创建database的副本。

接下来我将对此处的表结构复制以及表数据复制进行阐述,并非数据库的复制原理!!!!

下面是staff表的表结构

1
2
3
4
5
6
7
8
9
10
create table staff (
    id int not null auto_increment comment '自增id',
    name char(20) not null comment '用户姓名',
    dep char(20) not null comment '所属部门',
    gender tinyint not null default 1 comment '性别:1男; 2女',
    addr char(30) not null comment '地址',
    primary key(id),
    index idx_1 (name, dep),
    index idx_2 (name, gender)
) engine=innodb default charset=utf8mb4 comment '员工表';

 

2、具体方式

  2.1、执行旧表的创建SQL来创建表

如果原始表已经存在,那么可以使用命令查看该表的创建语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show create table staff\G
*************************** 1. row ***************************
       Table: staff
Create TableCREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `namechar(20) NOT NULL COMMENT '用户姓名',
  `dep` char(20) NOT NULL COMMENT '所属部门',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别:1男; 2女',
  `addr` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`name`,`dep`),
  KEY `idx_2` (`name`,`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
1 row in set (0.01 sec)

可以看到,上面show creat table xx的命令执行结果中,Create Table的值就是创建表的语句,此时可以直接复制创建表的SQL,然后重新执行一次就行了。

当数据表中有数据的时候,看到的创建staff表的sql就会稍有不同。比如,我在staff中添加了两条记录:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> insert into staff values (null'李明''RD', 1, '北京');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff values (null'张三''PM', 0, '上海');
Query OK, 1 row affected (0.00 sec)
mysql> select from staff;
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
rows in set (0.00 sec)

此时在执行show create table命令:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show create table staff\G
*************************** 1. row ***************************
       Table: staff
Create TableCREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `namechar(20) NOT NULL COMMENT '用户姓名',
  `dep` char(20) NOT NULL COMMENT '所属部门',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别:1男; 2女',
  `addr` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`name`,`dep`),
  KEY `idx_2` (`name`,`gender`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
1 row in set (0.00 sec)

注意,上面结果中的倒数第二行

ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT=’员工表’

因为staff表的id是自增的,且已经有了2条记录,所以下一次插入数据的自增id应该为3,这个信息,也会出现在表的创建sql中。

 

  2.2、使用like创建新表(仅包含表结构)

使用like根据已有的表来创建新表,特点如下:

1、方便,不需要查看原表的表结构定义信息;

2、创建的新表中,表结构定义、完整性约束,都与原表保持一致。

3、创建的新表是一个空表,全新的表,没有数据。

用法如下:

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
mysql> select from staff;  #旧表中已有2条数据
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
rows in set (0.00 sec)
mysql> create table staff_bak_1 like staff;  # 直接使用like,前面指定新表名,后面指定旧表(参考的表)
Query OK, 0 rows affected (0.02 sec)
mysql> show create table staff_bak_1\G
*************************** 1. row ***************************
       Table: staff_bak_1
Create TableCREATE TABLE `staff_bak_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `namechar(20) NOT NULL COMMENT '用户姓名',
  `dep` char(20) NOT NULL COMMENT '所属部门',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别:1男; 2女',
  `addr` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`name`,`dep`),
  KEY `idx_2` (`name`,`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表'  # 注意没有AUTO_INCREMENT=3
1 row in set (0.00 sec)
mysql> select from staff_bak_1; # 没有包含旧表的数据
Empty set (0.00 sec)

 

  2.3、使用as来创建新表(包含数据)

使用as来创建新表,有一下特点:

1、可以有选择性的决定新表包含哪些字段;

2、创建的新表中,会包含旧表的数据;

3、创建的新表不会包含旧表的完整性约束(比如主键、索引等),仅包含最基础的表结构定义。

用法如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> create table staff_bak_2 as select from staff;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select from staff_bak_2;
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
rows in set (0.00 sec)
mysql> show create table staff_bak_2\G
*************************** 1. row ***************************
       Table: staff_bak_2
Create TableCREATE TABLE `staff_bak_2` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT '自增id',
  `namechar(20) CHARACTER SET utf8mb4 NOT NULL COMMENT '用户姓名',
  `dep` char(20) CHARACTER SET utf8mb4 NOT NULL COMMENT '所属部门',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别:1男; 2女',
  `addr` char(30) CHARACTER SET utf8mb4 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

利用as创建表的时候没有保留完整性约束,其实这个仔细想一下也能想明白。因为使用as创建表的时候,可以指定新表包含哪些字段呀,如果你创建新表时,忽略了几个字段,这样的话即使保留了完整约束,保存数据是也不能满足完整性约束。

比如,staff表有一个索引idx1,由name和dep字段组成;但是我创建的新表中,没有name和dep字段(只选择了其他字段),那么新表中保存idx1也没有必要,对吧。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> --  只选择id、gender、addr作为新表的字段,那么name和dep组成的索引就没必要存在了
mysql> create table staff_bak_3 as (select id, gender, addr from staff);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> show create table staff_bak_3\G
*************************** 1. row ***************************
       Table: staff_bak_3
Create TableCREATE TABLE `staff_bak_3` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT '自增id',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别:1男; 2女',
  `addr` char(30) CHARACTER SET utf8mb4 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select from staff_bak_3;
+----+--------+--------+
| id | gender | addr   |
+----+--------+--------+
|  1 |      1 | 北京   |
|  2 |      0 | 上海   |
+----+--------+--------+
rows in set (0.00 sec)

 

  2.4、使用like+insert+select创建原表的副本(推荐)

使用like创建新表,虽然保留了旧表的各种表结构定义以及完整性约束,但是如何将旧表的数据导入到新表中呢?

最极端的方式:写一个程序,先将旧表数据读出来,然后写入到新表中,这个方式我就不尝试了。

有一个比较简单的命令:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select from staff; #原表数据
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
rows in set (0.00 sec)
mysql> select from staff_bak_1; # 使用like创建的表,与原表相同的表结构和完整性约束(自增除外)
Empty set (0.00 sec)
mysql> insert into staff_bak_1 select from staff;  # 将staff表的所有记录的所有字段值都插入副本表中
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select from staff_bak_1;
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
rows in set (0.00 sec)

 

其实这条SQL语句,是知道两个表的表结构和完整性约束相同,所以,可以直接select *。

1
insert into staff_bak_1 select from staff;

 

如果两个表结构不相同,其实也是可以这个方式的,比如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show create table demo\G
*************************** 1. row ***************************
       Table: demo
Create TableCREATE TABLE `demo` (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `_name` char(20) DEFAULT NULL,
  `_gender` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
# 只将staff表中的id和name字段组成的数据记录插入到demo表中,对应_id和_name字段
mysql> insert into demo (_id, _name) select id,name from staff;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select from demo;
+-----+--------+---------+
| _id | _name  | _gender |
+-----+--------+---------+
|   1 | 李明   |       1 |
|   2 | 张三   |       1 |
+-----+--------+---------+
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
# staff_bak_5的字段名与staff表并不相同,但是字段数量、相同顺序字段的类型相同,所以可以直接插入
mysql> show create table staff_bak_5\G
*************************** 1. row ***************************
       Table: staff_bak_5
Create TableCREATE TABLE `staff_bak_5` (
  `_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `_name` char(20) NOT NULL COMMENT '用户姓名',
  `_dep` char(20) NOT NULL COMMENT '所属部门',
  `_gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别:1男; 2女',
  `_addr` char(30) NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `idx_1` (`_name`,`_dep`),
  KEY `idx_2` (`_name`,`_gender`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
1 row in set (0.00 sec)
mysql> insert into staff_bak_5 select from staff;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select from staff_bak_5;
+-----+--------+------+---------+--------+
| _id | _name  | _dep | _gender | _addr  |
+-----+--------+------+---------+--------+
|   1 | 李明   | RD   |       1 | 北京   |
|   2 | 张三   | PM   |       0 | 上海   |
+-----+--------+------+---------+--------+
rows in set (0.00 sec)

原文地址https://www.cnblogs.com/-beyond/p/11890598.html

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

强烈推荐

高性能SSD云服务器ECS抗攻击,高可用云数据库RDS