您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

MySQL 動態分表實戰：以存儲過程實現按日期自動分表

編輯：Oracle 資料庫基礎

業務背景與挑戰

在 CRM 系統數據同步專案中，需要定期將 MySQL（源庫）的數據導入 Oracle（目標庫）。初期數據增長緩慢，但預計未來會大幅增長。由於 MySQL 資料庫非我方搭建，且主表缺乏唯一標識字段，導致在增量同步時面臨「同步期間有新數據寫入」的並發問題，極易造成數據不一致。開發人員暫時無法協助，只能臨時學習 MySQL 存儲過程來解決。

解決方案：按日期動態建表 + 存儲過程路由寫入

核心思路：系統每天自動生成第二天的分表（如 readinfo20070726），寫入時根據當前日期動態決定目標表，避免單表過大並天然實現時間維度的數據隔離。

1. 分表結構定義

所有分表結構一致，採用 MyISAM 引擎、utf8 字符集：

CREATE TABLE readinfo20070726 (
  eid INT(11),
  guid VARCHAR(36),
  ip VARCHAR(20),
  `date` VARCHAR(20),
  bookname VARCHAR(60),
  version INT(11),
  isvip INT(11),
  vipname VARCHAR(80)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

同樣建立 readinfo20070727、readinfo20070728 等後續日期表。

2. 動態寫入存儲過程

利用 PREPARE/EXECUTE 實現動態 SQL，根據 CURDATE() 拼接目標表名：

CREATE PROCEDURE sp_readinfo(
  IN eid INT,
  IN guid VARCHAR(36),
  IN ip VARCHAR(50)
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
  SET @tbl = CONCAT('readinfo', CURDATE() + 0);
  SET @sql = CONCAT(
    'INSERT INTO ', @tbl,
    ' (eid, guid, ip, `date`) VALUES (',
    eid, ',', QUOTE(guid), ',', QUOTE(ip), ', NOW())'
  );
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;

注意：此處使用 QUOTE() 防止 SQL 注入，並簡化了原文中的多變量拼接邏輯。

擴展示例：批量建表與動態查詢

例一：循環建立同結構多表（k1k, k2k...）

DELIMITER //
CREATE PROCEDURE ppp(IN max_num INT)
BEGIN
  DECLARE k INT DEFAULT 1;
  WHILE k < max_num DO
    SET @tname = CONCAT('k', k, 'k');
    SET @ddl = CONCAT('CREATE TABLE ', @tname, ' (id INT, name VARCHAR(255))');
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET k = k + 1;
  END WHILE;
END//
DELIMITER ;
CALL ppp(6);

例二：以參數為表名進行動態查詢

CREATE PROCEDURE pp(IN tname VARCHAR(255))
BEGIN
  SET @sql = CONCAT('SELECT COUNT(*) FROM ', tname);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;