当前位置：Gxlcms > mysql > MySQL复制表结构和表数据

MySQL复制表结构和表数据

时间：2021-07-01 10:21:17 帮助过：26人阅读

1、前言

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

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

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

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

下面是staff表的表结构

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

2、具体方式　

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

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

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

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

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

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

此时在执行show create table命令：

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

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

  1. ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'

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

2.2、使用like创建新表（仅包含表结构）

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

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

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

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

用法如下：

  1. mysql> select * from staff;　　#旧表中已有2条数据
  2. +----+--------+-----+--------+--------+
  3. | id | name   | dep | gender | addr   |
  4. +----+--------+-----+--------+--------+
  5. |  1 | 李明   | RD  |      1 | 北京   |
  6. |  2 | 张三   | PM  |      0 | 上海   |
  7. +----+--------+-----+--------+--------+
  8. 2 rows in set (0.00 sec)
  9.  
  10. mysql> create table staff_bak_1 like staff;  # 直接使用like，前面指定新表名，后面指定旧表（参考的表）
  11. Query OK, 0 rows affected (0.02 sec)
  12.  
  13. mysql> show create table staff_bak_1\G
  14. *************************** 1. row ***************************
  15.        Table: staff_bak_1
  16. Create Table: CREATE TABLE `staff_bak_1` (
  17.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  18.   `name` char(20) NOT NULL COMMENT '用户姓名',
  19.   `dep` char(20) NOT NULL COMMENT '所属部门',
  20.   `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别：1男; 2女',
  21.   `addr` char(30) NOT NULL,
  22.   PRIMARY KEY (`id`),
  23.   KEY `idx_1` (`name`,`dep`),
  24.   KEY `idx_2` (`name`,`gender`)
  25. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表'  # 注意没有AUTO_INCREMENT=3
  26. 1 row in set (0.00 sec)
  27.  
  28. mysql> select * from staff_bak_1; # 没有包含旧表的数据
  29. Empty set (0.00 sec)

2.3、使用as来创建新表（包含数据）

使用as来创建新表，有一下特点：

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

2、创建的新表中，会包含旧表的数据；

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

用法如下：

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

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

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

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

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

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

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

有一个比较简单的命令：

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

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

  1. insert into staff_bak_1 select * from staff;

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

  1. mysql> show create table demo\G
  2. *************************** 1. row ***************************
  3.        Table: demo
  4. Create Table: CREATE TABLE `demo` (
  5.   `_id` int(11) NOT NULL AUTO_INCREMENT,
  6.   `_name` char(20) DEFAULT NULL,
  7.   `_gender` tinyint(4) DEFAULT '1',
  8.   PRIMARY KEY (`_id`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  10. 1 row in set (0.00 sec)
  11.  
  12. # 只将staff表中的id和name字段组成的数据记录插入到demo表中，对应_id和_name字段
  13. mysql> insert into demo (_id, _name) select id,name from staff;
  14. Query OK, 2 rows affected (0.00 sec)
  15. Records: 2  Duplicates: 0  Warnings: 0
  16.  
  17. mysql> select * from demo;
  18. +-----+--------+---------+
  19. | _id | _name  | _gender |
  20. +-----+--------+---------+
  21. |   1 | 李明   |       1 |
  22. |   2 | 张三   |       1 |
  23. +-----+--------+---------+
  24. 2 rows in set (0.00 sec)

这是两个表的字段数量不相同的情况，此时需要手动指定列名，否则就会报错。

另外，如果两个表的字段数量，以及相同顺序的字段类型相同，如果是全部字段复制，即使字段名不同，也可以直接复制：

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

推荐 《mysql视频教程》

以上就是MySQL复制表结构和表数据的详细内容，更多请关注Gxlcms其它相关文章！

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！