MySql数据库表快速复制
表索引操作
这里之所以写索引的操作主要原因在于：在导入数据前删除索引能大大提升导入速度。
DDL语句获取现有索引
show create table t_question_bak;获取所有索引
获取单一索引
SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX,NON_UNIQUE,INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1;或者
show index from t_question_bak;创建表索引
基本语法
在已有的表中建立索引的语法：
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名](字段名1 [(长度)] [ASC|DESC]) [USING 索引方法];或者
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法];例子：
ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);或者
create index index_id on sorc4(s_id);获取建表普通索引SQL
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' ADD INDEX ',i.INDEX_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.NON_UNIQUE=1;或者
SELECT CONCAT('CREATE INDEX ',i.INDEX_NAME,' ON ',i.TABLE_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.NON_UNIQUE=1;获取表全文索引SQL
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' ADD FULLTEXT INDEX ',i.INDEX_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.INDEX_TYPE='FULLTEXT';或者
SELECT CONCAT('CREATE FULLTEXT INDEX ',i.INDEX_NAME,' ON ',i.TABLE_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.INDEX_TYPE='FULLTEXT';获取建表唯一索引SQL
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' ADD UNIQUE INDEX ',i.INDEX_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.NON_UNIQUE=0;或者
SELECT CONCAT('CREATE UNIQUE INDEX ',i.INDEX_NAME,' ON ',i.TABLE_NAME,'(',i.COLUMN_NAME,');')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY'
AND i.SEQ_IN_INDEX=1
AND i.NON_UNIQUE=0;删除表索引
导入数据前删除表索引能大大提升数据的导入效率
ALTER TABLE 表名 DROP INDEX 索引名;或
DROP INDEX 索引名 ON 表名;生成删除索引的SQL(排除主键索引)
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,';')
FROM INFORMATION_SCHEMA.STATISTICS i
WHERE TABLE_SCHEMA = 'xhkj_ques_0923'
AND TABLE_NAME='t_question_bak'
AND i.INDEX_NAME <> 'PRIMARY';复制运行即可
一、最简单的方法
create table t2 as select * from t1;适用表数据量小的情况
二、SQL形式
mysqldump逻辑导出
mysqldump -h127.0.0.1 -P3306 -uroot -p123456 --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=off test01 t2 --where='c1>4' --result-file=/mysql/backup/t2.sql–add-locks=0 表示在输出的的文件结果里，不增加”lock tbales t2 write” –no-create-info 不需要导出表结构 –single-transaction 不需要对t2进行加锁，而是使用start transaction with consistent snapshop的方法 -set-gtid-purged=off 不导出gtid相关信息 –where=’c1>4’ 过滤条件 –result-file 指定导出文件的路径
三、CSV文件形式
创建t3表结构
create table t3 like t2;导出需要导出t2的数据
# 全部导出
SELECT * FROM t2 INTO OUTFILE '/var/lib/mysql-files/t2.csv' FIELDS TERMINATED BY ',';
# 部分导出
SELECT * FROM t2 WHERE c1>4 INTO OUTFILE '/var/lib/mysql-files/t2.csv' FIELDS TERMINATED BY ',';将csv文件导入到t3
LOAD DATA INFILE '/var/lib/mysql-files/t2.csv' INTO TABLE t3 FIELDS TERMINATED BY ',';四、物理拷贝的方法
1、创建t3表结构
create table t3 like t2;2、执行
alter table t3 discard tablespace;此时t3.ibd会被删除
3、执行
flush table t2 for export;此时test01目录下会生成一个t2.cfg文件
4、拷贝t2.ibd，t2.cfg，注意权限
cp t2.cfg t3.cfg
cp t2.ibd t3.ibd5、解除表锁定
unlock tables;这时候t2.cfg会被删除
6、执行
alter table t3 import tablespace;将t3.ibd作为t3新的表空间，数据和t2也是相同的。
总结
- 对于大表，物理拷贝方法最快，对于误删表的情况，比较有用，但是也存在一定的弊端：
- 必须是全表拷贝；
- 必须能连服务器；
- 源表和目标表都必须是innodb存储引擎。
- mysqldump方法可以生成insert的语句，可以加过滤条件拷贝部分数据，但是不能使用join这种比较复杂的条件。
select .... into outfile的方法最灵活，但是缺点是每次只能导出一张表。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2019-09-24，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。