如何在执行CREATE TABLE AS时自动保存建表SQL到指定记录表

首先明确你之前没找到对应触发器，大概率是混淆了DML触发器和DDL触发器：大部分主流数据库都支持DDL级别的触发规则，可直接捕获CREATE TABLE事件，以下是不同场景的落地实现：

方案1：数据库原生DDL触发器（推荐，无需修改业务代码） #

前置准备：先建统一的审计记录表 #

-- 通用结构，可根据你用的数据库调整字段类型
CREATE TABLE create_table_audit (
  table_name VARCHAR(128) NOT NULL PRIMARY KEY,
  query TEXT NOT NULL,
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

不同数据库的触发器实现 #

• MySQL 8.0+ 实现

DELIMITER //
CREATE TRIGGER after_create_table_audit
AFTER CREATE ON SCHEMA
FOR EACH ROW
BEGIN
  -- 过滤非建表事件，只写入有效的表记录
  IF EVENT_OBJECT_TABLE IS NOT NULL THEN
    INSERT INTO create_table_audit (table_name, query)
    -- EVENT_OBJECT_TABLE取新建的表名，CURRENT_STATEMENT取完整执行的SQL
    VALUES (EVENT_OBJECT_TABLE, REPLACE(CURRENT_STATEMENT(), '\n', ' '));
  END IF;
END //
DELIMITER ;

配置后所有CREATE TABLE操作包括CREATE TABLE AS都会自动写入审计表，换行符会自动替换为空格，和你需要的存储效果完全一致。

• PostgreSQL 实现

-- 第一步：建触发函数
CREATE OR REPLACE FUNCTION log_create_table_func()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
    IF obj.object_type = 'table' THEN
      INSERT INTO create_table_audit (table_name, query)
      VALUES (obj.object_identity, REPLACE(current_query(), '\n', ' '));
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 第二步：建事件触发器，只捕获CREATE TABLE相关操作
CREATE EVENT TRIGGER log_create_table_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
EXECUTE FUNCTION log_create_table_func();

方案2：封装存储过程（兼容所有数据库，无权限建触发器时可用） #

如果你没有数据库的触发器配置权限，可以把建表逻辑封装为存储过程，所有建表操作统一调用存储过程实现：

-- MySQL示例，其他数据库语法类似
DELIMITER //
CREATE PROCEDURE create_table_with_log(IN create_sql TEXT)
BEGIN
  -- 执行传入的建表语句
  SET @exec_sql = create_sql;
  PREPARE stmt FROM @exec_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  -- 解析SQL提取表名，可根据你实际的SQL格式调整解析规则
  SET @table_name = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(create_sql, 'CREATE TABLE ', -1), ' ', 1));
  -- 写入审计表
  INSERT INTO create_table_audit (table_name, query)
  VALUES (@table_name, REPLACE(create_sql, '\n', ' '));
END //
DELIMITER ;

调用方式：

CALL create_table_with_log('CREATE TABLE tbl AS SELECT * FROM tbl_info;');

方案3：审计日志解析（无侵入，不修改现有代码） #

如果不允许修改现有建表逻辑，也不能加触发器/存储过程，可以开启数据库的全量SQL审计日志，定时运行脚本解析日志中匹配CREATE TABLE的语句，提取表名和完整SQL后写入你的记录表即可，不需要调整任何现有业务逻辑。

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