MySQL 進階管理指令：用戶、欄位、索引、快取操作實戰

MySQL 進階管理指令：用戶、欄位、索引、快取操作實戰

整理平時較少用但關鍵時刻必須精準掌握的指令：用戶全生命週期、欄位變更、索引維護、查詢快取控制。

一、用戶管理完整流程

1. 建立用戶並授權

-- 完整語法
GRANT privileges (columns) ON what TO user IDENTIFIED BY 'password' WITH GRANT OPTION;

-- 範例：超級用戶
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'strong_pass' WITH GRANT OPTION;

-- 範例：唯讀用戶
GRANT SELECT ON *.* TO 'readonly'@'%' IDENTIFIED BY 'pass';

權限指定符對照：

2. 撤銷權限並刪除用戶

-- 撤銷權限
REVOKE privileges (columns) ON what FROM user;
FLUSH PRIVILEGES;  -- 直接修改授權表時需手動刷新；GRANT/REVOKE 自動刷新

-- 刪除用戶
DROP USER 'user'@'host';
-- 或
DELETE FROM mysql.user WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;

二、索引管理指令

1. 新增索引

-- 普通索引（允許重複值）
ALTER TABLE `table_name` ADD INDEX `idx_name` (`col1`[, `col2`...]);
-- 範例
ALTER TABLE `employee` ADD INDEX `emp_name` (`name`);

-- 主鍵（自動唯一、非空）
ALTER TABLE `table_name` ADD PRIMARY KEY (`col_name`);
-- 範例
ALTER TABLE `employee` ADD PRIMARY KEY (`id`);

-- 唯一索引
ALTER TABLE `table_name` ADD UNIQUE `idx_name` (`col_name`);
-- 範例
ALTER TABLE `employee` ADD UNIQUE `emp_card` (`card_number`);

2. 刪除索引

ALTER TABLE `table_name` DROP INDEX `idx_name`;
-- 範例
ALTER TABLE `employee` DROP INDEX `emp_name`;

3. 查看索引

SHOW INDEX FROM `table_name`;
-- 範例
SHOW INDEX FROM `employee`;

三、欄位操作指令

1. 新增欄位

-- 末尾新增
ALTER TABLE `table_name` ADD COLUMN `col_name` `data_type`;

-- 指定位置新增
ALTER TABLE `table_name` ADD COLUMN `col_name` `data_type` FIRST;
ALTER TABLE `table_name` ADD COLUMN `col_name` `data_type` AFTER `existing_col`;

2. 刪除欄位

ALTER TABLE `table_name` DROP COLUMN `col_name`;

3. 修改欄位

-- 修改預設值
ALTER TABLE `table_name` ALTER COLUMN `col_name` SET DEFAULT 'new_default';
ALTER TABLE `table_name` ALTER COLUMN `col_name` DROP DEFAULT;

-- 修改欄位定義（含重命名、型別變更、位置移動）
ALTER TABLE `table_name` CHANGE COLUMN `old_name` `new_name` `data_type` [FIRST|AFTER `col`];
-- 型別不一致時 MySQL 自動嘗試轉換

四、查詢快取控制指令

1. 檢查快取是否啟用

SHOW VARIABLES LIKE 'have_query_cache';
-- Value = YES 表示支援並啟用

2. 整理快取（不移除條目，整理碎片）

FLUSH QUERY CACHE;

3. 完全清空快取

RESET QUERY CACHE;

4. 相關配置參數

• query_cache_limit：單一查詢結果最大快取值
• query_cache_size：快取總記憶體大小
• query_cache_type：ON/OFF/DEMAND
• query_cache_min_res_unit：快取塊最小單位
• query_cache_wlock_invalidate：WRITE LOCK 時是否失效快取