MySQL下使用SQL命令进行表结构与数据复制实践_mysql 全表复制sql

【1】表结构复制

第一种方式

create table 表名 like [数据库.]表名;

-- 如果是同一个数据库下的表的复制，不需要添加[数据库.];
-- 不会复制数据;
1
2
3
4

示例如下：

-- 原表创建语句：
CREATE TABLE `tb_lesson` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `joinTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;

-- 删除原表：
drop table tb_lesson;
--并从备份数据库复制
create table tb_lesson like db_exam2.tb_lesson;

-- 查看复制的新表语句;
CREATE TABLE `tb_lesson` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(60) DEFAULT NULL,
  `JoinTime` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 字段属性未变
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

---

第二种方式

create table new_table
as
select * from [数据库名.]old_table;

-- 第二种方式在mysql下可能会丢失列属性，如主键，自增等。
1
2
3
4
5

示例如下：

-- 第二种方式复制的新表如下：
CREATE TABLE `tb_lesson` (
  `ID` int(11) NOT NULL DEFAULT '0',
  `Name` varchar(60) DEFAULT NULL,
  `JoinTime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 改变了字段属性等。
-- 同时复制了数据;
-- 如果侧重表结构不建议使用这种方式。
1
2
3
4
5
6
7
8
9
10

【2】表数据复制

又被称为蠕虫复制，语法格式如下：

insert into Table2(field1,field2,...) select value1,value2,... from Table1;

// 如果不指定列，则如下(一定保证两个表列一致):

insert into new_table select * from [数据库.]old_table
1
2
3
4
5

指定列字段实例：

insert  into tb_sys_news (category_name,category_id,user_name,user_id,content,field9,create_time)
select 'XX特色',1,'XXXXX',2,content,website,publish_time  from sheetj1 
1
2
3

需要注意的是，下面方式不适用于MySQL：

SELECT vale1, value2 into Table2 from Table1
1

该方式常常用于MySQL触发器、存储过程或函数中为变量赋值。

【3】不同数据库之间表复制

两个数据库之间表复制过程如下：

复制db_exam2数据库的表tb_lesson到当前数据库的表tb_lesson(表不出存在时需要创建)。

-- 删除已经存在的表;
drop table if EXISTS tb_lesson;

--复制并创建新表
create table tb_lesson like db_exam2.tb_lesson;

-- 复制数据
insert into tb_lesson select * from db_exam2.tb_lesson;
1
2
3
4
5
6
7
8

这里是从另外一个数据库(两个数据库在同一个主机)复制表和数据到当前数据库，省略了当前数据库名字。

也可以不省略数据库名字如下：

从data_analysis复制表和数据到data_analysis_zh。此处不管当前连接对象是哪个数据库，都可以正确执行。

drop table if EXISTS data_analysis_zh.store_card_2;

create table data_analysis_zh.store_card_2 like data_analysis.pos_sale;

INSERT into data_analysis_zh.store_card_2 
select * from data_analysis.pos_sale where data_analysis.pos_sale.number_store_card >0;

select * from data_analysis_zh.store_card_2 ;
1
2
3
4
5
6
7
8

效果等同如下(打开data_analysis连接)：

drop table if EXISTS data_analysis_zh.store_card_2;

create table data_analysis_zh.store_card_2 like pos_sale;

INSERT into data_analysis_zh.store_card_2 
select * from pos_sale where number_store_card >0;

select * from data_analysis_zh.store_card_2 ;
1
2
3
4
5
6
7
8

效果等同如下(打开data_analysis_zh连接)：

drop table if EXISTS store_card_2;

create table store_card_2 like data_analysis.pos_sale;

INSERT into store_card_2 
select * from data_analysis.pos_sale where [data_analysis.pos_sale.]number_store_card >0;

select * from store_card_2 ;
1
2
3
4
5
6
7
8

参考博文：MySQL - 数据备份与还原(导出导入)。