萬盛學電腦網

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

MySQL ALTER TABLE 修改表結構完整指南:增刪欄位、索引、主鍵、重命名與資料型別變更

ALTER TABLE 核心語法分類

ALTER TABLE tbl_name
    [ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]]
    [ADD INDEX [index_name] (col_list)]
    [ADD PRIMARY KEY (col_list)]
    [ADD UNIQUE [index_name] (col_list)]
    [ADD FOREIGN KEY [id] (col_list) REFERENCES ...]
    [ALTER [COLUMN] col_name {SET DEFAULT default | DROP DEFAULT}]
    [CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]]
    [MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]]
    [DROP [COLUMN] col_name]
    [DROP PRIMARY KEY]
    [DROP INDEX index_name]
    [RENAME [TO | AS] new_tbl_name]
    [DEFAULT CHARACTER SET charset [COLLATE collation]]
    [CONVERT TO CHARACTER SET charset [COLLATE collation]]
    [TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]]
    ...

建立測試表

CREATE TABLE employee (
    id INT, first_name VARCHAR(15), last_name VARCHAR(15),
    start_date DATE, end_date DATE, salary FLOAT(8,2),
    city VARCHAR(10), description VARCHAR(15)
);
INSERT INTO employee VALUES
(1,'Jason','Martin','1996-07-25','2006-07-25',1234.56,'Toronto','Programmer'),
(2,'Alison','Mathews','1976-03-21','1986-02-21',6661.78,'Vancouver','Tester'),
(3,'James','Smith','1978-12-12','1990-03-15',6544.78,'Vancouver','Tester'),
(4,'Celia','Rice','1982-10-24','1999-04-21',2344.78,'Vancouver','Manager'),
(5,'Robert','Black','1984-01-15','1998-08-08',2334.78,'Vancouver','Tester'),
(6,'Linda','Green','1987-07-30','1996-01-04',4322.78,'New York','Tester'),
(7,'David','Larry','1990-12-31','1998-02-12',7897.78,'New York','Manager'),
(8,'James','Cat','1996-09-17','2002-04-15',1232.78,'Vancouver','Tester');

常用操作實戰範例

1. 新增欄位(指定位置)

-- 最後新增
ALTER TABLE employee ADD COLUMN email VARCHAR(100);

-- 指定位置:在 first_name 之後
ALTER TABLE employee ADD COLUMN middle_name VARCHAR(15) AFTER first_name;

-- 最前面
ALTER TABLE employee ADD COLUMN seq INT FIRST;

2. 修改欄位名稱與型別(CHANGE)

-- 修改名稱 + 型別(必須重新指定完整定義)
ALTER TABLE employee CHANGE first_name firstName VARCHAR(20);

-- 僅修改型別(MODIFY,名稱不變)
ALTER TABLE employee MODIFY last_name VARCHAR(50);

差異:CHANGE 可改名、可改型別、可移動位置;MODIFY 僅能改型別/屬性,不可改名。

3. 設定/移除預設值

ALTER TABLE employee ALTER COLUMN city SET DEFAULT 'Unknown';
ALTER TABLE employee ALTER COLUMN city DROP DEFAULT;

4. 索引操作

-- 一般索引
ALTER TABLE employee ADD INDEX idx_city (city);

-- 唯一索引
ALTER TABLE employee ADD UNIQUE uk_email (email);

-- 主鍵(表僅能有一個)
ALTER TABLE employee ADD PRIMARY KEY (id);

-- 刪除索引
ALTER TABLE employee DROP INDEX idx_city;
ALTER TABLE employee DROP PRIMARY KEY;

5. 刪除欄位

ALTER TABLE employee DROP COLUMN middle_name;
ALTER TABLE employee DROP COLUMN seq;

6. 重命名表

ALTER TABLE employee RENAME TO staff;
-- 或
RENAME TABLE staff TO employee;  -- 可一次改多表,原子性更好

7. 變更表字元集(重建表)

ALTER TABLE employee CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

進階技巧與注意事項

多項變更合併為單一 ALTER(減少鎖表時間)

ALTER TABLE employee
    ADD COLUMN department_id INT AFTER id,
    ADD INDEX idx_dept (department_id),
    MODIFY salary DECIMAL(10,2),
    DROP COLUMN description;

MySQL 5.6+ Online DDL 允許大多數操作並發讀寫,但仍會短暫鎖表;合併陳述式可減少鎖定次數。

大表變更策略(pt-online-schema-change / gh-ost)

  • 百萬列以上表,直接 ALTER 可能鎖表數分鐘至數小時
  • 使用 pt-online-schema-change (Percona Toolkit) 或 gh-ost (GitHub) 進行無鎖線上變更
  • 原理:建立影子表 → 同步資料 → 切換表名

外鍵約束變更

-- 先刪除 FK
ALTER TABLE child DROP FOREIGN KEY fk_name;
-- 再修改欄位
ALTER TABLE child MODIFY parent_id BIGINT;
-- 最後重建 FK
ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (parent_id) REFERENCES parent(id);

IGNORE 關鍵字(已移除)

MySQL 5.7 移除 ALTER IGNORE TABLE ... ADD UNIQUE 去重功能。去重請改用:

CREATE TABLE new_tbl LIKE old_tbl;
ALTER TABLE new_tbl ADD UNIQUE uk_col (col);
INSERT IGNORE INTO new_tbl SELECT * FROM old_tbl;
RENAME TABLE old_tbl TO old_tbl_bak, new_tbl TO old_tbl;

驗證變更結果

DESCRIBE employee;
SHOW CREATE TABLE employee;
SHOW INDEX FROM employee;

權限需求

  • ALTER 權限於該表
  • CREATE、INSERT 權限(某些重建操作需要)
  • 若涉及外鍵,需 REFERENCES 權限

總結

ALTER TABLE 是 DDL 核心指令。掌握 CHANGE vs MODIFY、索引管理、Online DDL 條件、大表變更工具,並養成合併多項變更、先測試再上線、備份資料的習慣,才能安全高效地演進資料庫結構。

copyright © 萬盛學電腦網 all rights reserved