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复制表构造和表数据的具体内容,更多请关注百分百源码网其它相关文章!