sql构建函数使汉字转拼音_sql汉字转拼音

使用sql 函数将数据字段转换为全拼音或首字母

临近年底，难得空闲，纪录一下前段时间使用汉字转拼音收集到的一些资料

核心思想

构建用户自定义函数，调用函数转换汉字为拼音

1. 转换单个汉字的首字母

• 1.1 创建函数（fristPinyin）

DROP FUNCTION IF EXISTS fristPinyin;
CREATE FUNCTION `fristPinyin`(P_NAME VARCHAR(255)) 
    RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE V_RETURN VARCHAR(255) ;
    DECLARE V_int int ;
		set V_int = 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);
        SET V_RETURN = ELT(V_int,'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
		IF V_int = 0 THEN
			SET V_RETURN = left(P_NAME,1);
		END IF;
    RETURN V_RETURN;
END

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• 1.2 使用函数

    SELECT fristPinyin("中");
1

2. 转换中文词组首字母

• 2.1 创建词组转首字母函数（pinyin）

依赖于 fristPinyin 函数

DROP FUNCTION IF EXISTS pinyin;
CREATE FUNCTION `pinyin`(P_NAME VARCHAR(255)) 
    RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC 
BEGIN
    DECLARE V_COMPARE VARCHAR(255);
    DECLARE V_RETURN VARCHAR(255);
    DECLARE I INT;
    SET I = 1;
    SET V_RETURN = '';
    WHILE I < LENGTH(P_NAME) do
        SET V_COMPARE = SUBSTR(P_NAME, I, 1);
        IF (V_COMPARE != '') THEN
            SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));
        END IF;
        SET I = I + 1;
    end WHILE;
    IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
        SET V_RETURN = P_NAME;
    END IF;
    RETURN V_RETURN;
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• 2.2 使用函数

    SELECT pinyin("中国");
1

3、 将中文词组转换成全拼拼音

• 1、 创建对应关系数据表

DROP TABLE IF EXISTS `t_base_pinyin`;
CREATE TABLE `t_base_pinyin`  (
  `pin_yin_` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `code_` int(11) NOT NULL,
  PRIMARY KEY (`code_`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '汉字转拼音使用的表。to_quanpin函数使用，慎删' ROW_FORMAT = Compact;
1
2
3
4
5
6

• 2、 导入数据

INSERT INTO t_base_pinyin (pin_yin_,code_)  VALUES ("a", 20319),("ai", 20317),("an", 20304),("ang", 20295),("ao", 20292),("ba", 20283),("bai", 20265),("ban", 20257),("bang", 20242),("bao", 20230),("bei", 20051),("ben", 20036),("beng", 20032),("bi", 20026),("bian", 20002),("biao", 19990),("bie", 19986),("bin", 19982),("bing", 19976),("bo", 19805),("bu", 19784),("ca", 19775),("cai", 19774),("can", 19763),("cang", 19756),("cao", 19751),("ce", 19746),("ceng", 19741),("cha", 19739),("chai", 19728),("chan", 19725),("chang", 19715),("chao", 19540),("che", 19531),("chen", 19525),("cheng", 19515