将一个 MySQL 表的内容复制到同一数据库的另一个 MySQL 表中
- Devashish Dixit
2
你的桌子是什么材质做的？ - Abdul Manaf
InnoDB 引擎. - Devashish Dixit
3个回答
mydb.mytb的数据库表，你想要创建一个名为mydb.mytbcopy的副本。
我有五种方法来完成这个复制过程。
第一种方法：
在mysql客户端中运行以下命令。
USE mydb
CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb;
方法二
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb | mysql ${MYSQL_CONN} -Dtest
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"
方法三
DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dtest < ${DUMPFILE}
rm -f ${DUMPFILE}
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"
方法 #4
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb
方法五
DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dmydb < ${DUMPFILE}
rm -f ${DUMPFILE}
分析
- 方法一在步骤上最简单，但需要将4000万行数据推送到一个事务中。这对InnoDB存储引擎来说是最具挑战性的。
- 对于其他方法，mysqldump会将4000万行数据分批发送，每批包含数千行数据。
- 方法二和方法三会将表导出到测试数据库。在测试数据库中创建表后，将其重命名并移动到原始数据库。
- 方法四和方法五使用sed对来自mysqldump的流进行重命名，同时回显INSERT命令。
- 方法二和方法四使用管道而不是输出文件。
- 方法三和方法五使用输出文件进行后续重新加载。
如果您想将mydb.mytb复制到已经存在的表mydb.mytbcopy，并且这两个表具有相同的结构：
方法 #6
INSERT INTO mytbcopy SELECT * FROM mytb;
像#方法1一样，#方法6将有一个包含4000万行的单一交易
方法7
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} -t mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb
- RolandoMySQLDBA
这种方法通常来说，速度与使用文件系统命令（cp、rsync）复制文件一样快。
虽然在某些情况下，这可能适用于 MySQL ＜ 5.6 （以一种巧妙的方式）进行还原，但对于表复制将不起作用。在这些情况下，可以使用 SQL 完成：
CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;
这将尽可能快地执行InnoDB的Handler_read_rnd_next和Handler_write，每一行执行一次。如果您使用此方法，请确保至少暂时禁用耐久性选项，并且具有较大的缓冲池和事务日志。在这些情况下，它可以减少导入时间，但绝对无法完全放入内存，因此预计需要很长时间。此外，您正试图在单个事务中导入4000万行数据，这可能会导致问题。
在这种第二种情况下，我的实际建议是使用类似pt-archiver的工具，它将执行类似于我刚才提到的操作，但会以“块”的方式进行，避免了事务开销（可能不会更快，但如果出现故障，它不会尝试回滚整个表格，从而花费很长时间）。根据您提到的数据大小，这可能是最佳选择。
一种最后的选择是使用CSV（或TSV）格式进行导出和导入，结合使用SELECT INTO OUTFILE/mysqldump和LOAD DATA/mysqlimport。在某些旧版本的mysql中，如果需要并发性，这是一个非常常见的选项，因为使用sql会创建更大的锁（如果正确执行则不再成立）。由于mysqldump/import只以串行方式工作，我建议您研究可并行化的选项，对于大型表格非常有用。 无论如何，尽量避免多个SQL语句，因为如果执行许多不同的查询（需要逐个执行、解析和优化），它将成为最重要的瓶颈。*MyISAM结构无法以热方式复制，但使用
FTWRL将它们暂时同步到磁盘非常容易。
- jynus
create table 你的表名 select * from 旧模式的表名;
- Shahzad Hussain