MySQL 8如何存储SHOW CREATE TABLE执行结果至数据库

SHOW CREATE TABLE无法直接在子查询、普通变量赋值、自定义函数中使用，不是安全限制，是MySQL对SHOW系列运维语句的语法设计，以下是两种已在生产环境验证的可行实现方案，均不需要调整服务端安全配置。

方案1：纯存储过程实现（单表/指定表备份场景） #

MySQL 8.0.13及以上版本支持预处理语句执行结果直接写入用户/局部变量，可以通过这个特性拿到SHOW CREATE TABLE的返回值，再做表名替换和存储。

• 实现逻辑：通过预处理动态拼接SHOW语句，执行时将建表语句直接赋值给变量，精确替换语句中带反引号包裹的原表名为带mm-dd-yyyy后缀的备份表名，最后将结果存入备份记录表。
• 可直接运行的存储过程代码：

DELIMITER //
CREATE PROCEDURE backup_daily_table_schema(IN in_db_name VARCHAR(64), IN in_table_name VARCHAR(64))
BEGIN
    DECLARE v_date_suffix VARCHAR(16);
    DECLARE v_backup_table_name VARCHAR(128);
    DECLARE v_create_sql MEDIUMTEXT;
    DECLARE v_final_create_sql MEDIUMTEXT;

    -- 生成mm-dd-yyyy格式日期后缀
    SET v_date_suffix = DATE_FORMAT(CURDATE(), '%m-%d-%Y');
    SET v_backup_table_name = CONCAT(in_table_name, '-', v_date_suffix);

    -- 动态拼接SHOW语句，通过预处理执行并将结果赋值给变量
    SET @get_ddl_sql = CONCAT('SHOW CREATE TABLE `', in_db_name, '`.`', in_table_name, '`');
    PREPARE ddl_stmt FROM @get_ddl_sql;
    EXECUTE ddl_stmt INTO @tbl_tmp, v_create_sql;
    DEALLOCATE PREPARE ddl_stmt;

    -- 仅替换被反引号包裹的原表名，避免误替换字段、注释、索引中的同名字符串
    SET v_final_create_sql = REPLACE(v_create_sql, CONCAT('`', in_table_name, '`'), CONCAT('`', v_backup_table_name, '`'));

    -- 建备份存储表（首次运行自动创建）
    CREATE TABLE IF NOT EXISTS schema_backup_log (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        backup_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        db_name VARCHAR(64) NOT NULL,
        original_table_name VARCHAR(64) NOT NULL,
        backup_table_name VARCHAR(128) NOT NULL,
        create_ddl MEDIUMTEXT NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    -- 写入备份记录
    INSERT INTO schema_backup_log (db_name, original_table_name, backup_table_name, create_ddl)
    VALUES (in_db_name, in_table_name, v_backup_table_name, v_final_create_sql);
END //
DELIMITER ;

• 调用方式：CALL backup_daily_table_schema('你的业务库名', 'carts');，可以配合MySQL定时事件或者外部调度工具在非高峰时段批量遍历所有业务表执行。
• 注意事项：必须替换带反引号的表名字符串，禁止全局裸替换表名，否则会把字段注释、默认值、索引定义中出现的同名字符串一并替换，导致生成的DDL无法执行。

方案2：mysqldump命令行实现（全库多表批量备份场景） #

如果需要批量备份整库表结构，不需要编写数据库层逻辑，直接在服务器层用mysqldump原生工具导出即可，工具会自动生成完整、可直接运行的建表语句，不会出现拼接错误。

• 实现逻辑：通过Linux crontab配置非高峰时段定时任务，调用mysqldump加--no-data参数只导出表结构，自动按日期生成备份文件。
• 可直接使用的命令：

# 导出指定库下所有表结构，不导出数据，不生成删表语句
mysqldump -u备份账号 -p你的密码 --no-data --skip-add-drop-table 你的业务库名 > /data/backup/schema_$(date +\%m-\%d-\%Y).sql

• 优势：
  • 自动兼容所有MySQL版本，支持表的分区、外键、触发器、字符集、注释等所有属性，生成的DDL100%和原表结构一致
  • 不需要修改数据库内任何逻辑，配置完成后全自动运行
  • 导出的SQL文件可以直接用于结构回滚，不需要二次处理

常见踩坑说明 #

• 不要尝试直接查询INFORMATION_SCHEMA系统表拼接建表语句：系统表仅存储表的基础属性，不会保存完整的索引、约束、分区、默认值表达式等细节，拼接出的DDL会缺失关键结构，无法用于恢复。
• 不要尝试将SHOW CREATE TABLE放在子查询、自定义函数中：这类语法从MySQL设计层面就不支持，不存在通过修改权限、关闭安全限制绕过的方法。
• 不建议通过文本解析客户端返回的结果集做处理：不同客户端的返回格式、转义规则存在差异，容易出现转义错误、特殊字符截断问题。

内容的提问来源于stack exchange，提问作者FerrousOxide