MySQL 表結構操作實戰：從創建到修改的完整案例教程

MySQL 表結構操作實戰：從創建到修改的完整案例教程

通過一個完整的公司部門員工管理模型，演示 MySQL 表的創建、查看、修改、刪除、引擎轉換、重命名等全生命周期操作。

一、創建數據庫與基礎表

-- 1. 創建數據庫
CREATE DATABASE company;
USE company;

-- 2. 創建部門表
CREATE TABLE offices (
    officeCode INT(10) NOT NULL UNIQUE,
    city VARCHAR(50) NOT NULL,
    address VARCHAR(50) NOT NULL,
    country VARCHAR(50) NOT NULL,
    postalCode VARCHAR(15) NOT NULL,
    PRIMARY KEY (officeCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 創建員工表 (含外鍵)
CREATE TABLE employees (
    employeeNumber INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    lastName VARCHAR(50) NOT NULL,
    firstName VARCHAR(50) NOT NULL,
    mobile VARCHAR(25) NOT NULL,
    officeCode INT(10) NOT NULL,
    jobTitle VARCHAR(50) NOT NULL,
    birth DATETIME,
    note VARCHAR(255),
    sex VARCHAR(5),
    CONSTRAINT office_fk FOREIGN KEY (officeCode) REFERENCES offices(officeCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二、查看表結構驗證

-- 查看所有表
SHOW TABLES;
-- offices, employees

-- 查看部門表結構
DESC offices;
-- officeCode (PK), city, address, country, postalCode

-- 查看員工表結構
DESC employees;
-- employeeNumber (PK, AI), lastName, firstName, mobile, officeCode (MUL), jobTitle, birth, note, sex

三、表結構修改系列操作

1. 修改列位置：mobile 移至 officeCode 後

ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
-- 驗證：DESC employees; 確認順序變更

2. 重命名列：birth → employee_birth

ALTER TABLE employees CHANGE birth employee_birth DATETIME;
-- 驗證：列名變更，類型保持

3. 修改列屬性：sex 字符集、非空

ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
-- 類型縮短 VARCHAR(5)→CHAR(1)，添加非空約束

4. 刪除列：移除 note

ALTER TABLE employees DROP note;
-- 數據永久刪除，謹慎操作

5. 添加列：favoriate_activity

ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
-- 默認添加到末尾，可用 FIRST/AFTER 定位

四、外鍵約束與表刪除的依賴處理

-- 嘗試直接刪除被引用的主表
DROP TABLE offices;
-- ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

-- 正確流程：
-- 1. 刪除從表外鍵
ALTER TABLE employees DROP FOREIGN KEY office_fk;

-- 2. 刪除主表
DROP TABLE offices;
-- Query OK

-- 驗證
SHOW TABLES;  -- 僅剩 employees

五、存儲引擎轉換

-- 修改為 MyISAM
ALTER TABLE employees ENGINE=MyISAM;

-- 驗證引擎變更
SHOW CREATE TABLE employees\G;
-- Engine: MyISAM
-- 注意：外鍵索引 office_fk 保留但約束失效 (MyISAM 不支持外鍵)

六、表重命名

ALTER TABLE employees RENAME employees_info;
SHOW TABLES;  -- employees_info

七、操作總結對比表

八、大表變更最佳實踐 (pt-online-schema-change)

# 安全添加列 (不鎖表)
pt-online-schema-change \
  --alter "ADD COLUMN new_col VARCHAR(50) DEFAULT ''" \
  D=company,t=employees_info \
  --execute \
  --max-load Threads_running=50 \
  --critical-load Threads_running=100 \
  --chunk-time 0.5

九、完整驗證腳本

-- 結構導出對比
SHOW CREATE TABLE employees_info\G;

-- 索引檢查
SHOW INDEX FROM employees_info;

-- 外鍵檢查
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA='company';

-- 表大小統計
SELECT TABLE_NAME, ENGINE, TABLE_ROWS, 
       ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) AS total_mb
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA='company';

表結構變更是資料庫演進的常態。掌握標準語法、理解鎖行為、評估數據風險、選擇適合工具 (原生 DDL vs pt-osc)，才能在保障業務連續性的前提下，安全高效地完成 Schema Evolution。