Ubuntu MySQL 編碼設定 UTF-8 完整指南

配置檔位置

# apt 安裝預設路徑
/etc/mysql/my.cnf
# 或
/etc/mysql/mysql.conf.d/mysqld.cnf  # Ubuntu 16.04+

# 找不到時搜尋
find /etc -name "*.cnf" -type f

修改配置 (my.cnf)

[mysqld]
# 伺服器端字元集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# 初始化連線字元集
init_connect = 'SET NAMES utf8mb4'

[client]
# 客戶端預設字元集
default-character-set = utf8mb4

[mysql]
# mysql 指令列工具
default-character-set = utf8mb4

[mysqldump]
# 備份工具
default-character-set = utf8mb4

關鍵：為何用 utf8mb4 而非 utf8

生產環境強制使用 utf8mb4，避免儲存 Emoji、生僻字報錯 Incorrect string value。

重啟驗證

systemctl restart mysql
# 或
service mysql restart

# 登入驗證
mysql -u root -p -e "SHOW VARIABLES LIKE 'char%';"

預期正確輸出

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb4                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

資料庫/資料表層級修正 (遷移既有資料)

# 修改資料庫預設編碼
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# 修改資料表編碼 (含所有欄位)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 單一欄位修改
ALTER TABLE tbl_name MODIFY col_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

應用程式連線字串

# PHP PDO
$dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4";

# Python PyMySQL
conn = pymysql.connect(charset='utf8mb4')

# Java JDBC
jdbc:mysql://localhost:3306/db?characterEncoding=utf8mb4&useSSL=false

# Go sqlx
db, _ = sqlx.Open("mysql", "user:pass@tcp(localhost:3306)/db?charset=utf8mb4&parseTime=True")

常見坑點

• utf8mb4 索引長度限制：VARCHAR(255) 在 utf8mb4 下需 767 bytes，InnoDB 預設 767 限制，MySQL 5.7.7+ 可設 innodb_large_prefix=ON 放寬至 3072
• 舊版框架 (Laravel 5.3-、CodeIgniter 3) 需手動設定 charset
• 備份/還原需指定 --default-character-set=utf8mb4