MySQL 基礎操作手冊（下）：表結構變更、數據增刪改、索引管理、用戶權限、備份恢復

承接上篇，本文匯總 MySQL 5.x/8.x 通用的 DDL、DML、索引、權限、備份核心命令與實戰示例，作為日常運維開發的速查手冊。

一、表結構變更（ALTER TABLE）

-- 增加欄位
ALTER TABLE student ADD saddress VARCHAR(25);

-- 修改欄位名+類型（即使類型不變也必須指定類型）
ALTER TABLE student CHANGE saddress sremark TEXT;

-- 僅修改類型
ALTER TABLE student MODIFY sremark VARCHAR(25);

-- 刪除欄位
ALTER TABLE student DROP sremark;

-- 重命名表
ALTER TABLE student RENAME TO employee;

注意： 大表 ALTER TABLE 會鎖表重建（MySQL 5.6+ 支援 Online DDL 部份操作），高併發環境請用 pt-online-schema-change 或 gh-ost 非阻塞變更。

二、數據增刪改（DML）

1. 插入

INSERT INTO student(sno, sname, ssex, sbirthday, sdepa)
VALUES ('0321001', 'Delux', DEFAULT, '1983-12-11', 'math');

-- 批量插入（單語句多 VALUES，效率遠高於多條 INSERT）
INSERT INTO student(sno, sname) VALUES
('001', 'A'), ('002', 'B'), ('003', 'C');

2. 刪除

DELETE FROM student WHERE sno='0321001';           -- 按條件刪除
DELETE FROM student WHERE LEFT(sno,4)='0321';      -- 模糊條件刪除
DELETE FROM student;                               -- 逐行刪全表（慢、產生大量 Binlog）
TRUNCATE TABLE student;                            -- 截斷表：DROP+重建，極快、不觸發 Trigger、自增歸零

3. 更新

UPDATE student SET sbirthday='1984-10-10' WHERE sno='9810141';
-- 返回：Rows matched: 1  Changed: 1  Warnings: 0

三、索引管理

1. 建表時創建

CREATE TABLE course (
  cno VARCHAR(5) NOT NULL,
  cname VARCHAR(30) NOT NULL,
  teacher VARCHAR(20),
  PRIMARY KEY (cno),
  INDEX cna (cname)          -- 普通索引
  -- UNIQUE (cname)           -- 唯一索引，禁止重複值
);

2. 事後添加

CREATE INDEX sna ON student(sname);
CREATE UNIQUE INDEX uq_email ON users(email);

-- 字符/文本列可指定前綴長度
CREATE INDEX idx_content_prefix ON articles(content(100));

-- BLOB/TEXT 列**必須**指定前綴長度

3. 刪除索引

DROP INDEX sna ON student;
DROP INDEX uq_email ON users;

四、用戶與權限體系

1. 五大授權表（mysql 库）

2. 常用權限項

• 數據操作：SELECT, INSERT, UPDATE, DELETE
• 結構操作：CREATE, DROP, ALTER, INDEX
• 管理操作：RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE/CLIENT
• 授權傳遞：GRANT OPTION

3. GRANT / REVOKE 規範化用法

-- 授權（推薦显式列出權限，避免 ALL PRIVILEGES）
GRANT SELECT, INSERT, UPDATE ON db_name.* TO 'user'@'host' IDENTIFIED BY 'pass' WITH GRANT OPTION;

-- 列級授權
GRANT SELECT (id, name), UPDATE (name) ON db.tbl TO 'user'@'host';

-- 回收權限
REVOKE INSERT ON db_name.* FROM 'user'@'host';

-- 修改密碼（兩種方式）
SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass');  -- 無需 FLUSH PRIVILEGES
UPDATE mysql.user SET authentication_string=PASSWORD('newpass') WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;

五、備份與恢復

1. 邏輯備份

# 全庫備份（含結構+數據）
mysqldump -u root -p --single-transaction --routines --triggers --events --databases db1 db2 > backup.sql

# 僅結構
mysqldump -u root -p -d db_name > schema.sql

# 僅數據
mysqldump -u root -p -t db_name > data.sql

# 指定表
mysqldump -u root -p db_name tbl1 tbl2 > tables.sql

# 壓縮輸出
mysqldump -u root -p db_name | gzip > backup.sql.gz

2. 邏輯恢復

mysql -u root -p db_name < backup.sql
# 或進入 mysql 客戶端
SOURCE /path/backup.sql;

3. 物理備份（生產首選）

• Percona XtraBackup：InnoDB 熱備份、不阻塞寫入、支援增量、流式傳輸、並行壓縮。
• 文件系統快照：LVM/ZFS/AWS EBS 快照 + 紅寶石一致性鎖（FLUSH TABLES WITH READ LOCK 秒級）。

六、避坑速查

• DELETE FROM table 不重置自增 ID，TRUNCATE 會歸零。
• CHANGE 必須指定類型，MODIFY 僅改類型。
• BLOB/TEXT 索引**必須**指定前綴長度。
• 權限修改後無需 FLUSH PRIVILEGES（MySQL 8.0+ 自動生效），但直接操作 mysql.* 表後必須執行。
• 備份必驗證：mysql -u root -p test_db < backup.sql 定期演練恢復，未測試的備份 = 無備份。

總結

掌握這套「DDL/DML/索引/權限/備份」核心命令集，覆蓋 90% 日常運維開發場景。關鍵在於：**大表變更用在線工具、刪數據用 TRUNCATE、權限最小化授予、備份必演練恢復**。將命令內化為肌肉記憶，配合版本差異（5.7 vs 8.0 權限系統、認證插件變化）查閱官方文檔，方能游刃有餘。