MySQL 常用操作大全：Upsert、多表更新、索引、匯入匯出、時間函數、變數、預存程序、權限管理

MySQL 常用操作大全：Upsert、多表更新、索引、匯入匯出、時間函數、變數、預存程序、權限管理

整理開發與 DBA 日常高頻使用的 MySQL 技巧，含語法範例與最佳實踐備註。

1. INSERT ... ON DUPLICATE KEY UPDATE（Upsert）

主鍵或唯一鍵衝突時自動轉為更新，避免「先查後插/更新」的競態條件。

INSERT INTO myblog (id, title, ctime) VALUES (123, 'hello', NOW())
ON DUPLICATE KEY UPDATE title=VALUES(title), ctime=VALUES(ctime);

2. 多表關聯更新

將 table_b 的狀態同步至 table_a。

UPDATE table_a a
JOIN table_b b ON a.shop_id = b.shop_id
SET a.shop_status = b.group_status;

3. INSERT ... SELECT（跨表複製資料）

結構相同表之間快速遷移資料。

INSERT INTO myblog (col1, col2, ctime)
SELECT col1, col2, NOW() FROM blog_bak;

4. 索引管理

-- 建立索引（兩種等效寫法）
CREATE INDEX idx_name ON table_name (col1, col2);
ALTER TABLE table_name ADD INDEX idx_name (col1, col2);

-- 刪除索引
DROP INDEX idx_name ON table_name;
ALTER TABLE table_name DROP INDEX idx_name;

提示：聯合索引遵循「最左前綴」原則；EXPLAIN 驗證索引是否生效。

5. SHOW 常用指令

SHOW WARNINGS;           -- 查看最近語句警告
SHOW ERRORS;             -- 查看最近語句錯誤
SHOW TABLES LIKE '%log%';-- 模糊查表名
SHOW FULL FIELDS FROM your_table; -- 含註解、字符集等完整欄位資訊

6. 資料匯入匯出

# 1. 僅匯出結構
mysqldump -h localhost -uroot -ppasswd -d database > dump_struct.sql

# 2. 匯出結構+資料
mysqldump -h localhost -uroot -ppasswd database > dump_all.sql

# 3. 單表結構
mysqldump -h localhost -uroot -ppasswd -d database table > dump_table_struct.sql

# 4. 單表結構+資料
mysqldump -h localhost -uroot -ppasswd database table > dump_table_all.sql

# 5. 匯入
mysql -uroot -ppasswd -h localhost database < dump.sql

# 6. 清空表（重置 auto_increment）
TRUNCATE TABLE table_name;

7. 時間處理函數

-- 格式化輸出
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS now;

-- 日期運算
SELECT DATE_SUB(NOW(), INTERVAL 10 DAY) AS ten_days_ago;

-- 多欄位排序分頁
SELECT * FROM test_table
WHERE status = 1
ORDER BY col_a DESC, col_b DESC, col_c ASC
LIMIT 100;

注意：INTERVAL 單位用單數（DAY, MONTH, HOUR），非複數。

8. 使用者變數

會話級變數，以 @ 開頭，生命週期隨連線結束。

SET @a = 100;
SET @a := 200;
SELECT @a := 300;  -- 賦值並返回 300

9. 預存程序基礎範例

修改分隔符避免與語句結束符 ; 衝突。

DELIMITER $$
CREATE PROCEDURE p()
BEGIN
  SELECT * FROM ttt;
END$$
DELIMITER ;

CALL p();

10. 解決中文亂碼（設定 UTF-8）

修改 /etc/my.cnf（或 /etc/mysql/my.cnf）在對應區段加入：

[client]
default-character-set=utf8

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
collation_server=utf8_general_ci

[mysqld_safe]
default-character-set=utf8

重啟 MySQL 後驗證：SHOW VARIABLES LIKE 'character%';

11. 權限管理

-- 建立用戶（僅允許本地連線）
CREATE USER 'phplamp'@'localhost' IDENTIFIED BY '1234';
-- 或舊版寫法
INSERT INTO mysql.user (Host, User, Password) VALUES ('localhost', 'phplamp', PASSWORD('1234'));

-- 授權
GRANT ALL PRIVILEGES ON t_sweet.* TO 'sweet_b'@'localhost' IDENTIFIED BY 'yourpassword';

-- 生效
FLUSH PRIVILEGES;

安全建議：生產環境避免使用 % 主機通配符，遵循最小權限原則。