在後端開發、資料庫改版或歸檔歷史資料時，經常需要執行 MySQL 複製資料表（Table） 來建立測試環境或備份重要資料。若要完整複製一張資料表的「欄位結構、主鍵（Primary Key）、索引（Index）」以及「所有資料列」，最安全且不會遺失索引的標準做法是分兩步驟執行 CREATE TABLE ... LIKE 與 INSERT INTO ... SELECT。
⚡ 快速跳轉（依實務情境選擇）：
- 需要馬上複製整張 Table（含結構＋索引＋資料）：直接複製下方兩行標準 SQL 指令。
- 需要把資料表複製到另一個資料庫（Cross-DB）：在表名前加上資料庫名稱
db_name.table_name。- 只想複製部分欄位或依月份條件拆表備份：使用
INSERT INTO ... SELECT ... WHERE指定條件。- 為什麼不建議用
CREATE TABLE AS SELECT？：檢視兩種建表語法對 Primary Key 與 Index 的影響差異。- 刪完過期資料後硬碟空間沒變小？：查看大表分批搬移與
OPTIMIZE TABLE釋放空間三步驟。
一、 完整複製資料表（結構＋資料）：兩步驟標準語法
假設我們要將現有的資料表 old_table 完整複製出一張一模一樣的新資料表 new_table，只需要依序執行以下兩行 SQL 指令：
-- 步驟 1：建立結構完全相同的新資料表（完整保留 Primary Key、Index 與 AUTO_INCREMENT 設定）
CREATE TABLE `new_table` LIKE `old_table`;
-- 步驟 2：將原資料表的所有資料列寫入新資料表
INSERT INTO `new_table` SELECT * FROM `old_table`;語法拆解與反引號（Backticks）防錯提醒
- 為什麼要分兩步寫？
- 第一步
CREATE TABLE \new_table` LIKE `old_table`;會讀取old_table的完整結構定義，建立一張包含所有欄位型別、主鍵（PRIMARY KEY）、唯一索引（UNIQUE KEY）、一般索引與AUTO_INCREMENT` 屬性的空資料表。 - 第二步
INSERT INTO \new_table` SELECT * FROM `old_table`;則是將SELECT *查詢出來的所有資料列，批次寫入剛建好的new_table` 中。
- 第一步
- ⚠️ 常見報錯地雷：表名請用「反引號
`」而非「單引號'」：- 在 MySQL 語法中，資料表名稱與欄位名稱若要加上符號包裹（避免與
order、group、user等保留字衝突），必須使用鍵盤左上角的 反引號（`，Backtick），或是直接不加符號。 - 千萬不能用單引號
'table_name'！單引號在 SQL 中代表「純文字字串（String Literal）」，若寫成CREATE TABLE 'new_table' LIKE 'old_table';會直接觸發ERROR 1064 (42000)語法錯誤。
- 在 MySQL 語法中，資料表名稱與欄位名稱若要加上符號包裹（避免與
二、 跨資料庫複製資料表（Cross-Database Clone）
在實務開發中，我們常需要把「正式環境資料庫（例如 prod_db）」裡某張資料表，直接複製到同一台 MySQL 伺服器上的「測試資料庫（例如 test_db）」進行除錯，只要在資料表名稱前方加上 資料庫名稱.資料表名稱 即可跨庫複製：
-- 步驟 1：在 test_db 建立與 prod_db.users 結構與索引完全相同的空資料表
CREATE TABLE `test_db`.`users` LIKE `prod_db`.`users`;
-- 步驟 2：將 prod_db.users 的資料跨資料庫寫入 test_db.users
INSERT INTO `test_db`.`users` SELECT * FROM `prod_db`.`users`;三、 指定欄位或依時間條件拆表搬移（INSERT INTO ... SELECT ... WHERE） {#insert-select-where}
當你不需要複製整張表的所有資料，而是想「只篩選特定月份／條件的資料」或「只挑幾個指定欄位」複製到備份表時，可以在 SELECT 後方加上欄位清單與 WHERE 條件。
情境 1：依月份或時間區間將資料表分類匯出（拆表歸檔）
假設單一資料表 table_data 累積了數百萬筆紀錄導致查詢變慢，我們想依月份將 1 月與 2 月的資料分別拆表寫入 table_data_2026_01 與 table_data_2026_02：
-- 先建立好對應結構的月份歸檔表
CREATE TABLE `table_data_2026_01` LIKE `table_data`;
CREATE TABLE `table_data_2026_02` LIKE `table_data`;
-- 將 1 月份資料篩選寫入 table_data_2026_01
INSERT INTO `table_data_2026_01`
SELECT * FROM `table_data`
WHERE `created_at` >= '2026-01-01 00:00:00'
AND `created_at` < '2026-02-01 00:00:00';情境 2：新舊資料表欄位數量不同時（指定欄位複製）
如果目標資料表與來源資料表的欄位結構不完全相同，直接使用 SELECT * 會引發 ERROR 1136 (21S01) 錯誤。此時應明確指定要寫入與讀取的欄位名稱：
-- 明確指定目標表與來源表的對應欄位（順序與型別需一致）
INSERT INTO `user_Simple_backup` (`user_id`, `email`, `registered_at`)
SELECT `id`, `email`, `created_at`
FROM `users`
WHERE `status` = 1;（若想深入複習基礎資料操作與欄位對應規則，可參考站內另一篇教學：MySQL 資料庫操作：INSERT、SELECT、UPDATE 與 DELETE 基礎語法規格。）
四、 CREATE TABLE LIKE 與 CREATE TABLE AS SELECT 差異比較
許多網路教學會介紹另一種一行寫完的縮寫語法：CREATE TABLE new_table AS SELECT * FROM old_table;（簡稱 CTAS）。雖然它能一步驟建表並塞入資料，但在正式環境中卻潛藏效能地雷：
| 比較項目 | CREATE TABLE ... LIKE ＋ INSERT（推薦） | CREATE TABLE ... AS SELECT（CTAS） |
|---|---|---|
| 複製資料表結構 | ✅ 完整保留型別與預設值 | ✅ 僅推導欄位名稱與基本型別 |
| 主鍵與索引 | ✅ 完整保留所有主鍵與索引 | ❌ 全部遺失！ |
| 自動遞增屬性 | ✅ 完整保留 | ❌ 全部遺失！ |
| 適用場景 | 正式環境備份、測試表、拆表歸檔 | 僅適合臨時匯出單次報表 |
💡 工程實務結論：只要新複製出來的資料表後續還會進行查詢或新增資料，請務必使用
CREATE TABLE ... LIKE搭配INSERT INTO ... SELECT，切勿為了省一行程式碼而弄丟整張表的索引。
五、 大資料量過期資料備份、刪除與 OPTIMIZE TABLE 釋放空間
當正式機上的資料表累積達數百萬筆時，我們通常會將「過期資料」搬移到備份表，並從現有資料表中刪除。
步驟 1 & 步驟 2：搬移備份與刪除舊資料
如果資料量較小，可以直接依時間條件搬移並刪除：
-- 1. 確保備份表結構已建立
CREATE TABLE IF NOT EXISTS `table_old` LIKE `table_data`;
-- 2. 將過期資料搬移至 table_old
INSERT INTO `table_old`
SELECT * FROM `table_data`
WHERE `created_at` < '2026-04-01 00:00:00';
-- 3. 將原表 table_data 中的過期資料刪除
DELETE FROM `table_data`
WHERE `created_at` < '2026-04-01 00:00:00';⚠️ 線上大表防當機鐵律：請務必「分批處理」
在 24 小時不間斷營運的正式環境中，千萬不能對大表直接跑全表 DELETE！因為單次鎖定數百萬筆資料列會導致資料庫卡死甚至連線逾時。穩健的做法是結合 交易（Transaction） 與 LIMIT 分批處理：
-- 建議由後端腳本迴圈執行，每次處理固定筆數
START TRANSACTION;
INSERT IGNORE INTO `table_old`
SELECT * FROM `table_data`
WHERE `created_at` < '2026-04-01 00:00:00'
ORDER BY `id` ASC
LIMIT 5000;
DELETE FROM `table_data`
WHERE `created_at` < '2026-04-01 00:00:00'
ORDER BY `id` ASC
LIMIT 5000;
COMMIT;步驟 3：執行 OPTIMIZE TABLE 優化資料表並釋放硬碟空間
許多工程師在執行 DELETE 後發現硬碟空間沒變小，這是因為 MySQL（InnoDB）在執行刪除時只會把資料列標記為「可重複使用」，產生大量碎片。請執行官方提供的 OPTIMIZE TABLE 指令來重組資料表與索引：
-- 重組資料表碎片、重建索引並將未使用的磁碟空間釋放回作業系統
OPTIMIZE TABLE `table_data`;六、 常見問題 Q&A
Q1：為什麼執行 CREATE TABLE 'new_table' LIKE 'old_table' 會報錯 ERROR 1064？
因為誤用了單引號（'）包裹資料表名稱。在 MySQL 中，包裹資料表或欄位名稱時，必須使用反引號（`），或直接省略引號。
Q2：CREATE TABLE LIKE 會連同外鍵約束（Foreign Keys）與觸發器（Triggers）一起複製嗎？
不會。CREATE TABLE ... LIKE 會複製欄位定義、主鍵與一般索引，但不會複製外鍵約束與觸發器。若原表有外鍵或觸發器，需透過 SHOW CREATE TABLE old_table; 檢視定義後手動補上。
Q3：如果只想複製資料表結構、完全不要裡面的資料，該用哪個指令？
只需要執行第一步的 CREATE TABLE \new_table` LIKE `old_table`;即可。這條指令只會複製表結構與索引，建立出來的new_table` 會是一張乾淨的 0 筆資料空表。
參考資料與延伸閱讀
- MySQL 8.0 Reference Manual: CREATE TABLE Statement (LIKE Clause)
- MySQL 8.0 Reference Manual: INSERT … SELECT Statement
- MySQL 8.0 Reference Manual: CREATE TABLE … AS SELECT Statement
- MySQL 8.0 Reference Manual: OPTIMIZE TABLE Statement
- MySQL 8.0 Reference Manual: Schema Object Names (Backtick usage)
- 延伸閱讀：MySQL 複製資料表（Table）語法教學：CREATE TABLE LIKE、INSERT SELECT 備份與搬移
- 延伸閱讀：MySQL 資料庫操作：INSERT、SELECT、UPDATE 與 DELETE 基礎語法規格
發佈留言