MySQL 基礎操作指令大全：從入門到日常運維

前言：指令是溝通資料庫的語言

熟練掌握 MySQL 客戶端指令，是開發、測試、運維的基本功。本文系統整理服務管理、連線認證、DDL/DML/DQL、備份還原、效能分析、進階物件管理等高頻指令，並附上實戰場景與避坑提醒。

一、服務管理與連線認證

1. 服務啟動/停止/狀態

2. 客戶端連線參數詳解

mysql -h  -P  -u  -p -D  [options]

# 常用選項
--protocol=tcp|socket|pipe|memory   # 連線協定
--ssl-mode=REQUIRED|VERIFY_CA|VERIFY_IDENTITY  # SSL 模式
--default-character-set=utf8mb4     # 指定字元集
--init-command="SET sql_mode='STRICT_TRANS_TABLES'"  # 登入後自動執行
--safe-updates                      # 禁止無 WHERE 的 UPDATE/DELETE
--max-allowed-packet=1G             # 客戶端封包上限
--compress                          # 啟用壓縮協定
--quick                             # 結果集不快取到記憶體，直接流式輸出（大結果集必加）

3. 免密登入配置（.my.cnf）

[client]
user=root
password=your_password
host=localhost
port=3306
socket=/var/lib/mysql/mysql.sock

# 權限必須 600
chmod 600 ~/.my.cnf

二、資料庫與資料表管理 (DDL)

1. 資料庫操作

-- 建立（指定字元集與校對）
CREATE DATABASE `db_name` 
  DEFAULT CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-- 查看建庫語句
SHOW CREATE DATABASE `db_name`;

-- 修改字元集
ALTER DATABASE `db_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 刪除（慎用！）
DROP DATABASE `db_name`;

2. 資料表操作

-- 建表最佳實踐範本
CREATE TABLE `orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
  `order_no` VARCHAR(32) NOT NULL COMMENT '訂單號',
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '金額',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '狀態:0待付款 1已付款 2已取消',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_status` (`user_id`, `status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='訂單表';

-- 查看建表語句
SHOW CREATE TABLE `orders`;

-- 修改表結構
ALTER TABLE `orders` 
  ADD COLUMN `refund_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '退款金額' AFTER `amount`,
  ADD INDEX `idx_refund` (`refund_amount`),
  MODIFY COLUMN `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '狀態擴展:3退款中 4已退款',
  DROP INDEX `idx_created_at`,
  RENAME TO `orders_v2`;

-- 刪除表
DROP TABLE IF EXISTS `old_table`;

3. 索引管理

-- 建立索引（三種等價方式）
ALTER TABLE `t` ADD INDEX `idx_name` (`name`);
CREATE INDEX `idx_name` ON `t` (`name`);
CREATE TABLE `t` (..., INDEX `idx_name` (`name`));

-- 唯一索引
ALTER TABLE `t` ADD UNIQUE INDEX `uk_email` (`email`);

-- 全文索引（5.6+ InnoDB 支援）
ALTER TABLE `articles` ADD FULLTEXT INDEX `ft_content` (`title`, `body`);

-- 刪除索引
DROP INDEX `idx_name` ON `t`;
ALTER TABLE `t` DROP INDEX `idx_name`;

-- 線上 DDL（5.6+，大表必加）
ALTER TABLE `big_table` ADD INDEX `idx_new` (`col`), ALGORITHM=INPLACE, LOCK=NONE;

三、資料操作 (DML) 與查詢 (DQL)

1. 插入/更新/刪除

-- 插入（批次效能最佳）
INSERT INTO `t` (`a`, `b`) VALUES (1, 'x'), (2, 'y'), (3, 'z');

-- 插入或更新（Upsert）
INSERT INTO `t` (`id`, `val`) VALUES (1, 'new') 
  ON DUPLICATE KEY UPDATE `val` = VALUES(`val`), `updated_at` = NOW();

-- 更新（必加 WHERE，防誤傷）
UPDATE `t` SET `status` = 1 WHERE `id` = 100 AND `status` = 0;

