萬盛學電腦網

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

MySQL 中文轉拼音函數實現方案(全拼與首字母)

方案一:全拼轉換函數 to_pinyin(基於 GBK 編碼區間映射)

核心原理

  • 漢字在 GBK 編碼中佔 2 位元組,高位 0x81~0xFE,低位 0x40~0xFE (除 0x7F)。
  • 將雙位元組轉為無符號整數 code = 65536 - high*256 - low,對照拼音區間表查找對應拼音。
  • 非漢字字元(ASCII)直接保留。

建立拼音對照表

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)首字母索引、分組、簡繁轉換輔助

注意事項

  1. 資料庫/連線字元集需支援 GBK(或在函數內 CONVERT(... USING gbk))。
  2. 生僻字、港澳台異體字可能不在基本區間,需擴充對照表。
  3. MySQL 8.0+ 可考慮使用 ICU 排序規則 utf8mb4_zh_0900_as_cs 實現原生排序,無需自訂函數。
  4. 大量資料轉換建議在應用層完成(如 Python pypinyin),避免 SQL 層 CPU 壓力。

總結

MySQL 內建無拼音函數,方案一「對照表+函數」輸出全拼,方案二「區間運算」輸出首字母,各有優勢。根據業務需求(全拼/首字母)、資料量、維護成本選擇,或直接在應用層處理更靈活。

copyright © 萬盛學電腦網 all rights reserved