最近测试环境要做一轮压测，测试同学提出一个需求：想复制一份真实数据出来，单独做回放测试，避免污染原始日志表。
这个需求听起来非常简单。组里新来的初级开发毫无悬念地接下了这个任务。
第二天，测试同学一大早就在群里发消息：
“这个日志查询接口怎么这么慢？昨天还好好的。”
过了一会儿，又来一条更离谱的消息：
“新增日志偶尔报主键冲突，能看下吗？”
紧接着又来：
“分页是不是有问题？第一页和第二页有重复数据，而且顺序老变。”
情景复现
业务团队有一张日志表：biz_operation_log。专门记录用户操作行为，比如：
- 登录
- 下单
- 支付
- 修改资料
想复制一份真实数据出来，单独做回放测试，避免污染原始日志表。做法就是创建一张备份表。
于是我新来的初级开发同学非常自信地敲下了这行 SQL：
CREATE TABLE biz_operation_log_bak SELECT * FROM biz_operation_log;在开发环境执行成功！
他还特意查了一下数据量：
SELECT COUNT(1) FROM biz_operation_log_bak;结果和原表一模一样，没问题，然后非常丝滑地提交到了测试环境。
那问题出在哪里？我们一步一步往下看。
首先，查看分页查询SQL的执行计划：
EXPLAIN SELECT *
FROM biz_operation_log_bak
WHERE biz_id = ?
ORDER BY create_time DESC
LIMIT 0,20;结果：
type: ALL
rows: 12000000全表扫描。1200 万日志记录，一页 20 条。
继续查原表结构：
SHOW INDEX FROM biz_operation_log;原表有索引：
PRIMARY KEY(id)
INDEX idx_biz_id(biz_id)
INDEX idx_create_time(create_time)再看备份表：
SHOW INDEX FROM biz_operation_log_bak;返回结果为空。
于是问题原因找到了，备份表没有原始表的主键、索引信息！
- 查询慢，是因为全表扫描了。
- 主键冲突，是因为备份表根本没有主键这个字段，ID只是普通字段。程序继续按老逻辑插入，某些场景下业务层自己生成了重复 ID。直接报错：Duplicate entry for key PRIMARY
- 分页混乱，是因为原表有主见ID，默认按主键物理顺序返回，相对稳定。但新表：没有主键、没有任何索引。MySQL返回顺序完全不保证。
什么原因？
CREATE TABLE new_table AS SELECT * FROM old_table;
CREATE TABLE new_table SELECT * FROM old_table;这种方式在 MySQL 中叫 CTAS（CREATE TABLE AS SELECT）。
它的特点是：
- 复制字段
- 复制数据
- 不复制索引
- 不复制主键
- 不复制唯一约束
- 不复制 AUTO_INCREMENT
- 不复制外键
- 不复制默认值（部分场景）
官方明确写了：CREATE TABLE ... SELECT does not automatically create any indexes
所以看到“索引不见了”，其实是这种语法的正常行为。
怎么解决？
正确的方式应该是先复制表结构，再把数据插进去。
第一步：复制表结构（包含索引）
CREATE TABLE new_table LIKE old_table;这个语句会：
- 复制字段
- 复制主键
- 复制普通索引
- 复制唯一索引
- 复制默认值
- 复制自增属性
- 不复制触发器
第二步：复制数据
INSERT INTO new_table SELECT * FROM old_table;第三步：检查
#检查表的DDL
SHOW CREATE TABLE new_table;
SHOW CREATE TABLE old_table;
#检查数据量
SELECT COUNT(1) FROM new_table;
SELECT COUNT(1) FROM old_table;
#两张表的 checksum 相同，通常意味着数据完全一致：每一行数据都一样，每个字段值都一样
CHECKSUM TABLE old_table;
CHECKSUM TABLE new_table;表复制的技术方案
在日常开发和数据库运维中，经常会遇到以下场景：
- 创建测试表
- 数据备份
- 表结构迁移
- 历史数据归档
- 大表重构
- 分库分表前的数据迁移
- 灰度发布新表
这时候，“复制表结构和数据”就是一个高频操作。很多人只会一句：
CREATE TABLE new_table AS SELECT * FROM old_table;但实际上，这只是最基础的一种方式。不同方式在 索引、主键、自增、约束、性能、锁表风险 上差异非常大。
接下来，我将详细讲述不同场景下的表复制与解决方案。
只复制表结构
只复制表结构，怎么做？
首先，回忆一下表结构（DDL）有哪些？
- 字段
- 字段类型
- 默认值
- NOT NULL
- 主键
- 唯一索引
- 普通索引
- 注释
- 自增属性
- 存储引擎
- 字符集
强烈推荐的复制表结构方式：CREATE TABLE LIKE
CREATE TABLE new_table LIKE old_table;相当于把原表的建表语句提取出来，然后再执行CREATE TABLE。最终得到的是一个和原始表一模一样的表。但是数据和配置是全新的。
谨慎使用"CREATE TABLE AS SELECT（CTAS）"的方式，除非你知道自己在做什么。
-- 其中where条件后面的“ 1=0 ”表示不复制数据’
CREATE TABLE new_table AS SELECT * FROM original_table WHERE 1=0;
复制表结构中的某些字段，不需要复制索引、约束等？
CREATE TABLE new_tableAS SELECT 需要的字段... FROM original_table WHERE 1=0; #where条件恒不成立，不复制数据注意这种方式：
- 不复制索引
- 不复制主键
- 不复制唯一约束
- 不复制 AUTO_INCREMENT
- 不复制外键
- 不复制默认值（部分场景）
复制表结构中的某些字段，如果这个字段有索引则也需要一并复制
不能直接实现
方案一：手动指定表结构。
方案二：用CREATE TABLE SELECT创建基础表，再Alter表的索引、约束等。
注意：复制时需确保新表与原表的字符集（如utf8、utf8mb4）、排序规则（如utf8_general_ci）一致，否则可能出现中文乱码、排序异常。若需修改，可在创建新表时指定（如`DEFAULT CHARSET=utf8mb4`）。
复制表结构+数据
那我既要复制表结构，又要复制表数据，应该怎么做呢？正确的做法是什么？
先复制结构，再复制数据
第一步：复制结构
CREATE TABLE new_table LIKE old_table;第二步：插入数据
INSERT INTO new_table SELECT * FROM old_table;谨慎使用"CREATE TABLE AS SELECT（CTAS）"的方式，除非你知道自己在做什么。
数据量太大时，INSERT INTO ... SELECT ... 在迁移表数据时，可能会产生什么问题？
- 执行很慢：因为有索引，每插入一行都要维护 B+Tree。
- 锁表问题
- 主从延迟：大事务会让 binlog 一次性写入大量数据，从库可能延迟几十分钟。
- 磁盘 IO 打满：连续全表扫描 + 连续写入（大量顺序读+大量索引写），可能直接把数据库拖慢。
解决方案1：分批次（在Java层面管理id的范围）
第一批：INSERT INTO new_table SELECT * FROM old_table WHERE id > 0 AND id < 100000;
第二批：INSERT INTO new_table SELECT * FROM old_table WHERE id > 100000 AND id < 200000;
......//Java实现伪代码：
long lastId = 0;
int batchSize = 100000;while (true) {int rows = jdbc.execute("""INSERT INTO new_tableSELECT *FROM old_tableWHERE id > ?ORDER BY idLIMIT ?""", lastId, batchSize);if (rows == 0) break;lastId += batchSize;
}解决方案2：先建无索引表，导完数据后再单独建索引
先建无索引表：CREATE TABLE new_table AS SELECT * FROM old_table;
导完数据后再单独建索引:ALTER TABLE ADD INDEX ...
其他注意点：
- 原表中的NULL值、默认值会被正常复制，但需注意`CREATE TABLE ... AS SELECT`创建的表，默认值不会保留，需手动添加（如`DEFAULT CURRENT_TIMESTAMP`）。
- 若新表已存在数据，插入数据时需避免主键重复，可通过`INSERT IGNORE`（忽略重复数据）、`REPLACE INTO`（替换重复数据）或`ON DUPLICATE KEY UPDATE`（更新重复数据）处理
- 示例：
-- 忽略重复主键的数据
INSERT IGNORE INTO 新表名 SELECT * FROM 原表名;-- 替换重复主键的数据（删除原重复数据，插入新数据）
REPLACE INTO 新表名 SELECT * FROM 原表名;-- 重复主键时，更新指定字段
INSERT INTO 新表名 SELECT * FROM 原表名 ON DUPLICATE KEY UPDATE order_no = VALUES(order_no);增量同步
前面我们已经创建了用于回放测试的备份表，但是现在测试给我们说，要把原始日志表的变动实时同步到备份表，更方便他做测试。
所以，我们现在的目标就是：把原始表的变动（DML操作），全部同步到备份表上。
此外，为了通用性的表达，有如下定义：原始表、旧表（old_table），新表（new_table）
Trigger触发器
主要思路：在某一时刻起，先为原始表创建DML操作的触发器，再把原始表的数据批量插入到新表。等待回归测试结束，再删除原表上的触发器。
主要步骤
- 创建新表
- 为old_table创建触发器（包括：INSERT、UPDATE、DELETE）
- 数据迁移（分批次）
- 校验数据一致性
- 删除触发器、新表
执行步骤：
1) 创建新表
CREATE TABLE new_table LIKE old_table;2) 为old_table创建触发器（包括：INSERT、UPDATE、DELETE）
DELIMITER $$
CREATE TRIGGER trg_old_table_insert
AFTER INSERT ON old_table
FOR EACH ROW
BEGININSERT INTO new_tableVALUES (NEW.id,NEW.col1,NEW.col2,NEW.col3-- 所有字段);
END$$
DELIMITER ;DELIMITER $$
CREATE TRIGGER trg_old_table_update
AFTER UPDATE ON old_table
FOR EACH ROW
BEGINUPDATE new_tableSETcol1 = NEW.col1,col2 = NEW.col2,col3 = NEW.col3WHERE id = NEW.id;
END$$
DELIMITER ;DELIMITER $$
CREATE TRIGGER trg_old_table_delete
AFTER DELETE ON old_table
FOR EACH ROW
BEGINDELETE FROM new_tableWHERE id = OLD.id;
END$$
DELIMITER ;3) 数据迁移（分批次）
一次性迁移：INSERT INTO new_table SELECT * FROM old_table;
全量分批迁移：
INSERT INTO new_table SELECT * FROM old_tableWHERE id > lastId AND id < maxId;4) 校验数据一致性
#校验数量是否一致
SELECT COUNT(1) FROM old_table;
SELECT COUNT(1) FROM new_table;#两张表的 checksum 相同，通常意味着数据完全一致：每一行数据都一样，每个字段值都一样
CHECKSUM TABLE old_table;
CHECKSUM TABLE new_table;5) 删除触发器、新表
DROP TRIGGER IF EXISTS trg_old_table_insert;
DROP TRIGGER IF EXISTS trg_old_table_update;
DROP TRIGGER IF EXISTS trg_old_table_delete;增量的DML操作数据如何同步到新表的？全量数据在分批复制，触发器在同步增量，两条线并行运行。触发器保证增量数据同步到新表
在“触发器刚创建”和“复制开始”之间，会不会有空档？
根本不存在这个窗口期，也就是说不会存在有数据丢失的情况。
虽然不会有数据丢失，但可能有“重复同步”。
重复同步会不会有问题？不会
情况1：复制时读到的是新值。这种情况是没问题的
情况2：复制时插入旧数据，触发器再 update 一次，最终结果还是正确的（幂等覆盖）
为什么用 AFTER 触发器？如果使用BEFORE，可能会由于事务回滚导致了脏数据移动到了new_table。AFTER表示old_table数据已经执行插入，则可以进行同步到new_table
技术方案评价
- 优势
- 简单易理解
- 利用数据库原生能力就可以实现，无需引入额外的组件
- 劣势
- 影响old_table的DML操作，例如影响写性能
- 触发器逻辑复杂难维护，如果是单表还好，如果涉及到多表同步更新
- 失败难排查，如果触发器内执行报错了，数据不可恢复的丢失
- 总结：适合中小规模在线迁移
Binlog解析
这个方案的主要思路是：伪装成MySQL的一个从库，解析binlog，回放主库上目标表的DML操作，从而实现表数据的增量同步。
开源的解决方案有：Canal、Debezium。这里不再展开。
总结
在复制表结构和数据时，旧表的DML，也需要增量同步到新表上，有两类解决方案：
- 基于触发器实现
- 优势：简单易理解，不需要引入额外组件
- 劣势：侵入数据库，操作复杂，容易出错且不方便debug
- 适用场景：少量数据迁移，单表数据同步，对性能要求不高
- 基于binlog实现
- 优势：无侵入，性能好，可以拓展性（可以整合ES、Kafka等组件）
- 劣势：引入新组件，实现比较复杂
- 适用场景：性能要求高，适合数据同步、迁移和数据平台等场景。
总结
本文主要介绍了再复制表结构时的三个重要问题：
1) 复制表结构时尽量首选这种方案：CREATE TABLE new_table LIKE old_table;
2) 复制数据时有2种核心的操作方式：
方式一：INSERT INTO new_table SELECT * FROM old_table;
方式二：INSERT INTO new_table SELECT * FROM old_table WHERE id > lastId AND id < maxId;3) 增量同步的解决方案有Trigger和Binlog解析
本文结束。