-- 刪除（軟刪除優於硬刪除）
UPDATE `t` SET `deleted_at` = NOW() WHERE `id` = 100;
-- 硬刪除
DELETE FROM `t` WHERE `id` = 100 AND `deleted_at` IS NOT NULL;

2. 查詢核心語法

-- 分頁（深分頁效能差，建議游標式）
SELECT * FROM `t` WHERE `id` > 10000 ORDER BY `id` LIMIT 20;

-- 多表關聯
SELECT o.*, u.`name` 
FROM `orders` o
INNER JOIN `users` u ON o.`user_id` = u.`id`
WHERE o.`status` = 1 AND u.`status` = 1;

-- 聚合與分組
SELECT `status`, COUNT(*) AS `cnt`, SUM(`amount`) AS `total`
FROM `orders`
WHERE `created_at` >= '2024-01-01'
GROUP BY `status`
HAVING `cnt` > 100
ORDER BY `total` DESC;

-- 子查詢與 CTE (8.0+)
WITH `high_value_users` AS (
  SELECT `user_id` FROM `orders` 
  WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  GROUP BY `user_id` HAVING SUM(`amount`) > 10000
)
SELECT u.* FROM `users` u
INNER JOIN `high_value_users` hvu ON u.`id` = hvu.`user_id`;

3. 字串與日期常用函式

四、備份與還原

1. mysqldump 邏輯備份（標準工具）

# 完整備份（含結構+資料）
mysqldump -u root -p --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob --default-character-set=utf8mb4 \
  --databases db1 db2 > full_backup_$(date +%F).sql

# 僅結構
mysqldump -u root -p --no-data --databases db1 > schema_only.sql

# 單表備份
mysqldump -u root -p db1 table1 table2 > tables_backup.sql

# 遠端備份
mysqldump -h remote_host -u root -p db1 | gzip > backup.sql.gz

# 關鍵參數說明
--single-transaction  # InnoDB 一致性快照，不鎖表
--master-data=2       # 記錄 Binlog 位置（註解形式），便於建立從庫
--hex-blob            # 二進位欄位用十六進位輸出，避免字元集問題
--quick               # 大表流式讀取，不快取到記憶體

2. 還原操作

# 完整還原
mysql -u root -p < full_backup.sql

# 指定資料庫還原（備份檔含多庫時）
mysql -u root -p -D target_db < full_backup.sql

# 並行還原（需拆分檔案）
# 參考 mydumper/myloader 章節

3. 物理備份：Percona XtraBackup（企業級首選）

# 安裝
yum install percona-xtrabackup-80 -y

# 完整備份（不阻塞寫入）
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=root --password

# 增量備份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full_20240115 --user=root --password

# 準備（適用 Redo Log）
xtrabackup --prepare --target-dir=/backup/full_20240115
xtrabackup --prepare --target-dir=/backup/full_20240115 --incremental-dir=/backup/inc1

# 還原（需停止 MySQL、清空 datadir）
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full_20240115
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

五、使用者與權限管理

-- 建立使用者（8.0 語法）
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly456!';

-- 授權（最小權限原則）
GRANT SELECT, INSERT, UPDATE, DELETE ON `biz_db`.* TO 'app'@'192.168.1.%';
GRANT SELECT ON `biz_db`.* TO 'readonly'@'%';

-- 撤銷權限
REVOKE DELETE ON `biz_db`.* FROM 'app'@'192.168.1.%';

-- 查看權限
SHOW GRANTS FOR 'app'@'192.168.1.%';

-- 修改密碼
ALTER USER 'app'@'192.168.1.%' IDENTIFIED BY 'NewPass456!';

-- 刪除使用者
DROP USER 'app'@'192.168.1.%';

-- 8.0 角色管理（簡化批量授權）
CREATE ROLE 'app_readonly';
GRANT SELECT ON `biz_db`.* TO 'app_readonly';
CREATE USER 'dev1'@'%' IDENTIFIED BY 'pass';
GRANT 'app_readonly' TO 'dev1'@'%';
SET DEFAULT ROLE 'app_readonly' TO 'dev1'@'%';

