業務中常需複製表結構、數據或兩者兼備。本文介紹 SHOW CREATE TABLE + CREATE TABLE + INSERT SELECT 標準克隆流程,以及 CREATE TABLE ... LIKE、CREATE TABLE ... SELECT 速成法。
mysql> SHOW CREATE TABLE tutorials_tbl\G
*************************** 1. row ***************************
Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
`tutorial_id` int(11) NOT NULL auto_increment,
`tutorial_title` varchar(100) NOT NULL default '',
`tutorial_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`tutorial_id`),
UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
CREATE TABLE `clone_tbl` (
`tutorial_id` int(11) NOT NULL auto_increment,
`tutorial_title` varchar(100) NOT NULL default '',
`tutorial_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`tutorial_id`),
UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO clone_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date)
SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl;
| 語句 | 複製結構 | 複製索引/約束 | 複製數據 | 適用場景 |
|---|---|---|---|---|
CREATE TABLE t2 LIKE t1 | ✅ | ✅ (含索引、FK、默認值) | ❌ | 結構克隆 |
CREATE TABLE t2 SELECT * FROM t1 | ✅ (僅列定義) | ❌ (無索引、PK、FK、默認值) | ✅ | 數據導出、歸檔 |
CREATE TABLE t2 LIKE t1; INSERT INTO t2 SELECT * FROM t1; | ✅ | ✅ | ✅ | 完整克隆(推薦) |
| 標準流程 | ✅ | ✅ | ✅ | 需調整結構再克隆 |
CREATE TABLE new_table LIKE old_table;
CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table WHERE status = 1 AND create_time > '2024-01-01';
CREATE TABLE user_summary AS
SELECT id AS user_id, CONCAT(first_name, ' ', last_name) AS full_name, email
FROM users;
CREATE TABLE db2.new_table LIKE db1.old_table;
INSERT INTO db2.new_table SELECT * FROM db1.old_table;
ALTER TABLE clone_tbl DROP INDEX uk_email, DROP INDEX idx_status;
INSERT INTO clone_tbl SELECT * FROM tutorials_tbl;
ALTER TABLE clone_tbl ADD UNIQUE INDEX uk_email (email), ADD INDEX idx_status (status);
.ibd 文件 + IMPORT TABLESPACE (InnoDB Transportable Tablespaces)-- 結構對比
SHOW CREATE TABLE tutorials_tbl\G
SHOW CREATE TABLE clone_tbl\G
-- 行數核對
SELECT COUNT(*) FROM tutorials_tbl;
SELECT COUNT(*) FROM clone_tbl;
-- 抽樣數據核對
SELECT * FROM tutorials_tbl ORDER BY RAND() LIMIT 10;
SELECT * FROM clone_tbl ORDER BY RAND() LIMIT 10;
-- 校驗和對比 (MySQL 8.0+)
SELECT CHECKSUM TABLE tutorials_tbl, clone_tbl;
AUTO_INCREMENT 值會複製,新表插入從現有最大值+1 開始