复制表 mysql数据库最稳妥的三种做法，新手也能一次搞定
复制表 mysql数据库的核心结论是：没有一条命令能通吃所有场景，最稳妥的组合是 CREATE TABLE ... LIKE 复制结构，再用 INSERT INTO ... SELECT 复制数据，两步完成，既保留索引又不怕数据错位。如果你只是要快速备份一份数据做测试，CREATE TABLE AS SELECT 一条语句更省事，但会丢掉索引和默认值，下面我把每种方式的适用场景、操作命令和坑都拆开讲清楚。
mysql复制表到另一个数据库怎么操作：先分清“结构”和“数据”
复制表这件事，看着简单，但很多人一上手就懵，原因在于没搞明白复制表有两个层面：结构（字段、索引、主键、默认值）和数据（行记录），你需要的到底是哪个,直接决定了用哪条命令。
只要表结构，不要数据
场景很常见：你想建一张和线上表一样结构的空表，用来做数据归档或者分表，这时候用 CREATE TABLE ... LIKE 是最佳选择。
CREATE TABLE 新表名 LIKE 原表名;
这条命令会把原表的所有字段定义、索引、主键、自增属性原封不动地复制过来，但不会复制任何数据行，执行完你就得到一张空壳表,结构一模一样。
如果你连表结构都只想复制一部分，比如只要字段不要索引，那可以用 SHOW CREATE TABLE 先拿到建表语句,手动改一改再执行：
SHOW CREATE TABLE 原表名\G
把输出里的 CREATE TABLE 语句复制出来，删掉索引相关行，改个表名,重新执行即可。
结构数据一起复制
这是需求量最大的场景，多数情况下，你希望得到一张包含全部数据的完整副本,有两种主流做法：
两步走（推荐，保留索引）
CREATE TABLE 新表名 LIKE 原表名; INSERT INTO 新表名 SELECT FROM 原表名;
第一步复制结构，第二步把数据灌进去。这种方式最稳，索引、触发器（如果有）都能保留，而且数据是按字段顺序插入的,不容易出错。
一条语句（简单，但丢索引）
CREATE TABLE 新表名 AS SELECT FROM 原表名;
这就是业内常说的 CTAS 写法，一行搞定，但新表不会继承任何索引、主键、自增属性，只有字段名和数据类型一致，如果你只是临时拉一份数据做分析，不在乎查询性能，这个最方便，如果是要做生产级替换,还是老老实实用两步走。
mysql复制表结构与数据时，字段类型和自增属性最容易踩坑
很多新手复制完表，发现数据对不上，或者插入报错,问题往往出在细节上。
自增字段的处理
原表有 AUTO_INCREMENT 的话，用 CTAS 复制的表会丢失自增属性，插入数据时如果不指定 id，会报错或者插入 NULL 失败，用 CREATE TABLE ... LIKE 就没这个问题,自增属性会完整保留。
大字段和特殊类型
TEXT、BLOB、JSON 这类字段在复制时一般没问题，但要注意：CTAS 复制大字段时，如果原表字段有默认值函数（CURRENT_TIMESTAMP），新表可能不会保留，这时候建议复制完结构后，用 SHOW CREATE TABLE 检查一下建表语句是否完整。
临时表复制
如果是复制临时表，注意 CREATE TABLE ... LIKE 不会复制临时表属性，你需要手动指定，不过实际工作中，临时表复制场景很少,知道即可。
复制表 mysql数据库效率太低？数据量大时这样提速
数据量小的时候，怎么复制都无所谓，秒完，但当你面对一张几千万行的大表，直接 INSERT INTO ... SELECT 可能会把磁盘 IO 打满，甚至锁住线上业务，行业共识认为，大表复制要在业务低峰期操作，并且分批处理。
分批复制，控制节奏
不要一次性把几千万行灌进去,分批次更安全：
INSERT INTO 新表名 SELECT FROM 原表名 WHERE id BETWEEN 1 AND 100000; INSERT INTO 新表名 SELECT FROM 原表名 WHERE id BETWEEN 100001 AND 200000;
每次插入十万行左右，观察系统负载，再继续下一批，这样即使中途出错,也不需要从头再来。
关闭日志和唯一键检查
在批量插入时,临时关闭一些安全机制能显著提速：
SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;
插入完成后记得重新打开，如果是 InnoDB 引擎，可以临时把 autocommit 设为 0，手动控制事务提交,减少磁盘刷写频率。
用 mysqldump 做跨服务器复制
如果是复制到另一台服务器，用 SQL 语句就不好使了，得上工具。mysqldump 是最常用的方案：
mysqldump -u用户名 -p 数据库名 原表名 > 表名.sql
然后在目标服务器上导入：
mysql -u用户名 -p 数据库名 < 表名.sql
mysqldump 支持 --no-data 参数只导出结构，也支持 --where 条件导出部分数据，灵活度很高，但要注意，mysqldump 在旧版本中默认会锁表，如果线上业务不能停，建议加 --single-transaction 参数（InnoDB 引擎下有效）。
复制表 mysql数据库出现权限不够或锁表问题怎么解决
权限不足
复制表需要 SELECT、CREATE、INSERT 权限，缺一不可，如果你在授权账户下操作报权限错误,检查一下：
SHOW GRANTS FOR '用户名'@'主机';
确认三条权限是否齐全，业内专家指出，很多复现的权限问题其实出在 SELECT 权限上，因为某些运维账号只有 DML 权限，没有查询权限,导致复制命令直接失败。
锁表问题
CREATE TABLE ... LIKE 在 DDL 期间会获取元数据锁（MDL），如果此时有长事务在跑，你的复制操作会一直等待,解决办法是：
- 先查一下当前是否有长时间未提交的事务：
SHOW PROCESSLIST; - 如果等不及，可以设置锁等待超时时间：
SET innodb_lock_wait_timeout = 5;
INSERT INTO ... SELECT 在 InnoDB 引擎下默认会对源表加共享锁，如果源表正在频繁写入，可能互相阻塞，这时候可以考虑用 SELECT ... INTO OUTFILE 配合 LOAD DATA INFILE 绕过锁，但操作复杂度会上升,一般业务场景用不到。
复制表 mysql数据库常见问题：锁表与权限
问：复制表时提示“Table ‘xxx’ already exists”，但明明表不存在？
答：检查是否在同一个数据库下有同名视图或者临时表。CREATE TABLE 对视图名和表名是同一命名空间，如果之前建过同名的视图，也会报这个错误，用 SHOW TABLES LIKE 'xxx' 确认一下。
问：复制的表数据量和原表对不上，少了行怎么办？
答：先确认复制过程中是否有报错被忽略，分批插入时，检查每批次影响的行数，加起来是否等于源表总数，如果源表在复制过程中有数据写入，也会导致不一致，最稳妥的做法是在低峰期操作，或者用 SELECT COUNT() 对比验证。
问：mysql复制表到另一个数据库，为什么新表查询特别慢？
答：大概率是索引丢了，用 CTAS 方式复制的表没有索引，全表扫描自然慢，用 CREATE TABLE ... LIKE 复制结构的话，索引会保留，如果已经用 CTAS 复制完了，可以手动补索引：CREATE INDEX 索引名 ON 新表名(字段); 复制后的表查询性能问题,多数情况下都是索引缺失导致的。
复制表这件事，核心思路就一句话：先想清楚要结构还是要数据，再选择对应的命令，小表随意，大表分批，生产环境勤备份，记住两步走的方式,你就已经超过了绝大多数还在用一条语句硬扛的人。
首发原创文章，作者：王坚，如若转载，请注明出处：https://idctop.com/article/558939.html
