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

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

涵蓋服務管理、客戶端連接、庫表操作、增刪改查、用戶權限、索引維護、事務控制、導入導出、正則表達式等高頻命令，適合開發、運維、面試快速查閱。

一、服務管理

二進制啟動調試：mysqld --console（前台輸出啟動日志，Ctrl+C 停止）

二、客戶端連接

# 標準連接
mysql -h host -P port -u user -p'password' -D dbname

# 常用簡寫
mysql -uroot -p          # 本地root，交互輸入密碼
mysql -uroot -p123456    # 密碼直接跟在-p後（無空格，生產慎用）
mysql --ssl-mode=REQUIRED ...  # 強制SSL

# 退出
exit / quit / \q

三、庫表結構操作 (DDL)

1. 數據庫

SHOW DATABASES;
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE db_name;
DROP DATABASE db_name;

2. 表結構

SHOW TABLES;
SHOW TABLES LIKE 'prefix_%';

CREATE TABLE tbl (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL DEFAULT '',
    age TINYINT UNSIGNED NOT NULL DEFAULT 0,
    email VARCHAR(100) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_email (email),
    KEY idx_age (age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SHOW CREATE TABLE tbl;          -- 顯示建表語句
DESC tbl; / SHOW COLUMNS FROM tbl;  -- 表結構
SHOW INDEX FROM tbl;            -- 索引詳情
SHOW TABLE STATUS LIKE 'tbl';   -- 表統計信息（行數、大小、自增值等）

3. 修改表結構 (ALTER)

-- 添加列
ALTER TABLE tbl ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '' AFTER email;
ALTER TABLE tbl ADD COLUMN status TINYINT NOT NULL DEFAULT 1 FIRST;

-- 修改列
ALTER TABLE tbl MODIFY COLUMN name VARCHAR(60) NOT NULL DEFAULT '';
ALTER TABLE tbl CHANGE COLUMN age age SMALLINT UNSIGNED NOT NULL DEFAULT 0;

-- 刪除列
ALTER TABLE tbl DROP COLUMN phone;

-- 索引操作
ALTER TABLE tbl ADD INDEX idx_name (name);
ALTER TABLE tbl ADD UNIQUE INDEX uk_phone (phone);
ALTER TABLE tbl DROP INDEX idx_name;
ALTER TABLE tbl ADD PRIMARY KEY (id);  -- 僅表無PK時

-- 重命名表
ALTER TABLE tbl RENAME TO new_tbl;

-- 修改表選項
ALTER TABLE tbl ENGINE=InnoDB;
ALTER TABLE tbl AUTO_INCREMENT=10000;
ALTER TABLE tbl CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

四、數據操作 (DML)

1. 插入

INSERT INTO tbl (name, age, email) VALUES ('張三', 25, 'zhang@example.com');
INSERT INTO tbl (name, age, email) VALUES 
    ('李四', 30, 'li@example.com'),
    ('王五', 28, 'wang@example.com');  -- 批量插入，性能更高

INSERT INTO tbl SELECT * FROM other_tbl;           -- 表間複製
INSERT INTO tbl (name) SELECT name FROM other_tbl; -- 指定列複製

2. 查詢

SELECT id, name, age FROM tbl WHERE age > 20 ORDER BY age DESC LIMIT 10;
SELECT * FROM tbl WHERE name LIKE '張%';           -- 模糊查詢
SELECT * FROM tbl WHERE age IN (20, 25, 30);       -- IN 列表
SELECT * FROM tbl WHERE age BETWEEN 20 AND 30;     -- 範圍
SELECT * FROM tbl WHERE email IS NULL;             -- NULL 判斷
SELECT COUNT(*) AS total FROM tbl;                 -- 聚合
SELECT age, COUNT(*) FROM tbl GROUP BY age HAVING COUNT(*) > 1; -- 分組過濾

3. 更新

UPDATE tbl SET age = 26, email = 'new@example.com' WHERE id = 1;
-- ⚠️ 必須帶 WHERE，否則全表更新！

4. 刪除

DELETE FROM tbl WHERE id = 1;
-- ⚠️ 必須帶 WHERE！
TRUNCATE TABLE tbl;  -- 清空表，重置自增ID，不可回滾，速度極快

五、用戶與權限 (DCL)

-- 創建用戶
CREATE USER 'app'@'%' IDENTIFIED BY 'strong_password';
CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'pwd';

-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'%';
GRANT SELECT ON *.* TO 'readonly'@'192.168.1.%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

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

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

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

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

-- 刷新權限
FLUSH PRIVILEGES;

六、索引維護

SHOW INDEX FROM tbl;
CREATE INDEX idx_name ON tbl (name);
CREATE UNIQUE INDEX uk_email ON tbl (email);
ALTER TABLE tbl ADD INDEX idx_age (age);
DROP INDEX idx_name ON tbl;

-- 分析表統計信息（優化器依賴）
ANALYZE TABLE tbl;
-- 優化表（碎片整理、回收空間）
OPTIMIZE TABLE tbl;

七、事務控制

-- 開啟事務
START TRANSACTION;  -- 或 BEGIN

-- 提交
COMMIT;

-- 回滾
ROLLBACK;

-- 保存點
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- 自動提交控制
SET autocommit = 0;  -- 關閉自動提交
SET autocommit = 1;  -- 開啟（默認）

ACID 原則：僅 InnoDB 支持事務。innodb_flush_log_at_trx_commit=1 保證持久性。

八、導入導出

1. mysqldump 邏輯備份

# 全庫備份
mysqldump -uroot -p --single-transaction --routines --triggers --events --all-databases > full_backup.sql

# 單庫備份（推薦生產）
mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers db_name > db_backup.sql

# 單表備份
mysqldump -uroot -p db_name tbl_name > tbl_backup.sql

# 只導出結構
mysqldump -uroot -p -d db_name > structure.sql

# 只導出數據
mysqldump -uroot -p -t db_name > data.sql

2. 文本文件導入導出

-- 導出到文件（服務器端，需 FILE 權限）
SELECT * FROM tbl INTO OUTFILE '/tmp/tbl.txt' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

-- 從文件導入（服務器端）
LOAD DATA INFILE '/tmp/tbl.txt' INTO TABLE tbl 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

-- 本地文件導入（客戶端，需 local_infile=1）
LOAD DATA LOCAL INFILE 'local.txt' INTO TABLE tbl ...

九、正則表達式 (REGEXP)

SELECT * FROM tbl WHERE name REGEXP '^張';           -- 以張開頭
SELECT * FROM tbl WHERE name REGEXP '李$';           -- 以李結尾
SELECT * FROM tbl WHERE name REGEXP '張|李|王';      -- 包含任一
SELECT * FROM tbl WHERE name REGEXP '^[趙錢孫李]';   -- 首字屬於集合
SELECT * FROM tbl WHERE name REGEXP '[0-9]{3,}';     -- 含3位以上數字

十、實用系統命令

# 查看版本
mysqladmin -uroot -p version
mysql -V

# 查看進程
SHOW FULL PROCESSLIST;
-- 或
mysqladmin -uroot -p processlist

# 殺死進程
KILL 12345;

# 查看狀態變量
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Queries';

# 查看配置
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE '%buffer%';

# 當前用戶/數據庫
SELECT USER(), DATABASE();

十一、總結：高頻命令肌肉記憶表

建議收藏、打印、貼在工位，養成「不看文檔也能敲出核心命令」的肌肉記憶。