六、效能分析與監控指令

1. 即時狀態觀察

-- 當前執行緒/查詢
SHOW FULL PROCESSLIST;
-- 或資訊結構（8.0+，效能更好）
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep';

-- InnoDB 狀態（核心診斷資訊）
SHOW ENGINE INNODB STATUS\G

-- 全域狀態計數器
SHOW GLOBAL STATUS LIKE 'Com_%';        # 命令計數
SHOW GLOBAL STATUS LIKE 'Innodb_%';     # InnoDB 核心指標
SHOW GLOBAL STATUS LIKE 'Qcache_%';     # 查詢快取（5.7）

-- 慢查詢狀態
SHOW GLOBAL STATUS LIKE 'Slow_%';

2. 執行計劃分析

EXPLAIN FORMAT=JSON SELECT * FROM `orders` WHERE `user_id` = 100 AND `status` = 1;
-- 關注：type, key, key_len, ref, rows, filtered, Extra
-- type: system > const > eq_ref > ref > range > index > ALL
-- Extra: Using index, Using where, Using temporary, Using filesort

3. 索引使用統計

-- 查看索引基數
SHOW INDEX FROM `orders`;

-- 查詢未使用索引（需啟用 userstat 或 sys schema）
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

七、進階物件：視圖、觸發器、預存程序、事件

-- 視圖
CREATE VIEW `v_user_order_summary` AS
SELECT u.`id`, u.`name`, COUNT(o.`id`) AS `order_cnt`, SUM(o.`amount`) AS `total_amount`
FROM `users` u LEFT JOIN `orders` o ON u.`id` = o.`user_id`
GROUP BY u.`id`, u.`name`;

-- 觸發器（審計/同步/驗證）
DELIMITER $$
CREATE TRIGGER `trg_orders_audit_insert` AFTER INSERT ON `orders`
FOR EACH ROW BEGIN
  INSERT INTO `order_audit` (`order_id`, `action`, `new_data`, `created_at`)
  VALUES (NEW.`id`, 'INSERT', JSON_OBJECT('amount', NEW.`amount`, 'status', NEW.`status`), NOW());
END$$
DELIMITER ;

-- 預存程序
DELIMITER $$
CREATE PROCEDURE `sp_get_user_orders`(IN p_user_id BIGINT, IN p_limit INT)
BEGIN
  SELECT * FROM `orders` WHERE `user_id` = p_user_id ORDER BY `created_at` DESC LIMIT p_limit;
END$$
DELIMITER ;

-- 事件（定時任務，需啟用 event_scheduler=ON）
CREATE EVENT `evt_cleanup_expired_sessions`
ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 03:00:00'
DO
  DELETE FROM `sessions` WHERE `expires_at` < NOW();

八、系統資訊與版本查詢

SELECT VERSION();                    -- 版本號
SELECT CURRENT_USER();               -- 當前使用者
SELECT DATABASE();                   -- 當前資料庫
SELECT NOW();                        -- 當前時間
SHOW VARIABLES LIKE 'version%';      -- 詳細版本資訊
SHOW VARIABLES LIKE 'innodb_version';-- InnoDB 版本
SELECT @@sql_mode;                   -- 當前 SQL 模式
SHOW ENGINES;                        -- 支援引擎列表

九、避坑指南：高頻錯誤與最佳實踐

總結

MySQL 指令體系龐大，但核心高頻者有限。建議：

1. 建立個人 Cheat Sheet：常用指令、參數、範本整理成單頁文件
2. 善用 \G 垂直輸出：寬表資訊結構、InnoDB 狀態、EXPLAIN 結果更易閱讀
3. 養成 EXPLAIN 習慣：上線前必跑執行計劃
4. 版本差異留意：5.7 → 8.0 語法、認證、資訊結構變化大
5. 自動化腳本化：備份、巡檢、權限審計、結構同步皆納入 CI/CD

指令熟練度 = 故障處理速度。每天花 10 分鐘練習一個新指令或參數，半年後將質變為資料庫操作專家。