您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 常用命令大全：從連接管理到備份恢復的完整速查手冊

編輯：MySQL 資料庫相關知識

連接與基礎管理

登入 MySQL

# 本地連接
mysql -u root -p

# 指定主機/端口
mysql -h 192.168.1.100 -P 3306 -u root -p

# 執行單條 SQL 並退出
mysql -u root -p -e "SHOW DATABASES;"

# 讀取密碼文件（避免明文歷史記錄）
mysql --defaults-extra-file=~/.mysql.cnf

退出與幫助

exit;  -- 或 quit、\q
help;  -- 或 \h、?  顯示幫助
\c     -- 取消當前輸入

資料庫操作 (DDL)

創建/刪除/查看

-- 創建（指定字符集排序規則）
CREATE DATABASE IF NOT EXISTS mydb 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 刪除
DROP DATABASE IF EXISTS mydb;

-- 列出所有
SHOW DATABASES;

-- 切換當前庫
USE mydb;

-- 查看當前庫
SELECT DATABASE();

字符集修改

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

表操作 (DDL)

創建表範例

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用戶名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '郵箱',
    password_hash CHAR(60) NOT NULL COMMENT 'bcrypt 哈希',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1正常 0禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';

修改表結構

-- 新增欄位
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改欄位類型/屬性
ALTER TABLE users MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1;

-- 重命名欄位
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20);

-- 刪除欄位
ALTER TABLE users DROP COLUMN mobile;

-- 新增索引
ALTER TABLE users ADD INDEX idx_email (email);

-- 刪除索引
ALTER TABLE users DROP INDEX idx_email;

-- 重命名表
RENAME TABLE users TO app_users;

數據操作 (DML)

插入數據

-- 單行
INSERT INTO users (username, email, password_hash) VALUES ('john', 'john@example.com', '$2y$10$...');

-- 批次（高效）
INSERT INTO users (username, email, password_hash) VALUES 
    ('alice', 'alice@example.com', '$2y$10$...'),
    ('bob', 'bob@example.com', '$2y$10$...');

-- INSERT IGNORE（主鍵/唯一衝突忽略）
INSERT IGNORE INTO users (username, email) VALUES ('john', 'john@example.com');

-- ON DUPLICATE KEY UPDATE（衝突更新）
INSERT INTO users (username, email, login_count) VALUES ('john', 'john@example.com', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1, updated_at = NOW();

查詢數據

-- 基礎查詢
SELECT id, username, email FROM users WHERE status = 1;

-- 分頁（高效寫法：延遲關聯）
SELECT u.* FROM users u
JOIN (SELECT id FROM users WHERE status = 1 ORDER BY id LIMIT 100000, 20) t USING (id);

-- 聚合統計
SELECT status, COUNT(*) AS cnt FROM users GROUP BY status;

更新與刪除

-- 更新
UPDATE users SET status = 0 WHERE id = 123;

-- 軟刪除（推薦）
UPDATE users SET status = 0, deleted_at = NOW() WHERE id = 123;

-- 硬刪除（謹慎）
DELETE FROM users WHERE id = 123;

用戶與權限管理

-- 創建用戶
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass#123';

-- 授權（最小權限原則）
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';

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

-- 撤銷權限
REVOKE DELETE ON mydb.* FROM 'app_user'@'%';

-- 修改密碼
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass#456';

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

備份與恢復

mysqldump 邏輯備份

# 全庫備份（含存儲過程/觸發器/事件）
mysqldump -u root -p --single-transaction --routines --triggers --events --all-databases > full_backup.sql

# 單庫備份
mysqldump -u root -p --single-transaction mydb > mydb_backup.sql

# 單表備份
mysqldump -u root -p --single-transaction mydb users > users_backup.sql

# 僅結構
mysqldump -u root -p --no-data --single-transaction mydb > schema_only.sql

# 壓縮備份
mysqldump -u root -p --single-transaction mydb | gzip > mydb_backup_$(date +%F).sql.gz

恢復數據

# 全量恢復
mysql -u root -p < full_backup.sql

# 指定庫恢復
mysql -u root -p mydb < mydb_backup.sql

# 並行恢復（大數據量，需 mydumper/myloader）
# myloader -d ./backup_dir -t 8

效能診斷常用命令

-- 查看進程
SHOW FULL PROCESSLIST;

-- 殺死長時間查詢
KILL 12345;

-- 慢查詢日誌分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 表大小統計
SELECT table_schema, table_name, 
       ROUND(data_length/1024/1024,2) AS data_mb,
       ROUND(index_length/1024/1024,2) AS idx_mb
FROM information_schema.tables 
WHERE table_schema = 'mydb'
ORDER BY data_length DESC;

-- 索引使用情況
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

關鍵系統變量查看/修改

-- 查看
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 運行時動態修改（重啟失效）
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB
SET GLOBAL max_connections = 1000;

-- 永久修改需編輯 my.cnf