MySQL 常用命令精華整理 (轉載自 cnblogs)

MySQL 常用命令精華整理

本文轉載自 cnblogs 博主 q1ng，針對 MySQL 日常開發與運維高頻指令進行分類彙總，包含資料匯出入、服務控制、庫表結構操作、資料增刪改查、使用者授權、編碼設定、備份策略等核心內容，適合離線速查與團隊知識庫沉澱。

一、 資料匯出與匯入

• 完整庫備份： mysqldump -u user -p --default-character-set=utf8mb4 db > backup.sql
• 單表備份： mysqldump -u user -p db table > table.sql
• 僅結構 (含 DROP TABLE)： mysqldump -u user -p -d --add-drop-table db > schema.sql
• 還原 (Source 互動式)： 進入 mysql > USE db; SOURCE backup.sql;
• 還原 (非互動式)： mysql -u user -p -D db < backup.sql

二、 服務啟停與連線基礎

• Windows: net start mysql / net stop mysql
• Linux: systemctl start mysqld / /etc/init.d/mysqld start
• 連線: mysql -h host -u user -p (本機可省 -h)
• 退出: EXIT / QUIT / \q

三、 資料庫與資料表 DDL

四、 資料操作 DML 範例

• 插入多筆: INSERT INTO t (name, age) VALUES ('A', 1), ('B', 2);
• 條件查詢: SELECT * FROM t WHERE age > 1 ORDER BY age DESC LIMIT 3;
• 更新: UPDATE t SET age = age + 1 WHERE name = 'A';
• 刪除: DELETE FROM t WHERE age < 2;
• 字串替換: UPDATE t SET name = REPLACE(name, 'A', 'C');
• 載入文字檔: LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t FIELDS TERMINATED BY '\t';

五、 使用者與權限管理

1. 建立用戶: CREATE USER 'dev'@'%' IDENTIFIED BY 'Pwd123!';
2. 授權 (庫級): GRANT SELECT, INSERT, UPDATE, DELETE ON dev_db.* TO 'dev'@'%';
3. 授權 (表級): GRANT SELECT ON dev_db.logs TO 'dev'@'%';
4. 刷新生效: FLUSH PRIVILEGES;
5. 查看權限: SHOW GRANTS FOR 'dev'@'%';
6. 回收權限: REVOKE DELETE ON dev_db.* FROM 'dev'@'%';
7. 修改密碼: ALTER USER 'dev'@'%' IDENTIFIED BY 'NewPwd456!';
8. 刪除用戶: DROP USER 'dev'@'%';

六、 編碼設定與亂碼排查

• 檢視編碼: SHOW VARIABLES LIKE 'character%';
• Session 級設定: SET NAMES utf8mb4;
• 永久生效: 編輯 my.cnf / my.ini

  [client]
  default-character-set=utf8mb4
  [mysqld]
  character-set-server=utf8mb4
  collation-server=utf8mb4_general_ci

  重啟服務生效。

七、 進階查詢語法速查

• 連接查詢： INNER/LEFT/RIGHT JOIN ... ON ...
• 子查詢： WHERE col IN (SELECT ...), EXISTS (SELECT ...)
• 集合運算： UNION / UNION ALL (MySQL 8.0+ 支援 INTERSECT/EXCEPT)
• 分組聚合： GROUP BY col HAVING COUNT(*) > 1
• 分頁： LIMIT offset, rows
• 視窗函數 (8.0+)： ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)

八、 備份最佳實踐建議

1. 生產環境採用 XtraBackup 物理熱備份，減少鎖表影響。
2. 邏輯備份加參數：--single-transaction --master-data=2 --routines --triggers 保證一致性。
3. 啟用 Binlog (log_bin, binlog_format=ROW) 配合 mysqlbinlog 實現 PITR (時間點恢復)。
4. 定期演練還原，驗證備份可用性 (RTO/RPO 指標)。

九、 常用除錯與檢視指令

• SHOW PROCESSLIST; / KILL id;
• SHOW ENGINE INNODB STATUS; (鎖/死鎖分析)
• EXPLAIN SELECT ...; (執行計畫)
• SHOW INDEX FROM t; (索引狀態)
• SELECT TABLE_NAME, ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) AS 'MB' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db'; (表大小)

來源註明：https://www.cnblogs.com/q1ng/p/4474501.html。建議配合官方文件 dev.mysql.com/doc 深入理解各參數邊界與版本差異。