MySQL 實現漢字拼音首字母查詢：自定義函數 + 映射表 + 觸發器完整實戰

核心架構：映射表 + 標量函數 + 虛擬/實體欄位索引

將 GBK 區位碼轉換邏輯封裝為 `hzcode()` 函數，配合 `pinyin_map` 映射表，實現 SQL 層面首字母提取，支援 5.7+ 所有版本。

步驟一：建立拼音首字母映射表 (GBK 區位碼區間)

CREATE TABLE `pinyin_map` (
    `py` CHAR(1) NOT NULL COMMENT '首字母 A-Z',
    `zone_start` SMALLINT UNSIGNED NOT NULL COMMENT '區碼起始',
    `zone_end` SMALLINT UNSIGNED NOT NULL COMMENT '區碼結束',
    `pos_start` SMALLINT UNSIGNED NOT NULL COMMENT '位碼起始',
    `pos_end` SMALLINT UNSIGNED NOT NULL COMMENT '位碼結束',
    PRIMARY KEY (`py`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COMMENT='GBK 區位碼對應拼音首字母';

-- 匯入標準映射資料 (26 行)
INSERT INTO pinyin_map VALUES
('A', 16, 16, 1, 87),   ('B', 16, 16, 88, 255), ('C', 17, 17, 1, 255),
('D', 18, 18, 1, 255),  ('E', 19, 19, 1, 255),  ('F', 20, 20, 1, 255),
('G', 21, 21, 1, 255),  ('H', 22, 22, 1, 255),  ('J', 23, 23, 1, 255),
('K', 24, 24, 1, 255),  ('L', 25, 25, 1, 255),  ('M', 26, 26, 1, 255),
('N', 27, 27, 1, 255),  ('O', 28, 28, 1, 255),  ('P', 29, 29, 1, 255),
('Q', 30, 30, 1, 255),  ('R', 31, 31, 1, 255),  ('S', 32, 32, 1, 255),
('T', 33, 33, 1, 255),  ('W', 34, 34, 1, 255),  ('X', 35, 35, 1, 255),
('Y', 36, 36, 1, 255),  ('Z', 37, 37, 1, 255);
-- 註：GBK 編碼 = (區碼-1)*256 + (位碼-1) + 0x8140，簡化為連續區間映射

步驟二：建立高效標量函數 `hzcode()`

DELIMITER //

DROP FUNCTION IF EXISTS `hzcode` //

CREATE FUNCTION `hzcode`(p_str VARCHAR(500))
RETURNS CHAR(1)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_gbk_code INT UNSIGNED;
    DECLARE v_py CHAR(1) DEFAULT '#';
    DECLARE v_len INT DEFAULT CHAR_LENGTH(p_str);
    
    -- 取第一個字符
    IF v_len = 0 THEN RETURN '#'; END IF;
    
    -- 將首字符轉為 GBK 編碼數值
    -- 方法：CONVERT 到 GBK 後取十六進位
    SET v_gbk_code = CONV(HEX(CONVERT(SUBSTRING(p_str,1,1) USING gbk)), 16, 10);
    
    -- 若為 ASCII (單字節 < 128) 直接返回大寫
    IF v_gbk_code < 128 THEN
        RETURN UPPER(CHAR(v_gbk_code));
    END IF;
    
    -- 查詢映射表 (GBK 雙字節範圍 0x8140-0xFEFE)
    SELECT py INTO v_py 
    FROM pinyin_map 
    WHERE v_gbk_code BETWEEN 
        ((zone_start-1)*256 + pos_start) AND 
        ((zone_end-1)*256 + pos_end)
    LIMIT 1;
    
    RETURN COALESCE(v_py, '#');
END //

DELIMITER ;

步驟三：測試函數正確性

SELECT hzcode('南海龍王') AS py;  -- N
SELECT hzcode('張三') AS py;       -- Z
SELECT hzcode('李四') AS py;       -- L
SELECT hzcode('王五') AS py;       -- W
SELECT hzcode('趙六') AS py;       -- Z
SELECT hzcode('錢七') AS py;       -- Q
SELECT hzcode('ABC') AS py;        -- A
SELECT hzcode('123') AS py;        -- 1
SELECT hzcode('') AS py;           -- #

步驟四：業務表增加首字母欄位與索引

方案 A：MySQL 8.0+ 虛擬欄位 (零存儲、自動維護、推薦)

ALTER TABLE `users`
ADD COLUMN `name_py` CHAR(1) CHARSET ascii 
    GENERATED ALWAYS AS (hzcode(`name`)) VIRTUAL,
ADD INDEX `idx_name_py` (`name_py`);

方案 B：MySQL 5.7 實體欄位 + 觸發器維護

ALTER TABLE `users`
ADD COLUMN `name_py` CHAR(1) CHARSET ascii NOT NULL DEFAULT '#',
ADD INDEX `idx_name_py` (`name_py`);

-- 插入觸發器
DELIMITER //
CREATE TRIGGER `bi_users_name_py` BEFORE INSERT ON `users`
FOR EACH ROW
BEGIN
    SET NEW.name_py = hzcode(NEW.name);
END //

-- 更新觸發器
CREATE TRIGGER `bu_users_name_py` BEFORE UPDATE ON `users`
FOR EACH ROW
BEGIN
    IF NEW.name <> OLD.name THEN
        SET NEW.name_py = hzcode(NEW.name);
    END IF;
END //
DELIMITER ;

-- 歷史資料回填
UPDATE `users` SET `name_py` = hzcode(`name`) WHERE `name_py` = '#';

步驟五：業務查詢範例

-- 1. 按首字母分組統計
SELECT name_py, COUNT(*) AS cnt 
FROM users 
GROUP BY name_py 
ORDER BY name_py;

-- 2. 首字母篩選 (走 idx_name_py 索引)
SELECT id, name, phone FROM users 
WHERE name_py = 'Z' 
ORDER BY name LIMIT 20;

-- 3. 首字母範圍查詢 (A-G)
SELECT * FROM users 
WHERE name_py BETWEEN 'A' AND 'G' 
ORDER BY name_py, name;

-- 4. 聯表查詢優化 (避免在 JOIN ON 中呼叫函數)
SELECT u.name, d.dept_name 
FROM users u
JOIN departments d ON u.dept_id = d.id
WHERE u.name_py = 'L';

效能優化關鍵點

1. 函數聲明 `DETERMINISTIC` + `READS SQL DATA`：允許優化器緩存結果、推導常量
2. 映射表主鍵 `py` + 區間查詢：單次索引查找，極快
3. 虛擬欄位索引：不佔用磁碟、寫入無開銷、查詢走 B+ 樹
4. 避免 `WHERE hzcode(name) = 'Z'`：全表掃描，**必須**用欄位索引
5. 字符集 `ascii`**：比較最快、儲存最小 (1 字節)

-- 1. 擴展映射表含全拼
ALTER TABLE pinyin_map ADD COLUMN pinyin_full VARCHAR(20) NOT NULL AFTER py;
UPDATE pinyin_map SET pinyin_full = CASE py 
    WHEN 'A' THEN 'a' WHEN 'B' THEN 'ba' ... END;  -- 需完整填充

-- 2. 建立全拼函數 (類似 hzcode，返回 VARCHAR)
CREATE FUNCTION pinyin_full(p_str VARCHAR(500)) RETURNS VARCHAR(2000)
DETERMINISTIC READS SQL DATA
BEGIN
    -- 迴圈逐字符轉換，CONCAT 結果
    -- 略...
END;

-- 3. 虛擬欄位
ALTER TABLE users ADD COLUMN name_py_full VARCHAR(200) CHARSET ascii
    GENERATED ALWAYS AS (pinyin_full(name)) VIRTUAL,
ADD INDEX idx_name_py_full (name_py_full);