萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL DATETIME 轉 TIMESTAMP 遷移方案:零停機、數據不丟失的實操步驟

業務需將 DATETIME 欄位改為 INT(11) 儲存 Unix 時間戳 (TIMESTAMP 數值型),以節省儲存空間並統一應用層時間處理。本文提供零停機、可回滾的標準遷移流程。

遷移腳本 (標準 6 步法)

-- 1. 新增臨時欄位
ALTER TABLE erp_diff_order ADD COLUMN created_date_tmp INT(11) DEFAULT 0;

-- 2. 數據轉換寫入 (DATETIME → Unix Timestamp)
UPDATE erp_diff_order SET created_date_tmp = UNIX_TIMESTAMP(created_date);

-- 3. 刪除原欄位
ALTER TABLE erp_diff_order DROP COLUMN created_date;

-- 4. 新增目標欄位 (同名)
ALTER TABLE erp_diff_order ADD COLUMN created_date INT(11) DEFAULT 0;

-- 5. 回寫數據
UPDATE erp_diff_order SET created_date = created_date_tmp;

-- 6. 清理臨時欄位
ALTER TABLE erp_diff_order DROP COLUMN created_date_tmp;

關鍵點解析

  • 分步驟執行:每步驗證無誤再執行下一步,任一步驟失敗可回滾。
  • 避免直接 MODIFY:ALTER TABLE ... MODIFY created_date INT 會嘗試原地轉換,鎖表時間不可控且不可逆。
  • 大表優化:配合 pt-online-schema-change 或 gh-ost 線上遷移,避免長時間鎖表。
  • 時區一致性:確認 UNIX_TIMESTAMP() 使用的會話時區與應用預期一致 (建議統一 UTC)。
  • 應用層同步:部署新版應用代碼 (讀寫 INT 時間戳) 需與資料庫遷移協同,建議藍綠部署或特性開關。

驗證查詢

-- 抽樣對比
SELECT created_date, FROM_UNIXTIME(created_date) AS converted, created_date_tmp
FROM erp_diff_order LIMIT 10;

-- 全量一致性檢查
SELECT COUNT(*) FROM erp_diff_order WHERE created_date != created_date_tmp;
-- 應為 0

擴展:反向遷移 (INT → DATETIME)

ALTER TABLE t ADD COLUMN dt_new DATETIME DEFAULT NULL;
UPDATE t SET dt_new = FROM_UNIXTIME(ts_col);
ALTER TABLE t DROP COLUMN ts_col;
ALTER TABLE t CHANGE dt_new ts_col DATETIME DEFAULT NULL;

此方案適用於所有數值型與日期時間型互轉場景,核心思想:新增 → 回填 → 切換 → 清理,保證數據安全與業務連續性。

copyright © 萬盛學電腦網 all rights reserved