最近测试环境要做一轮压测，测试同学提出一个需求：想复制一份真实数据出来，单独做回放测试，避免污染原始日志表。
这个需求听起来非常简单。组里新来的初级开发毫无悬念地接下了这个任务。
第二天，测试同学一大早就在群里发消息：
“这个日志查询接口怎么这么慢？昨天还好好的。”
过了一会儿，又来一条更离谱的消息：
“新增日志偶尔报主键冲突，能看下吗？”
紧接着又来：
“分页是不是有问题？第一页和第二页有重复数据，而且顺序老变。”
业务团队有一张日志表：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;返回结果为空。
于是问题原因找到了，备份表没有原始表的主键、索引信息！
CREATE TABLE new_table AS SELECT * FROM old_table;
CREATE TABLE new_table SELECT * FROM old_table;这种方式在 MySQL 中叫 CTAS（CREATE TABLE AS SELECT）。
它的特点是：
官方明确写了：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）有哪些？
强烈推荐的复制表结构方式：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_table
AS SELECT 需要的字段... FROM original_table WHERE 1=0; #where条件恒不成立，不复制数据注意这种方式：
复制表结构中的某些字段，如果这个字段有索引则也需要一并复制
不能直接实现
方案一：手动指定表结构。
方案二：用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 ... 在迁移表数据时，可能会产生什么问题？
解决方案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_table
SELECT *
FROM old_table
WHERE id > ?
ORDER BY id
LIMIT ?
""", lastId, batchSize);
if (rows == 0) break;
lastId += batchSize;
}解决方案2：先建无索引表，导完数据后再单独建索引
先建无索引表：CREATE TABLE new_table AS SELECT * FROM old_table;
导完数据后再单独建索引:ALTER TABLE ADD INDEX ...
其他注意点：
-- 忽略重复主键的数据
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）
主要思路：在某一时刻起，先为原始表创建DML操作的触发器，再把原始表的数据批量插入到新表。等待回归测试结束，再删除原表上的触发器。
主要步骤
执行步骤：
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
BEGIN
INSERT INTO new_table
VALUES (
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
BEGIN
UPDATE new_table
SET
col1 = NEW.col1,
col2 = NEW.col2,
col3 = NEW.col3
WHERE id = NEW.id;
END$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER trg_old_table_delete
AFTER DELETE ON old_table
FOR EACH ROW
BEGIN
DELETE FROM new_table
WHERE id = OLD.id;
END$$
DELIMITER ;3) 数据迁移（分批次）
一次性迁移：INSERT INTO new_table SELECT * FROM old_table;
全量分批迁移：
INSERT INTO new_table SELECT * FROM old_table
WHERE 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
技术方案评价
这个方案的主要思路是：伪装成MySQL的一个从库，解析binlog，回放主库上目标表的DML操作，从而实现表数据的增量同步。
开源的解决方案有：Canal、Debezium。这里不再展开。
在复制表结构和数据时，旧表的DML，也需要增量同步到新表上，有两类解决方案：
本文主要介绍了再复制表结构时的三个重要问题：
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解析
本文结束。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。