MySQL复制表构造和表数据-百分百源码网

MySQL复制表构造和表数据

发布时间：09/01 来源：未知浏览：关键词：

在功能开发结束，在当地或者测试环境进行测试时，时常会碰到这种状况：有专门的测试数据，测试历程会波及到修改表中的数据，时常不克不及一次测试成功，所以，每次施行测试后，本来表中的数据其实已经被修改了，下一次测试，就需要将数据恢复。

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)

举荐《mysql视频教程》

以上就是MySQL复制表构造和表数据的细致内容，更多请关注百分百源码网其它相干文章！

打赏