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');
-- 最後新增
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;
-- 修改名稱 + 型別(必須重新指定完整定義)
ALTER TABLE employee CHANGE first_name firstName VARCHAR(20);
-- 僅修改型別(MODIFY,名稱不變)
ALTER TABLE employee MODIFY last_name VARCHAR(50);
差異:CHANGE 可改名、可改型別、可移動位置;MODIFY 僅能改型別/屬性,不可改名。
ALTER TABLE employee ALTER COLUMN city SET DEFAULT 'Unknown';
ALTER TABLE employee ALTER COLUMN city DROP DEFAULT;
-- 一般索引
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;
ALTER TABLE employee DROP COLUMN middle_name;
ALTER TABLE employee DROP COLUMN seq;
ALTER TABLE employee RENAME TO staff;
-- 或
RENAME TABLE staff TO employee; -- 可一次改多表,原子性更好
ALTER TABLE employee CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
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 允許大多數操作並發讀寫,但仍會短暫鎖表;合併陳述式可減少鎖定次數。
ALTER 可能鎖表數分鐘至數小時-- 先刪除 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);
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 條件、大表變更工具,並養成合併多項變更、先測試再上線、備份資料的習慣,才能安全高效地演進資料庫結構。