MySQL 常用命令大全：從入門到運維的速查手冊

整理了 MySQL 日常開發、運維、遷移、權限管理的高頻命令，按模塊分類，適合收藏為桌面速查表。

一、 連接與退出

mysql -h host -P port -u user -p        # 交互式登錄
mysql -e "SQL語句"                       # 非交互執行單條 SQL
quit / exit / \q                         # 退出

二、 庫級操作

CREATE DATABASE `db_name` CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
SHOW DATABASES;
DROP DATABASE `db_name`;
USE `db_name`;
SELECT DATABASE();                       # 當前庫
SHOW TABLES;                             # 當前庫所有表

三、 表結構操作

CREATE TABLE `t` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DESC `t`;                                # 結構
SHOW CREATE TABLE `t`\G                  # 完整建表語句
ALTER TABLE `t` ADD COLUMN `age` TINYINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE `t` DROP COLUMN `age`;
ALTER TABLE `t` MODIFY `name` VARCHAR(100) NOT NULL;
RENAME TABLE `t` TO `t_new`;
DROP TABLE `t`;
TRUNCATE TABLE `t`;                      # 秒級清空+重置自增

四、 數據增刪改查

INSERT INTO `t` (`name`) VALUES ('Alice'),('Bob');
SELECT * FROM `t` WHERE `id` > 10 ORDER BY `id` DESC LIMIT 20;
UPDATE `t` SET `name`='Alice_v2' WHERE `id`=1;
DELETE FROM `t` WHERE `id`=1;            # 帶 WHERE
-- 大表刪除建議分批：DELETE ... WHERE id BETWEEN ? AND ? LIMIT 1000;

五、 索引操作

ALTER TABLE `t` ADD INDEX `idx_name` (`name`);
ALTER TABLE `t` ADD UNIQUE INDEX `uk_email` (`email`);
ALTER TABLE `t` ADD PRIMARY KEY (`id`);  # 仅无主键时
DROP INDEX `idx_name` ON `t`;
SHOW INDEX FROM `t`\G;

六、 權限與用戶管理

CREATE USER 'app'@'%' IDENTIFIED BY 'strong_pass';
GRANT SELECT,INSERT,UPDATE,DELETE ON `db`.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON `db`.* TO 'dba'@'%';
REVOKE INSERT ON `db`.* FROM 'app'@'%';
SHOW GRANTS FOR 'app'@'%';
DROP USER 'app'@'%';
FLUSH PRIVILEGES;

七、 備份與還原

# 邏輯備份
mysqldump -h host -u user -p --single-transaction --routines --triggers --events db_name > dump.sql
mysqldump -h host -u user -p -d db_name > schema_only.sql   # 只導結構

# 還原
mysql -h host -u user -p db_name < dump.sql
# 或交互式
SOURCE /path/dump.sql;

八、 運行狀態監控

SHOW PROCESSLIST;                        # 當前線程
SHOW ENGINE INNODB STATUS\G;             # InnoDB 內部狀態
SHOW GLOBAL STATUS LIKE 'Uptime';        # 運行時長
SHOW GLOBAL VARIABLES LIKE 'max_conn%';  # 參數查看
SET GLOBAL max_connections=1000;         # 熱調參數

九、 字符集檢查與修正

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
ALTER DATABASE `db` CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `t` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

十、 常用系統變量速查

建議將本文存為 mysql-cheatsheet.md，配合 alias mysqlc='cat ~/mysql-cheatsheet.md' 實現終端秒查。