MySQL 實戰手冊：字符集選型、備份恢復、SQL 腳本執行、常用操作速查

MySQL 實戰手冊：字符集選型、備份恢復、SQL 腳本執行、常用操作速查

記錄 Windows/Linux 下 MySQL 開發運維高頻場景與避坑指南，涵蓋字符集深度對比、備份恢復標準流程、SQL 腳本多種執行方式、Delete/Truncate/Drop 差異、核心 DDL/DML 語法速查。

一、字符集深度對比：char vs varchar vs text

建議：新項目全鏈路 utf8mb4，列類型優先 VARCHAR，超長文本用 TEXT 族，慎用 CHAR 除非長度極固定且高頻讀寫。

二、數據庫備份標準流程

1. 邏輯備份：mysqldump（中小庫首選）

# 單庫備份
mysqldump -u user_name -p database_name > /backup/db_$(date +%F).sql

# 多庫備份
mysqldump -u user_name -p --databases db1 db2 > /backup/multi_db.sql

# 全量備份（含用戶權限）
mysqldump -u user_name -p --all-databases --single-transaction --master-data=2 --routines --triggers --events > /backup/full_$(date +%F).sql

關鍵參數：

• --single-transaction：InnoDB 熱備一致性視圖，無需鎖表。
• --master-data=2：輸出 Binlog 位點註釋，便利 PITR。
• --routines --triggers --events：包含存儲過程/觸發器/事件。

2. 物理備份：xtrabackup（大庫/生產首選）

# 完全備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=root --password

# 增量備份 (基於上一次完全備份)
xtrabackup --backup --target-dir=/backup/inc_$(date +%F) --incremental-basedir=/backup/full_20240101

# 恢復準備
xtrabackup --prepare --target-dir=/backup/full_20240101
xtrabackup --prepare --target-dir=/backup/full_20240101 --incremental-dir=/backup/inc_20240102

# 複制回數據目錄 (需停庫)
xtrabackup --copy-back --target-dir=/backup/full_20240101

三、SQL 腳本執行三種方式

四、單引號轉義：插入文本含引號

-- 方法一：雙單引號轉義 (標準 SQL)
INSERT INTO t(content) VALUES ('It''s a test');

-- 方法二：反斜杠轉義 (MySQL 預設開啟 NO_BACKSLASH_ESCAPES 影響)
INSERT INTO t(content) VALUES ('It\'s a test');

-- 最佳實踐：使用參數化查詢 (PreparedStatement)，驅動層自動處理轉義，杜絕 SQL 注入。

五、MySQL 閃退/無法啟動：端口佔用排查

# 1. 查看錯誤日誌
cat /var/lib/mysql/$(hostname).err
# 關鍵錯誤: [ERROR] Do you already have another mysqld server running on port: 3306 ?

# 2. 定位佔用進程
netstat -tlnp | grep :3306
# 或
lsof -i :3306

# 3. 終止進程
kill -9 

# 4. 重啟服務
systemctl restart mysqld

六、Delete / Truncate / Drop 核心差異

選型：清空大表用 TRUNCATE；刪除部分數據用 DELETE WHERE (配合 LIMIT 分批)；廢棄表用 DROP。

七、核心 DDL/DML 語法速查卡

-- 庫操作
CREATE DATABASE db_name CHARSET utf8mb4;
USE db_name;
DROP DATABASE db_name;

-- 表操作
CREATE TABLE t (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(64) NOT NULL DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

ALTER TABLE t ADD COLUMN email VARCHAR(128) AFTER name;
ALTER TABLE t MODIFY name VARCHAR(128);
ALTER TABLE t DROP COLUMN email;
DROP TABLE t;

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

-- 增刪改查
INSERT INTO t(name) VALUES ('test'), ('batch');
SELECT * FROM t WHERE id = 1;
UPDATE t SET name='new' WHERE id=1;
DELETE FROM t WHERE id=1;

-- 自增調整
ALTER TABLE t AUTO_INCREMENT = 1000;

-- 條件更新技巧 (CASE WHEN)
UPDATE t SET status = CASE type WHEN 1 THEN 'A' WHEN 2 THEN 'B' ELSE 'C' END WHERE type IN (1,2,3);

八、Windows 環境變量配置建議

將 MySQL bin 目錄加入系統 Path，任意目錄直接運行 mysql、mysqldump、mysqladmin 等命令，無需進入安裝目錄。

總結

本手冊匯總了 MySQL 開發運維 80% 高頻場景的標準化操作。建議：

1. 收藏 字符集對比表 與 Delete/Truncate/Drop 差異表，決策零猶豫。
2. 建立 mysqldump + xtrabackup 混合備份策略，小庫邏輯備份便於遷移，大庫物理備份保證 RTO。
3. 養成 參數化查詢 肌肉記憶，從源頭杜絕轉義錯誤與注入風險。