to_pinyin(基於 GBK 編碼區間映射)code = 65536 - high*256 - low,對照拼音區間表查找對應拼音。CREATE TABLE t_base_pinyin (
pin_yin VARCHAR(10) CHARACTER SET gbk NOT NULL,
code_ INT UNSIGNED NOT NULL,
PRIMARY KEY (code_)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 插入 400+ 條映射資料(略,見原文完整 INSERT)
DROP FUNCTION IF EXISTS to_pinyin;
DELIMITER $$
CREATE FUNCTION to_pinyin(in_str VARCHAR(255) CHARSET gbk)
RETURNS VARCHAR(255) CHARSET gbk
DETERMINISTIC
BEGIN
DECLARE hex_str VARCHAR(510);
DECLARE i INT DEFAULT 1;
DECLARE l INT;
DECLARE high INT;
DECLARE low INT;
DECLARE code INT;
DECLARE result VARCHAR(255) DEFAULT '';
DECLARE py VARCHAR(10);
SET hex_str = HEX(in_str);
SET l = LENGTH(hex_str);
WHILE i <= l DO
SET high = CAST(CONV(SUBSTRING(hex_str, i, 2), 16, 10) AS UNSIGNED);
IF high > 128 THEN
SET low = CAST(CONV(SUBSTRING(hex_str, i+2, 2), 16, 10) AS UNSIGNED);
SET code = 65536 - high * 256 - low;
SELECT pin_yin INTO py FROM t_base_pinyin
WHERE code_ >= code ORDER BY code_ ASC LIMIT 1;
SET result = CONCAT(result, py);
SET i = i + 4;
ELSE
SET result = CONCAT(result, CHAR(high));
SET i = i + 2;
END IF;
END WHILE;
RETURN LOWER(result);
END $$
DELIMITER ;
SELECT to_pinyin('測試'); -- ceshi
SELECT to_pinyin('北京'); -- beijing
fristPinyin / pinyin(基於區間判斷,無需對照表)CREATE FUNCTION fristPinyin(p_name VARCHAR(255))
RETURNS VARCHAR(255) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE v_return CHAR(1);
SET v_return = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(p_name USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
RETURN v_return;
END;
CREATE FUNCTION pinyin(p_name VARCHAR(255))
RETURNS VARCHAR(255) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE v_return VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 1;
DECLARE v_char VARCHAR(1);
WHILE i <= LENGTH(p_name) DO
SET v_char = SUBSTR(p_name, i, 1);
IF v_char != '' THEN
SET v_return = CONCAT(v_return, fristPinyin(v_char));
END IF;
SET i = i + 1;
END WHILE;
RETURN IFNULL(NULLIF(v_return, ''), p_name);
END;
SELECT province, fristPinyin(province), pinyin(province) FROM province;
+----------+------------------------+------------------+
| province | fristPinyin(province) | pinyin(province) |
+----------+------------------------+------------------+
| 北京市 | B | BJS |
| 天津市 | T | TJS |
| 河北省 | H | HBS |
| 山西省 | S | SXS |
| 內蒙古自治區 | N | NMGZZQ |
+----------+------------------------+------------------+
| 維度 | 方案一 to_pinyin | 方案二 fristPinyin/pinyin |
|---|---|---|
| 輸出 | 全拼小寫 | 首字母大寫 |
| 依賴 | 需建立對照表 (400+ 行) | 無依賴,純區間運算 |
| 效能 | 查表開銷略大 | 極快,純計算 |
| 準確度 | 高(覆蓋所有漢字) | 高(GBK 區間標準) |
| 適用場景 | 需完整拼音(搜尋、排序、URL slug) | 首字母索引、分組、簡繁轉換輔助 |
CONVERT(... USING gbk))。ICU 排序規則 utf8mb4_zh_0900_as_cs 實現原生排序,無需自訂函數。pypinyin),避免 SQL 層 CPU 壓力。MySQL 內建無拼音函數,方案一「對照表+函數」輸出全拼,方案二「區間運算」輸出首字母,各有優勢。根據業務需求(全拼/首字母)、資料量、維護成本選擇,或直接在應用層處理更靈活。