1, 前言
在功能开发完毕, 在本地或者测试环境进行测试时, 经常会遇到这种情况: 有专门的测试数据, 测试过程会涉及到修改表中的数据, 经常不能一次测试成功, 所以, 每次执行测试后, 原来表中的数据其实已经被修改了, 下一次测试, 就需要将数据恢复.
我一般的做法是: 先创建一个副本表, 比如测试使用的 user 表, 我在测试前创建副本表 user_bak, 每次测试后, 将 user 表清空, 然后将副本表 user_bak 的数据导入到 user 表中.
上面的操作是对一个 table 做备份, 如果涉及到的 table 太多, 可以创建 database 的副本.
接下来我将对此处的表结构复制以及表数据复制进行阐述, 并非数据库的复制原理!!!!
下面是 staff 表的表结构
- 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 来创建表
如果原始表已经存在, 那么可以使用命令查看该表的创建语句:
- MySQL> show create table staff\G
- *************************** 1. row ***************************
- Table: staff
- Create Table: CREATE TABLE `staff` (
- `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 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
- 1 row in set (0.01 sec)
可以看到, 上面 show creat table xx 的命令执行结果中, Create Table 的值就是创建表的语句, 此时可以直接复制创建表的 SQL, 然后重新执行一次就行了.
当数据表中有数据的时候, 看到的创建 staff 表的 sql 就会稍有不同. 比如, 我在 staff 中添加了两条记录:
- 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 | 上海 |
- +----+--------+-----+--------+--------+
- 2 rows in set (0.00 sec)
此时在执行 show create table 命令:
- MySQL> show create table staff\G
- *************************** 1. row ***************************
- Table: staff
- Create Table: CREATE TABLE `staff` (
- `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)
注意, 上面结果中的倒数第二行
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
因为 staff 表的 id 是自增的, 且已经有了 2 条记录, 所以下一次插入数据的自增 id 应该为 3, 这个信息, 也会出现在表的创建 sql 中.
2.2, 使用 like 创建新表 (仅包含表结构)
使用 like 根据已有的表来创建新表, 特点如下:
1, 方便, 不需要查看原表的表结构定义信息;
2, 创建的新表中, 表结构定义, 完整性约束, 都与原表保持一致.
3, 创建的新表是一个空表, 全新的表, 没有数据.
用法如下:
- MySQL> select * from staff; #旧表中已有 2 条数据
- +----+--------+-----+--------+--------+
- | id | name | dep | gender | addr |
- +----+--------+-----+--------+--------+
| 1 | 李明 | RD | 1 | 北京 |
| 2 | 张三 | PM | 0 | 上海 |
- +----+--------+-----+--------+--------+
- 2 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 Table: CREATE TABLE `staff_bak_1` (
- `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 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, 创建的新表不会包含旧表的完整性约束 (比如主键, 索引等), 仅包含最基础的表结构定义.
用法如下:
- 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 | 上海 |
- +----+--------+-----+--------+--------+
- 2 rows in set (0.00 sec)
- MySQL> show create table staff_bak_2\G
- *************************** 1. row ***************************
- Table: staff_bak_2
- Create Table: CREATE TABLE `staff_bak_2` (
- `id` int(11) NOT NULL DEFAULT '0' COMMENT '自增 id',
- `name` char(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 也没有必要, 对吧.
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 Table: CREATE 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 | 上海 |
- +----+--------+--------+
- 2 rows in set (0.00 sec)
2.4, 使用 like+insert+select 创建原表的副本 (推荐)
使用 like 创建新表, 虽然保留了旧表的各种表结构定义以及完整性约束, 但是如何将旧表的数据导入到新表中呢?
最极端的方式: 写一个程序, 先将旧表数据读出来, 然后写入到新表中, 这个方式我就不尝试了.
有一个比较简单的命令:
- MySQL> select * from staff; #原表数据
- +----+--------+-----+--------+--------+
- | id | name | dep | gender | addr |
- +----+--------+-----+--------+--------+
| 1 | 李明 | RD | 1 | 北京 |
| 2 | 张三 | PM | 0 | 上海 |
- +----+--------+-----+--------+--------+
- 2 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 | 上海 |
- +----+--------+-----+--------+--------+
- 2 rows in set (0.00 sec)
其实这条 SQL 语句, 是知道两个表的表结构和完整性约束相同, 所以, 可以直接 select *.
insert into staff_bak_1 select * from staff;
如果两个表结构不相同, 其实也是可以这个方式的, 比如:
- MySQL> show create table demo\G
- *************************** 1. row ***************************
- Table: demo
- Create Table: CREATE 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 |
- +-----+--------+---------+
- 2 rows in set (0.00 sec)
这是两个表的字段数量不相同的情况, 此时需要手动指定列名, 否则就会报错.
另外, 如果两个表的字段数量, 以及相同顺序的字段类型相同, 如果是全部字段复制, 即使字段名不同, 也可以直接复制:
- # staff_bak_5 的字段名与 staff 表并不相同, 但是字段数量, 相同顺序字段的类型相同, 所以可以直接插入
- MySQL> show create table staff_bak_5\G
- *************************** 1. row ***************************
- Table: staff_bak_5
- Create Table: CREATE 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 | 上海 |
- +-----+--------+------+---------+--------+
- 2 rows in set (0.00 sec)
来源: https://www.cnblogs.com/-beyond/p/11890598.html