萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 實戰踩坑指南:權限、編碼、連線與工具篇

MySQL Workbench 無法連線/報錯

現象:Workbench 連線報權限錯誤、讀寫 /var/folders/ 異常。

根因:多半是 MySQL 服務端本身已損壞(錯誤日誌滿是錯誤),而非 Workbench 問題。

解決:重新安裝官方 MySQL 社區版,掛載舊資料目錄,避免使用整合包(XAMPP/MAMP)自帶的 MySQL 元件。

權限系統核心陷阱

1. localhost ≠ 127.0.0.1

MySQL 權限表中 host 欄位嚴格區分:

  • localhost:Unix Socket 連線
  • 127.0.0.1:TCP/IP 回環連線
  • %:任意主機(含上述兩者)

最佳實踐:為同一用戶建立三條記錄:

CREATE USER 'app'@'localhost' IDENTIFIED BY 'pwd';
CREATE USER 'app'@'127.0.0.1' IDENTIFIED BY 'pwd';
CREATE USER 'app'@'%' IDENTIFIED BY 'pwd';
GRANT ALL ON mydb.* TO 'app'@'localhost';
GRANT ALL ON mydb.* TO 'app'@'127.0.0.1';
GRANT ALL ON mydb.* TO 'app'@'%';
FLUSH PRIVILEGES;

2. JDBC 連線 localhost 被拒

JDBC 驅動解析 localhost 為本機真實 IP,若該 IP 不在權限表中即被拒。解決:改用 127.0.0.1 或在 URL 加 ?allowPublicKeyRetrieval=true&useSSL=false。

字符集編碼徹底解決

現象

插入中文報 ERROR 1366 (HY000): Incorrect string value,查詢顯示亂碼。

層層排查清單

  1. 服務端配置(my.cnf):
    [mysqld]
    character_set_server=utf8mb4
    collation_server=utf8mb4_general_ci
  2. 資料庫層級:
    ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
  3. 資料表層級(關鍵步驟):
    ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

    CONVERT TO 會同時轉換欄位字符集,單純 DEFAULT CHARACTER SET 僅影響新增欄位。

  4. 欄位層級驗證:
    SHOW FULL COLUMNS FROM tbl_name;
    確認 Collation 皆為 utf8mb4_general_ci。
  5. 客戶端連線:
    -- 連線後立即執行
    SET NAMES utf8mb4;
    -- 或 JDBC URL 附加
    ?characterEncoding=utf8mb4&connectionCollation=utf8mb4_general_ci

為何選 utf8mb4 而非 utf8?

MySQL 的 utf8 實為 utf8mb3,僅支援 3 位元組字符,**無法儲存 Emoji 與部分生僻字**。utf8mb4 完整支援 Unicode BMP 及增補平面。

其他常見坑點速查

問題解決方案
auto_increment 耗盡 改用 BIGINT,或定期重置:ALTER TABLE t AUTO_INCREMENT = 1
TIMESTAMP 2038 年問題 改用 DATETIME(佔 8 bytes,範圍 1001-9999 年)
GROUP BY 隨機結果 MySQL 5.7+ 預設 ONLY_FULL_GROUP_BY,需明確列出非聚合欄位
外鍵導入失敗 匯入前 SET FOREIGN_KEY_CHECKS=0; 導入後 =1
主從同步延遲 檢查 Seconds_Behind_Master,優化從庫 SQL 執行緒、啟用並行複寫

總結

MySQL 看似簡單,實則坑點眾多。核心原則:統一字符集用 utf8mb4、權限精確到 host、連線參數顯式指定、DDL 變更謹慎測試。遇到詭異問題優先檢查錯誤日誌,往往能直指核心原因。

copyright © 萬盛學電腦網 all rights reserved