MySQL 存儲過程動態建表插入：按日期分表的實戰方案

MySQL 存儲過程動態建表插入：按日期分表的實戰方案

業務場景：日志數據按天分表（表名 = 固定前綴 + 日期），存儲過程需判斷當日表是否存在，不存在則創建，隨後寫入數據。本文演示 Navicat 下的實現全流程。

核心邏輯

1. 入參：業務數據字段 + 目標日期（或隱式當天）。
2. 動態拼接表名：CONCAT('log_', DATE_FORMAT(NOW(), '%Y%m%d'))。
3. 檢查表是否存在（INFORMATION_SCHEMA.TABLES）。
4. 不存在 → PREPARE ... EXECUTE 動態創建表。
5. 動態拼接 INSERT 語句 → PREPARE ... EXECUTE 寫入數據。

關鍵代碼片段（Navicat 存儲過程編輯器）

1. 動態創建表

SET @table_name = CONCAT('log_', DATE_FORMAT(NOW(), '%Y%m%d'));
SET @sql_create = CONCAT('CREATE TABLE IF NOT EXISTS ', @table_name, ' (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  biz_id VARCHAR(64),
  content TEXT,
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
PREPARE stmt FROM @sql_create;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2. 動態插入數據

SET @sql_insert = CONCAT('INSERT INTO ', @table_name, ' (biz_id, content) VALUES (?, ?)');
PREPARE stmt FROM @sql_insert;
SET @biz_id = p_biz_id;  -- 入參賦值給用戶變量
SET @content = p_content;
EXECUTE stmt USING @biz_id, @content;
DEALLOCATE PREPARE stmt;

關鍵技術點解析

• PREPARE/EXECUTE：MySQL 支持的動態 SQL 機制，運行時解析字符串為語句。
• 用戶變量 (@var)：作為動態 SQL 的參數佔位符載體（USING @var），解決入參無法直接在動態 SQL 中引用的問題。
• CONCAT 拼接表名/列名/值：構建完全動態的 DDL/DML。
• IF NOT EXISTS：創建表時加保護，避免併發重複創建報錯。

避坑指南

1. SQL 注入風險：業務字段值必須通過 USING 綁定變量傳入，嚴禁直接拼接值字符串。
2. DDL 隱式提交：CREATE TABLE 會隱式提交當前事務，若存儲過程在事務中調用需注意。
3. 權限要求：調用者需具備 CREATE、INSERT、EXECUTE 權限。
4. 分表後續治理：需配合定時任務（Event/外部調度）實現：舊表歸檔/壓縮/刪除、分區表遷移、統計聚合等。

擴展思路

• MySQL 8.0+ 可考慮原生分區表（RANGE COLUMNS 按天分區），自動路由、無需動態 SQL、維護成本極低。
• 大規模日志場景建議：Kafka/Flume → ClickHouse/Doris/StarRocks（OLAP 引擎），而非 MySQL 分表。

動態建表是 MySQL 分表落地的經典模式，掌握 PREPARE + 用戶變量 組合拳，可應對絕大多數動態 DML/DDL 需求。