MySQL 常用命令大全離線速查版：連線、DDL、DML、權限、備份、優化、索引、分析

MySQL 常用命令大全離線速查版：連線、DDL、DML、權限、備份、優化、索引、分析

彙整 MySQL 日常開發運維 95% 場景指令，分類標籤化，適合列印貼牆或存入筆記本離線查閱。

🔗 連線與基本資訊

mysql -h host -u user -p[pass] -P port -D db
mysql -u root -p
SHOW DATABASES; USE db; SHOW TABLES; DESC tbl;
SELECT VERSION(), USER(), DATABASE(), NOW();

🏗️ DDL：庫/表/欄位/索引/約束

-- 庫
CREATE DATABASE db CHARSET utf8mb4;
DROP DATABASE db;
ALTER DATABASE db CHARSET utf8mb4;

-- 表
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)) ENGINE=InnoDB;
DROP TABLE t;
TRUNCATE TABLE t;
RENAME TABLE t1 TO t2;
CREATE TABLE t2 LIKE t1;              -- 只複製結構
CREATE TABLE t2 SELECT * FROM t1;     -- 複製結構+資料 (無索引/約束/預設值)
CREATE TABLE t2 SELECT * FROM t1 WHERE 1<>1;  -- 只複製結構

-- 欄位
ALTER TABLE t ADD col VARCHAR(50);
ALTER TABLE t DROP COLUMN col;
ALTER TABLE t MODIFY col VARCHAR(100);
ALTER TABLE t CHANGE old_col new_col VARCHAR(100);

-- 索引
CREATE INDEX idx_name ON t(name);
CREATE UNIQUE INDEX uk_email ON t(email);
ALTER TABLE t ADD INDEX idx_name (name);
DROP INDEX idx_name ON t;
ALTER TABLE t DROP INDEX idx_name;

-- 約束
ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id);
ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t ADD CONSTRAINT fk_t_ref FOREIGN KEY (ref_id) REFERENCES ref(id);
ALTER TABLE t DROP FOREIGN KEY fk_t_ref;
ALTER TABLE t ADD CONSTRAINT uk_t_col UNIQUE (col);
ALTER TABLE t DROP INDEX uk_t_col;

📝 DML：增刪改查

INSERT INTO t (name) VALUES ('a'),('b');
INSERT INTO t SELECT name FROM t2;
UPDATE t SET name='c' WHERE id=1;
DELETE FROM t WHERE id=2;
SELECT * FROM t WHERE name LIKE 'a%' ORDER BY id DESC LIMIT 10;
SELECT COUNT(*) FROM t;
SELECT DISTINCT name FROM t;

🔐 權限與用戶

CREATE USER 'u'@'%' IDENTIFIED BY 'pass';
GRANT SELECT,INSERT ON db.* TO 'u'@'%';
GRANT ALL ON *.* TO 'admin'@'%' WITH GRANT OPTION;
REVOKE INSERT ON db.* FROM 'u'@'%';
SHOW GRANTS FOR 'u'@'%';
DROP USER 'u'@'%';
FLUSH PRIVILEGES;
-- 修改密碼
SET PASSWORD FOR 'u'@'%' = PASSWORD('newpass');  -- 5.6
ALTER USER 'u'@'%' IDENTIFIED BY 'newpass';      -- 5.7+

💾 備份與還原

mysqldump -u root -p --databases db > dump.sql
mysqldump -u root -p db tbl > dump.sql
mysqldump -u root -p -t db tbl > data_only.sql
mysql -u root -p db < dump.sql
mysql -u root -p -e "SOURCE /path/dump.sql;"

📥 匯入文字檔

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE t
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

⚡ 優化與分析

EXPLAIN SELECT * FROM t WHERE name='a';
ANALYZE TABLE t;
OPTIMIZE TABLE t;                    -- 整理碎片 (MyISAM/InnoDB)
CHECK TABLE t;
REPAIR TABLE t QUICK;                -- 快速重建索引 (MyISAM)
SHOW INDEX FROM t;
SHOW TABLE STATUS LIKE 't'\G

📊 索引使用判斷 (EXPLAIN 關鍵欄位)

🛠️ 索引優缺點與原則

• ✅ 加速查詢、排序、分組、關聯
• ❌ 降低寫入速度、佔用磁碟、增加優化器負擔
• 原則：高選擇性欄位、WHERE/JOIN/ORDER BY 常用欄位、組合索引遵循最左前綴

🧪 強制/忽略索引

SELECT * FROM t USE INDEX (idx_name) WHERE name='a';
SELECT * FROM t IGNORE INDEX (idx_bad) WHERE name='a';
SELECT * FROM t FORCE INDEX (idx_name) WHERE name='a';

📈 硬體調優方向

1. 增加記憶體 (InnoDB Buffer Pool 佔物理 70%)
2. 使用 NVMe SSD 減少 I/O 等待
3. 將繁忙庫分置不同物理磁碟 (非同磁碟不同分區)