MySQL 常用命令大全：從登錄連接、庫表操作、用戶授權到備份還原的完整速查手冊

連接與基礎操作

# 登錄 (本地)
mysql -u root -p
# 登錄 (遠程)
mysql -h 192.168.1.100 -u root -p
# 登錄 (指定端口/協議)
mysql -h localhost -P 3306 --protocol=tcp -u root -p

庫/表結構管理

-- 庫
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE db_name;
DROP DATABASE db_name;

-- 表
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL) ENGINE=InnoDB;
SHOW TABLES;
DESC t;              -- 或 SHOW COLUMNS FROM t;
SHOW CREATE TABLE t;
ALTER TABLE t ADD COLUMN age TINYINT UNSIGNED DEFAULT 0;
ALTER TABLE t MODIFY COLUMN name VARCHAR(100);
ALTER TABLE t DROP COLUMN age;
ALTER TABLE t RENAME TO t_new;
DROP TABLE t;

數據增刪改查 (DML)

-- 插入
INSERT INTO t (name) VALUES ('Alice'), ('Bob');
INSERT INTO t (name) SELECT name FROM t_old;

-- 查詢
SELECT * FROM t;
SELECT id, name FROM t WHERE name LIKE 'A%' ORDER BY id DESC LIMIT 10;
SELECT COUNT(*), SUM(id) FROM t;
SELECT DISTINCT name FROM t;

-- 更新
UPDATE t SET name='AliceUpdated' WHERE id=1;

-- 刪除
DELETE FROM t WHERE id=1;
TRUNCATE TABLE t;  -- 重置自增 ID、速度快、不可回滾

索引與約束

-- 索引
ALTER TABLE t ADD INDEX idx_name (name);
ALTER TABLE t ADD UNIQUE INDEX uk_name (name);
ALTER TABLE t ADD PRIMARY KEY (id);  -- 若無 PK
ALTER TABLE t DROP INDEX idx_name;

-- 外鍵 (InnoDB)
ALTER TABLE child ADD CONSTRAINT fk_parent FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE;

用戶與權限管理

-- 創建用戶 (5.7+)
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongP@ssw0rd';
-- 或兼容舊版
INSERT INTO mysql.user (Host,User,Password) VALUES ('%','app',PASSWORD('pwd'));
FLUSH PRIVILEGES;

-- 授權
GRANT SELECT,INSERT,UPDATE,DELETE ON db_name.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON db_name.* TO 'admin'@'10.0.0.%';
FLUSH PRIVILEGES;

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

-- 撤銷
REVOKE INSERT ON db_name.* FROM 'app'@'%';

-- 修改密碼 (5.7+)
ALTER USER 'app'@'%' IDENTIFIED BY 'NewStrongPwd';
-- 或
SET PASSWORD FOR 'app'@'%' = PASSWORD('NewPwd');

-- 刪除用戶
DROP USER 'app'@'%';

備份與還原

# 邏輯備份 (mysqldump)
# 全庫
mysqldump -u root -p --single-transaction --routines --triggers --master-data=2 --all-databases > full_backup.sql
# 單庫
mysqldump -u root -p --single-transaction db_name > db_backup.sql
# 單表
mysqldump -u root -p db_name table_name > table_backup.sql
# 只導結構
mysqldump -u root -p -d db_name > schema_only.sql

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

# 物理備份 (xtrabackup) - 推薦生產
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx
xtrabackup --prepare --target-dir=/backup/full

字符集與編碼

-- 查看
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- 修改庫/表/列編碼 (慎重，需轉換數據)
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE t CHANGE name name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

性能診斷與調優

-- 慢查詢
SHOW VARIABLES LIKE 'slow_query%';
SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=1;

-- 執行計劃
EXPLAIN SELECT * FROM t WHERE name='Alice';
EXPLAIN FORMAT=JSON SELECT ...;  -- 5.6+ 詳細 JSON

-- 狀態監控
SHOW GLOBAL STATUS LIKE 'Innodb_%';
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

-- 表大小統計
SELECT TABLE_SCHEMA, TABLE_NAME,
       ROUND(DATA_LENGTH/1024/1024,2) AS data_mb,
       ROUND(INDEX_LENGTH/1024/1024,2) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='db_name'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC;

常用技巧片段

-- 查找重複記錄 (按 name)
SELECT name, COUNT(*) FROM t GROUP BY name HAVING COUNT(*) > 1;

-- 刪除重複 (保留最小 id)
DELETE t1 FROM t t1 JOIN t t2 ON t1.name=t2.name AND t1.id > t2.id;

-- 隨機抽樣
SELECT * FROM t ORDER BY RAND() LIMIT 10;

-- 分頁優化 (延遲關聯)
SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 100000, 20) tmp USING (id);

-- 導出 CSV
SELECT * FROM t INTO OUTFILE '/tmp/t.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

-- 遠程授權
GRANT ALL PRIVILEGES ON db.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'pwd';
FLUSH PRIVILEGES;

版本差異提示

• MySQL 5.7：PASSWORD() 棄用，ALTER USER ... IDENTIFIED BY、CREATE USER、validate_password 插件。
• MySQL 8.0：caching_sha2_password 預設認證、utf8mb4 預設字符集、CTE/Window Functions、角色 (ROLE)、函數索引、即時加列 (Instant ADD COLUMN)。
• MariaDB 10.x：語法高度兼容，SEQUENCE 引擎、COLUMNSTORE 列存引擎、MAX() 聚合優化。