如何在MySQL中使用CREATE TABLE AS SELECT备份结构与数据

不能。CREATE TABLE AS SELECT 仅复制列定义和数据，不保留主键、索引、AUTO_INCREMENT、字符集、注释等元数据，因其本质是“按查询结果反推建表”，而非读取原表元数据；生产环境应改用 CREATE TABLE LIKE + INSERT 实现结构保真复制。

不能。CREATE TABLE AS SELECT 只能复制列定义和数据，不保留主键、索引、AUTO_INCREMENT、字符集、注释等任何结构元数据，不是真正的“备份”，仅适合临时快照。

为什么 CREATE TABLE AS SELECT 会丢主键和索引

它本质是“按查询结果反推建表”，不是读取原表的 information_schema 元数据。执行后 SHOW CREATE TABLE new_table 里看不到 PRIMARY KEY、KEY 或 AUTO_INCREMENT 字段。

• 原表 id INT AUTO_INCREMENT PRIMARY KEY → 新表变成 id INT，插入时可能报 Field 'id' doesn't have a default value
• UNIQUE KEY idx_email (email) 消失 → 后续插入重复邮箱不会报错，查重逻辑彻底失效
• 字符集可能降级为 latin1_swedish_ci 或 utf8mb4_0900_ai_ci，影响中文排序和大小写敏感行为
• 遇到 GENERATED COLUMN 或 CHECK 约束直接报错退出，不兼容

什么时候可以勉强用 CREATE TABLE AS SELECT

仅限约束无强依赖的临时场景，且你清楚后续要手动补救。

• 导出带时间戳的分析快照，如 logs_20260607，只读聚合用
• ETL 中间表，后续会清洗再写入目标表
• 测试环境快速造数据，字段数少、无业务校验逻辑
• 跨库克隆（同实例），但必须确认目标库无同名表，否则报 ERROR 1050 (42S01)

大表慎用：它是单事务执行，可能撑爆 innodb_log_file_size，或触发长事务告警；执行前务必加 WHERE 或 LIMIT 控制数据量。

想保留主键和索引？必须用 CREATE TABLE LIKE + INSERT

这是生产环境唯一稳妥的全量复制路径。

• CREATE TABLE new_t LIKE old_t 复制：NOT NULL、DEFAULT、AUTO_INCREMENT、所有索引（主键/唯一/全文）、字符集、排序规则、自增起始值
• 但它不复制：FOREIGN KEY、触发器、表注释、分区定义；外键需手动重建，注释得补 ALTER TABLE new_t COMMENT = 'xxx'
• INSERT INTO new_t SELECT * FROM old_t 要求字段顺序和类型严格一致；若不匹配，必须显式列出字段：INSERT INTO new_t (id, name) SELECT id, name FROM old_t
• 大表导入前建议调大会话参数：SET SESSION sort_buffer_size = 16M，避免因排序缓存不足导致慢或失败

最常被忽略的是验证环节：执行完 CREATE TABLE AS SELECT 后不检查 SHOW CREATE TABLE，就直接上线使用，结果主键缺失、索引丢失、字符集异常——这些问题在线上暴露时往往已造成数据不一致或性能雪崩。