学编程 ❀(๑╯◡╰๑)❀ 就上soxunxi.cn!这里有CMS,CSS,NET,PHP,Linux,HTML,JAVA,MySQL,Python等教程.
当前位置: MySQL > MySQL复制表构造和表数据【MySQL教程】,mysql,表结构,表数据

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复制表构造和表数据的细致内容,更多请关注ki4网别的相干文章!

「梦想一旦被付诸行动,就会变得神圣,如果觉得我的文章对您有用,请帮助本站成长」

分享到:
赞(0) 打赏

支付宝扫一扫打赏

微信扫一扫打赏

上一篇:

下一篇:

相关推荐

0 条评论关于"MySQL复制表构造和表数据【MySQL教程】,mysql,表结构,表数据"

最新评论

    暂无留言哦~~

博客简介

看古风美女插画Cos小姐姐,素材合集图集打包下载:炫龙网,好看二次元插画应有尽有,唯美小姐姐等你来。

友情链接

他们同样是一群网虫,却不是每天泡在网上游走在淘宝和网游之间、刷着本来就快要透支的信用卡。他们或许没有踏出国门一步,但同学却不局限在一国一校,而是遍及全球!申请交换友链

服务热线:
 

 QQ在线交流

 旺旺在线