MySQL 常用管理指令速查：mysqldump、查詢匯出、重複資料處理、編碼檢查、DDL 操作

整理日常 MySQL 運維最常用的指令與 SQL 片段，涵蓋備份/還原、結構變更、資料清理、權限設定等場景。

mysqldump 備份與匯出

# 完整備份 (結構+資料)
mysqldump -h host -u user -p db_name > backup.sql

# 僅結構
mysqldump -h host -u user -p -d db_name > structure.sql

# 單一資料表
mysqldump -h host -u user -p db_name table_name > table.sql

# 免互動密碼 (腳本用)
mysqldump -h host -u user --password=pwd db_name > backup.sql

查詢結果匯出檔案

SELECT title FROM book INTO OUTFILE '/tmp/outfile.txt';

重複資料處理

-- 找出重複 (依 peopleId)
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);

-- 刪除重複 (保留第一筆) - 以 title 判斷
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 TABLE tbl_name CHANGE old_col new_col DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00';

-- 新增欄位
ALTER TABLE host ADD ks_mac VARCHAR(100);

-- 刪除欄位
ALTER TABLE tbl_name DROP COLUMN col_name;

-- 重命名資料表
ALTER TABLE t1 RENAME t2;

索引操作

-- 一般索引
ALTER TABLE tbl_name ADD INDEX idx_name (col1, col2);

-- 主鍵
ALTER TABLE tbl_name ADD PRIMARY KEY (id);

-- 唯一索引
ALTER TABLE tbl_name ADD UNIQUE uk_card (cardnumber);

-- 刪除索引
ALTER TABLE tbl_name DROP INDEX idx_name;

遠端存取授權

GRANT ALL PRIVILEGES ON db_name.* TO 'root'@'192.168.1.9' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;