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

MySQL 常用命令大全：mysqldump 備份/匯出、mysql 匯入/查詢、權限設定、字元集檢視與結構變更完整速查

編輯：MySQL 資料庫相關知識

日常 MySQL 運維開發高頻指令彙整：mysqldump 多種備份模式、mysql 客戶端查詢/匯入/匯出、權限授予、字元集檢視與修改、結構變更 (增刪欄位、索引、重命名)，附參數詳解與實戰範例。

mysqldump 備份與匯出

場景	指令範例
完整備份 (結構+資料)	`mysqldump -h ip -u user -p --opt dbname > backup.sql`
僅匯出結構 (無資料)	`mysqldump -h ip -d -u user -p dbname > struct.sql`
匯出單表	`mysqldump --opt --add-drop-table -u user -p dbname tbl > tbl.sql`
指定字元集 (防亂碼)	`mysqldump --default-character-set=utf8 -u user -p dbname > backup.sql`
自動輸入密碼 (腳本用)	`mysqldump -u user --password=pwd --opt dbname > backup.sql`
壓縮輸出 (管道)	`mysqldump -u user -p dbname \| gzip > backup.sql.gz`

mysql 客戶端常用操作

-- 連線並指定預設資料庫
mysql -h ip -u user -p dbname

-- 執行 SQL 檔案匯入
mysql -h ip -u user -p dbname < backup.sql
-- 或在 mysql> 提示符下
SOURCE /path/backup.sql;

-- 將查詢結果輸出到檔案 (Tab 分隔)
SELECT title FROM book INTO OUTFILE '/tmp/outfile.txt';

-- 重複資料查找/刪除/去重
SELECT * FROM people WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1);
SELECT * FROM phome_ecms_wma WHERE title IN (SELECT DISTINCT title FROM phome_ecms_wma);
-- 匯出去重 -> 清空表 -> 匯入 (REPLACE 避免主鍵衝突)
SELECT *, COUNT(DISTINCT title) INTO OUTFILE '/tmp/table.bak' FROM phome_ecms_wma GROUP BY title;
DELETE FROM phome_ecms_wma;
LOAD DATA INFILE '/tmp/table.bak' REPLACE INTO TABLE phome_ecms_wma CHARACTER SET utf8;

字元集檢視與修改

-- 檢視目前字元集設定
SHOW VARIABLES LIKE 'character_set%';

-- 修改資料庫預設字元集
ALTER DATABASE dbname DEFAULT CHARACTER SET utf8;

-- 修改表預設字元集 (僅影響新欄位)
ALTER TABLE tbl DEFAULT CHARACTER SET utf8;

-- **將現有欄位資料一併轉換** (推薦)
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8;

-- 特殊情況：欄位實際編碼與定義不符 (雙重轉換法)
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

結構變更 (DDL)

操作	指令
修改欄位型別/預設值	`ALTER TABLE tbl CHANGE old_col new_col TYPE DEFAULT 'val';`
新增欄位	`ALTER TABLE tbl ADD COLUMN col_name TYPE DEFAULT 'val';`
刪除欄位	`ALTER TABLE tbl DROP COLUMN col_name;`
重命名表	`ALTER TABLE t1 RENAME t2;`
新增索引	`ALTER TABLE tbl ADD INDEX idx_name (col1, col2);`
新增主鍵	`ALTER TABLE tbl ADD PRIMARY KEY (id);`
新增唯一索引	`ALTER TABLE tbl ADD UNIQUE uk_name (col);`
刪除索引	`ALTER TABLE tbl DROP INDEX idx_name;`

遠程存取授權

GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'192.168.1.9' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

PHP 連線設定字元集 (避免亂碼)

$conn = mysql_connect("localhost", "root", "");
mysql_query("SET NAMES 'utf8'");  -- 或 'gbk' 視情況
-- 建議改用 mysqli/PDO:
$mysqli = new mysqli("localhost", "root", "", "dbname");
$mysqli->set_charset("utf8");

注意： mysql_* 函式已棄用 (PHP 5.5+) 並移除 (PHP 7+)，生產環境**必須使用 mysqli 或 PDO**。

收藏此速查表，日常 MySQL 操作不再需要到處搜尋指令語法。