阿京小站

今天裝了虛擬Linux主機ubuntu

一、MySQL 字元集(Character Set)基礎
1. 什麼是字元集？
字元集是資料庫用來儲存和處理文字的編碼系統
常見字元集：
latin1：西歐語系
utf8：UTF-8編碼(MySQL中的"utf8"是偽UTF-8，最多只支援3位元組)
utf8mb4：真正的UTF-8(支援4位元組，包含emoji等特殊字元)
big5：繁體中文
gb2312/gbk：簡體中文

2. 校對規則(Collation)
定義字元如何比較和排序
命名規則：字元集_語言_ci/cs/bin
ci：大小寫不敏感
cs：區分大小寫
bin：二進位比較
範例：utf8mb4_unicode_ci、utf8mb4_general_ci

二、字元集相關操作
1. 查看可用字元集與校對規則
SHOW CHARACTER SET;
SHOW COLLATION LIKE 'utf8mb4%';
2. 設定字元集（不同層級）
-- 伺服器層級（通常在my.cnf設定）
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
-- 資料庫層級CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 表格層級
CREATE TABLE mytable (
    id INT,
    name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 欄位層級
CREATE TABLE mytable (
    id INT,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

三、資料庫操作與字元集
1. 建立資料庫時指定字元集
CREATE DATABASE mydatabase 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;
2. 修改現有資料庫的字元集
ALTER DATABASE mydatabase 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;
3. 匯入/匯出資料時的字元集注意事項
-- 匯出時指定字元集mysqldump -u username -p --default-character-set=utf8mb4 mydatabase > backup.sql
-- 匯入時指定字元集mysql -u username -p --default-character-set=utf8mb4 mydatabase < backup.sql

四、常見問題與解決方案
1. 亂碼問題
原因：客戶端、連接層、資料庫層字元集不一致
解決方案：
-- 設定連接字元集SET NAMES 'utf8mb4';
-- 或在連接字串中指定jdbc:mysql://localhost/mydb?useUnicode=true&characterEncoding=utf8
2. emoji儲存問題
原因：utf8只能儲存3位元組字元，emoji需要4位元組
解決方案：使用utf8mb4字元集
3. 效能考量
utf8mb4比utf8佔用更多空間
校對規則影響排序效能：_general_ci比_unicode_ci快

五、最佳實踐建議
統一使用utf8mb4：支援所有Unicode字元，包括emoji
應用層與資料庫層設定一致：避免轉碼開銷
新專案從開始就設定正確字元集：後期修改成本高
備份時注意字元集設定：避免備份/還原時出現亂碼

六、實用查詢
-- 查看當前資料庫的字元集設定SELECT @@character_set_database, @@collation_database;
-- 查看表格的字元集設定SHOW CREATE TABLE mytable;
-- 查看連接相關的字元集變數
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
預習時可以實際操作這些指令，觀察不同設定下的行為差異，這將幫助您更深入理解MySQL的字元集處理機制。

一、MySQL 儲存引擎 (Storage Engines)

1. 常見儲存引擎比較

2. 儲存引擎操作指令

-- 查看支援的引擎
SHOW ENGINES;

-- 查看表的引擎
SHOW TABLE STATUS LIKE 'table_name';

-- 建立表時指定引擎
CREATE TABLE my_table (
    id INT PRIMARY KEY
) ENGINE=InnoDB;

-- 修改表的引擎
ALTER TABLE my_table ENGINE=InnoDB;

二、資料型態 (Data Types)

1. 數值型態

2. 字串型態

3. 日期時間型態

4. 二進位型態

三、型態選擇最佳實踐

1. 整數選擇原則：

   • 根據數據範圍選擇最小夠用的型態

   • 自增ID用UNSIGNED INT(約42億)或BIGINT

2. 字串選擇原則：

   • 固定長度用CHAR(如MD5哈希)

   • 變動長度用VARCHAR

   • 避免過大長度設定(會影響記憶體分配)

3. 時間型態選擇：

   • 只需要日期用DATE

   • 需要時間用DATETIME

   • 需要自動更新用TIMESTAMP

四、儲存引擎選擇策略

1. InnoDB 使用時機：

   • 需要事務處理

   • 需要外鍵約束

   • 高併發寫入場景

2. MyISAM 使用時機：

   • 讀密集型應用

   • 不需要事務

   • 需要全文索引(MySQL 5.6前)

3. MEMORY 引擎注意：

   • 伺服器重啟後數據消失

   • 不支援TEXT/BLOB型態

五、實用查詢範例

-- 建立包含各種型態的表格
CREATE TABLE sample_data (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    age TINYINT UNSIGNED,
    balance DECIMAL(10,2) DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    profile TEXT,
    avatar BLOB
) ENGINE=InnoDB;

-- 查看欄位型態資訊
DESCRIBE sample_data;

-- 查詢型態相關系統變數
SHOW VARIABLES LIKE 'storage_engine%';

六、常見問題解決

1. int(11)中的11是什麼意思？

   • 只是顯示寬度，不影響儲存範圍

   • 實際範圍由型態本身決定

2. VARCHAR(255)還是TEXT？

   • VARCHAR(255)會占用額外1字元記錄長度

   • 超過255字元建議使用TEXT

3. DATETIME vs TIMESTAMP？

   • TIMESTAMP會自動轉換時區

   • DATETIME儲存範圍更大

預習時建議實際建立各種型態的表格並插入測試數據，觀察不同型態的儲存行為和限制，這將幫助您更深入理解MySQL的資料型態系統。