如何在子查询中使用SHOW CREATE TABLE获取建表脚本并存入数据库

报错原因 #

你编写的语句无法执行，是因为SHOW CREATE TABLE属于MySQL的展示类命令，返回结果无法直接作为子查询被SELECT语句调用，属于语法限制。

可行的实现方案 #

• 方案1：查询information_schema元数据表（最适合赋值变量场景） #

  直接读取系统元数据表就能拿到建表语句，不需要调用SHOW类命令，支持直接赋值给变量：

  -- 直接查询建表语句
  SELECT CREATE_TABLE 
  FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA = '你的数据库名称' 
    AND TABLE_NAME = '目标表名';
  
  -- 赋值给自定义变量
  SELECT CREATE_TABLE INTO @create_table_sql
  FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA = DATABASE() -- 直接匹配当前操作的数据库
    AND TABLE_NAME = 'tablename';
  

  拿到@create_table_sql变量后就可以直接执行存储到数据库等后续操作。

• 方案2：直接执行SHOW CREATE TABLE命令 #

  如果是在客户端工具、命令行或者脚本中直接获取结果，可直接执行原生命令：

  SHOW CREATE TABLE tablename;
  

  输出结果包含两个字段，第二个Create Table字段的值就是完整的建表脚本。如果是在shell等脚本环境中调用，可以直接过滤输出结果：

  # 示例：shell环境下提取建表语句赋值给变量
  create_sql=$(mysql -u你的用户名 -p你的密码 目标数据库 -se "SHOW CREATE TABLE tablename" | awk '{print $2}')
  

• 方案3：用mysqldump工具批量导出 #

  如果需要导出多个表/全库的建表脚本，用官方的mysqldump工具效率更高：

  # 仅导出建表语句，不导出表数据
  mysqldump -u你的用户名 -p你的密码 -d 目标数据库 表名 > 建表脚本存储路径.sql
  

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