您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 表結構查看與修改進階：SHOW CREATE TABLE、ALTER TABLE 全語法實戰

編輯：MySQL 綜合教學

MySQL 表結構查看與修改進階：SHOW CREATE TABLE、ALTER TABLE 全語法實戰

掌握 DESC、SHOW CREATE TABLE、ALTER TABLE 全語法，是高效進行 Schema 管理的核心技能。本文系統整理查看方法、修改語法、大表策略與最佳實踐。

一、表結構查看三板斧

1. DESC / DESCRIBE - 快速列表


DESC product;
-- 或
DESCRIBE product;
-- 或
SHOW COLUMNS FROM product;

輸出解讀：Field、Type、Null、Key (PRI/UNI/MUL)、Default、Extra (auto_increment/on update CURRENT_TIMESTAMP)

2. SHOW CREATE TABLE - 完整 DDL


SHOW CREATE TABLE bm\G;
*************************** 1. row ***************************
       Table: bm
Create Table: CREATE TABLE `bm` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `STH` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

關鍵信息：引擎、字符集、自增值、索引定義、外鍵、分區、註釋

3. INFORMATION_SCHEMA - 程序化查詢


SELECT COLUMN_NAME, DATA_TYPE, COLUMN_KEY, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='db' AND TABLE_NAME='tbl';

二、ALTER TABLE 完整語法速查

1. 表重命名


ALTER TABLE old_name RENAME [TO] new_name;
ALTER TABLE bm RENAME department;

2. 列數據類型修改


ALTER TABLE tbl MODIFY col NEW_TYPE;
ALTER TABLE department MODIFY id VARCHAR(11);

3. 列重命名 (需指定完整類型)


ALTER TABLE tbl CHANGE old_col new_col NEW_TYPE;
ALTER TABLE employees CHANGE location loc VARCHAR(350);

4. 添加列


ALTER TABLE tbl ADD col TYPE [CONSTRAINTS] [FIRST|AFTER exist_col];
ALTER TABLE employees ADD manager_id INT(10);
ALTER TABLE employees ADD age INT(11) NOT NULL;
ALTER TABLE employees ADD sal FLOAT FIRST;
ALTER TABLE employees ADD hire_date DATE AFTER manager_id;

5. 刪除列


ALTER TABLE tbl DROP col;
ALTER TABLE employees DROP manager_id;
ALTER TABLE employees DROP hire_date;

6. 修改列位置


ALTER TABLE tbl MODIFY col TYPE FIRST|AFTER other_col;
ALTER TABLE employees MODIFY name VARCHAR(22) FIRST;
ALTER TABLE department MODIFY location VARCHAR(350) AFTER department_id;

7. 修改存儲引擎


ALTER TABLE tbl ENGINE=NewEngine;
ALTER TABLE department ENGINE=MyISAM;
-- 主流引擎: InnoDB, MyISAM, MEMORY, ARCHIVE, CSV, BLACKHOLE, FEDERATED

8. 刪除外鍵約束


ALTER TABLE tbl DROP FOREIGN KEY fk_name;
ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept;

三、刪除表操作

1. 普通表刪除


DROP TABLE [IF EXISTS] tbl1, tbl2, ...;
DROP TABLE IF EXISTS employees;

2. 被引用主表刪除 (需先刪外鍵)


-- 建立測試場景
CREATE DATABASE rm_tab;
USE rm_tab;
CREATE TABLE department (id INT PRIMARY KEY, name VARCHAR(22), location VARCHAR(200));
CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(25), department_id INT, salary FLOAT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES department(id));

-- 直接刪除主表失敗
DROP TABLE department;  -- ERROR 1217

-- 正確流程
ALTER TABLE employee DROP FOREIGN KEY fk_emp_dept;
DROP TABLE department;  -- 成功

四、大表 DDL 執行策略

策略	適用場景	工具/語法	優點	缺點
原生 DDL	小表、維護窗口、可接受鎖表	ALTER TABLE ...	簡單、原子性	長時間鎖表、不可中斷
Online DDL (5.6+)	InnoDB 添加/刪除索引、修改列屬性	ALGORITHM=INPLACE, LOCK=NONE	無鎖/輕量鎖	不支持所有操作、需滿足條件
pt-online-schema-change	大表、無維護窗口、業務零停機	pt-osc --alter "..." D=db,t=tbl --execute	完全在線、可控、可回滾	需額外工具、觸發器開銷、雙倍空間
影子表雙寫	極大表、極高可用性要求	應用層雙寫 + 同步校驗 + 切換	完全業務可控	開發成本極高、雙寫一致性難保證

五、Online DDL 操作分類 (5.6+)


-- 完全在線 (INPLACE, LOCK=NONE)
ALTER TABLE tbl ADD INDEX idx_col (col), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE tbl DROP INDEX idx_col, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE tbl MODIFY col VARCHAR(100), ALGORITHM=INPLACE, LOCK=NONE;  -- 類型擴大

-- 允許共享鎖 (INPLACE, LOCK=SHARED)
ALTER TABLE tbl ADD COLUMN col INT, ALGORITHM=INPLACE, LOCK=SHARED;

-- 僅複製表 (COPY, 鎖表)
ALTER TABLE tbl CHANGE col new_col BIGINT, ALGORITHM=COPY;  -- 類型縮小、重命名