MySql数据库表快速复制

表索引操作

这里之所以写索引的操作主要原因在于:在导入数据前删除索引能大大提升导入速度。

DDL语句获取现有索引

1
show create table t_question_bak;

获取所有索引

获取单一索引

1
2
3
4
5
6
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;

或者

1
show index from t_question_bak;

创建表索引

基本语法

在已有的表中建立索引的语法:

1
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名](字段名1 [(长度)] [ASC|DESC]) [USING 索引方法];

或者

1
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法];

例子:

1
ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);

或者

1
create index index_id on sorc4(s_id);

获取建表普通索引SQL

1
2
3
4
5
6
7
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;

或者

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
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';

或者

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
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;

或者

1
2
3
4
5
6
7
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;

删除表索引

导入数据前删除表索引能大大提升数据的导入效率

1
ALTER TABLE 表名 DROP INDEX 索引名;

1
DROP INDEX 索引名 ON 表名;

生成删除索引的SQL(排除主键索引)

1
2
3
4
5
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';

复制运行即可

一、最简单的方法

1
create table t2 as select * from t1;

适用表数据量小的情况

二、SQL形式

mysqldump逻辑导出

1
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表结构

1
create table t3 like t2;

导出需要导出t2的数据

1
2
3
4
5
# 全部导出
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

1
LOAD DATA INFILE '/var/lib/mysql-files/t2.csv' INTO TABLE t3 FIELDS TERMINATED BY ',';

四、物理拷贝的方法

1、创建t3表结构

1
create table t3 like t2;

2、执行

1
alter table t3 discard tablespace;

此时t3.ibd会被删除

3、执行

1
flush table t2 for export;

此时test01目录下会生成一个t2.cfg文件

4、拷贝t2.ibd,t2.cfg,注意权限

1
2
cp t2.cfg t3.cfg
cp t2.ibd t3.ibd

5、解除表锁定

1
unlock tables;

这时候t2.cfg会被删除

6、执行

1
alter table t3 import tablespace;

将t3.ibd作为t3新的表空间,数据和t2也是相同的。

总结

  1. 对于大表,物理拷贝方法最快,对于误删表的情况,比较有用,但是也存在一定的弊端:

    • 必须是全表拷贝;
    • 必须能连服务器;
    • 源表和目标表都必须是innodb存储引擎。
  2. mysqldump方法可以生成insert的语句,可以加过滤条件拷贝部分数据,但是不能使用join这种比较复杂的条件。

  3. select .... into outfile的方法最灵活,但是缺点是每次只能导出一张表。