将mysql中汉字转为首字母字符串的三种形式

最新推荐文章于 2021-09-14 16:31:22 发布

原创最新推荐文章于 2021-09-14 16:31:22 发布 · 3.4k 阅读

CC 4.0 BY-SA版权

文章标签：

mysql中存储汉字是以双字节形式存储，连续两个汉字编码从161 161开始。连续两个字符大于160，就可以认为是汉字。
以下三种形式过滤了字符中存在的空格（包括英文空格与汉字空格）
1.利用mysql中的ascii与char函数互相转换，取出gb2312的首字母内容。
参照GB2312-80区位编码表将汉字提取，
DELIMITER $$

DROP FUNCTION IF EXISTS `radius`.`strtopy`$$

CREATE DEFINER=`root`@`%` FUNCTION `strtopy`(str CHAR(255)) RETURNS varchar(255) CHARSET latin1
    READS SQL DATA
    /*
    tangwf: 2007-12-29
    use: convert the chinese to first letter
    email: tangwf_hrb@126.com
    */
BEGIN
     DECLARE mycode int;
     DECLARE lcode int;
     DECLARE rcode int;
     DECLARE mypy varchar(255);
     DECLARE i int;
     SET mycode=0;
     SET mypy='';
     SET i=1;
    -- SET str=CONVERT(REPLACE(REPLACE(str, ' ', ''),'??','') USING gbk);
     SET str=REPLACE(REPLACE(str, ' ', ''),'??','');
     WHILE i <=length(str) DO
     SET lcode = ascii(substring(str,i,1));
     SET rcode = ascii(substring(str,i+1,1));
     -- when is chinese
     IF lcode>160 and rcode>160 THEN
         SET mycode = lcode*256+rcode-65536 ;
         SET i=i+1;
         SELECT concat(mypy,PY) INTO mypy FROM
         (
         SELECT 'A' AS PY,-20319 AS BG,-20284 AS ED UNION ALL
         SELECT 'B',-20283,-19776 UNION ALL
         SELECT 'C',-19775,-19219 UNION ALL
         SELECT 'D',-19218,-18711 UNION ALL
         SELECT 'E',-18710,-18527 UNION ALL
         SELECT 'F',-18526,-18240 UNION ALL
         SELECT 'G',-18239,-17923 UNION ALL
         SELECT 'H',-17922,-17418 UNION ALL
         SELECT 'J',-17417,-16475 UNION ALL
         SELECT 'K',-16474,-16213 UNION ALL
         SELECT 'L',-16212,-15641 UNION ALL
         SELECT 'M',-15640,-15166 UNION ALL
         SELECT 'N',-15165,-14923 UNION ALL
         SELECT 'O',-14922,-14915 UNION ALL
         SELECT 'P',-14914,-14631 UNION ALL
         SELECT 'Q',-14630,-14150 UNION ALL
         SELECT 'R',-14149,-14091 UNION ALL
         SELECT 'S',-14090,-13319 UNION ALL
         SELECT 'T',-13318,-12839 UNION ALL
         SELECT 'W',-12838,-12557 UNION ALL
         SELECT 'X',-12556,-11848 UNION ALL
         SELECT 'Y',-11847,-11056 UNION ALL
         SELECT 'Z',-11055,-10247
         ) AS tb
         WHERE mycode>=BG AND mycode<=ED;
     ELSE
         SET mypy:=concat(mypy,substring(str,i,1));
     END IF;
     --

     SET i=i+1;
     END WHILE;
     RETURN mypy;
     END
DELIMITER $$

2.按gbk拼章库查询

GBK 亦采用双字节表示，总体编码范围为 8140-FEFE，首字节在 81-FE 之间，尾字节在 40-FE 之间，剔除 xx7F 一条线。总计 23940 个码位，共收入 21886 个汉字和图形符号，其中汉字（包括部首和构件）21003 个，图形符号 883 个。
全部编码分为三大部分：
汉字区。包括：
a. GB 2312 汉字区。即 GBK/2: B0A1-F7FE。收录 GB 2312 汉字 6763 个，按原顺序排列。
b. GB 13000.1 扩充汉字区。包括：
GBK/3: 8140-A0FE。收录 GB 13000.1 中的 CJK 汉字 6080 个。
GBK/4: AA40-FEA0。收录 CJK 汉字和增补的汉字 8160 个。CJK 汉字在前，按 UCS 代码大小排列；增补的汉字（包括部首和构件）在后，按《康熙字典》的页码／字位排列。

国标码规定，每个汉字(包括非汉字的一些符号)由2字节代码表示。每个字节的最高位为0，只使用低7位，而低7位的编码中又有34个适用于控制用的，
这样每个字节只有2的7次方 - 34 = 94个编码用于汉字。
2个字节就有94 94=8836个汉字编码。在表示一个汉字的2个字节中，高字节对应编码表中的行号，称为区号;低字节对应编码表中的列号，称为位号。

一个汉字内码与区位码的关系是：
十进制
内码高字节＝区码＋160 内码低字节＝位码＋160
十六进制
内码高字节＝区码＋A0 内码低字节＝位码＋A0
由于汉字的区码是从16开始的，16＋160＝176，这样汉字的内码的高字节就是从176开始的.

对于GBK 方式的拼音查询，在此处使用了辅表，即gbk拼音表。
注意事项：多音字的处理方式。此处按默认排序取其中一个。比如汤这个汉字有tang 和shang 两种拼写，默认选shang
拼音辅表建表方式：
CREATE TABLE `pytable` (
           `id` int(11) NOT NULL auto_increment,
           `py` varchar(25) default NULL,
           `hz` varchar(5) default NULL,
           PRIMARY KEY  (`id`)
         ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
GBK格式的拼音表在网上可以找到：http://input.foruto.com/gbqpxdm/gbkpydz.htm
此处的拼音表也可多加一ascii码格式。可在查询中有效节约运算时间。
导入表中后，注意多音字的处理情况。如：汤 tang shang当前不能支持'喆',珺,赟,'琤'，璆　等汉字。'喆'的区位码为134,180,此处查询可直接生成区码及位码。在
拼音表中进行匹配查询。
DROP FUNCTION IF EXISTS `radius`.`strtopy2`$$

CREATE FUNCTION `radius`.`strtopy2`(str CHAR(255))
    RETURNS  varchar(255)
    READS SQL DATA
    /*
    tangwf: 2007-12-29
    use: convert the chinese to first letter
    email: tangwf_hrb@126.com
    */
    BEGIN
    DECLARE mycode int;
     DECLARE lcode int;
     DECLARE rcode int;
     DECLARE mypy varchar(255);
     DECLARE i int;
     SET mycode=0;
     SET mypy='';
     SET i=1;
    -- SET str=CONVERT(REPLACE(REPLACE(str, ' ', ''),'??','') USING gbk);
     SET str=REPLACE(REPLACE(str, ' ', ''),'??','');
     WHILE i <=length(str) DO
     SET lcode = ascii(substring(str,i,1));
     SET rcode = ascii(substring(str,i+1,1));
     -- when is chinese
     IF lcode>160 and rcode>160 THEN
         SET mycode = lcode*256+rcode-65536 ;
         SET i=i+1;
         SELECT concat(mypy,UPPER(SUBSTRING(PY,1,1))) INTO mypy FROM pytable
         WHERE mycode=ascii(substring(HZ,1,1))*256+ascii(substring(HZ,2,1))-65536 limit 1;
     ELSE
         SET mypy:=concat(mypy,substring(str,i,1));
     END IF;
     --

     SET i=i+1;
     END WHILE;
     RETURN mypy;
    END$$

DELIMITER ;

3.按区位码查找
思路是参照GB2312-80区位编码表
1-9区为标准符号区;
10-15区为自定义符号区
16-55区为一级汉字区，包含3755个汉字。这些区中的汉字按汉语拼音顺序排序，同音字按笔画顺序列出。
56 -87区：为二级汉字区，包含3008个汉字。这些区中的汉字是按部首笔划顺序排序的（包括多音字）。
在函数中slv的字符串是根据汪秋棠写的string f_GetFirstLetter(string as_inputstring)
文章中提供的。代表二级汉字区3008个汉字对应的拼音首字母，其中的'['表示类似于两点水等符号没有声母。此字符串没有核对过。
对二级汉字的查找为区码减56区乘汉字个数加位码如：SET mypos=((lcode-160)*100 - 56) * 94 + rcode-160;　
DELIMITER $$

DROP FUNCTION IF EXISTS `radius`.`strtopy3`$$

CREATE FUNCTION `radius`.`strtopy3`(str CHAR(255))
    RETURNS  varchar(255)
    READS SQL DATA
   /*
    tangwf: 2007-12-29
    use: convert the chinese to first letter
    email: tangwf_hrb@126.com
    */
    BEGIN
    DECLARE mycode int;
    DECLARE lcode int;
      DECLARE rcode int;
      DECLARE mypy varchar(255);
    DECLARE i int;
    DECLARE slv varchar(4000); -- 2level code
    DECLARE mypos int;
    SET i=1;
    SET mypy='';
      SET mycode=0;
    SET slv='CQWGNSPGCGNEGYPBTYYZDXYKYGT[JNNJQMBSGZSCYJSYY[PGKBZGY[YWJKGKLJYWKPJQHY[W[DZLSGMRYPYWWCCKZNKYYGTTNJJNYKKZYTCJNMCYLQLYPYQFQRPZSLWBTGKJFYXJWZLTBNCXJJJJTXDTTSQZYCDXXHGCK[PHFFSS[YBGXLPPBYLL[HLXS[ZM[JHSOJNGHDZQYKLGJHSGQZHXQGKEZZWYSCSCJXYEYXADZPMDSSMZJZQJYZC[J[WQJBYZPXGZNZCPWHKXHQKMWFBPBYDTJZZKQHYLYGXFPTYJYYZPSZLFCHMQSHGMXXSXJ[[DCSBBQBEFSJYHXWGZKPYLQBGLDLCCTNMAYDDKSSNGYCSGXLYZAYBNPTSDKDYLHGYMYLCXPY[JNDQJWXQXFYYFJLEJPZRXCCQWQQSBNKYMGPLBMJRQCFLNYMYQMSQYRBCJTHZTQFRXQHXMJJCJLXQGJMSHZKBSWYEMYLTXFSYDSWLYCJQXSJNQBSCTYHBFTDCYZDJWYGHQFRXWCKQKXEBPTLPXJZSRMEBWHJLBJSLYYSMDXLCLQKXLHXJRZJMFQHXHWYWSBHTRXXGLHQHFNM[YKLDYXZPYLGG[MTCFPAJJZYLJTYANJGBJPLQGDZYQYAXBKYSECJSZNSLYZHSXLZCGHPXZHZNYTDSBCJKDLZAYFMYDLEBBGQYZKXGLDNDNYSKJSHDLYXBCGHXYPKDJMMZNGMMCLGWZSZXZJFZNMLZZTHCSYDBDLLSCDDNLKJYKJSYCJLKWHQASDKNHCSGANHDAASHTCPLCPQYBSDMPJLPZJOQLCDHJJYSPRCHN[NNLHLYYQYHWZPTCZGWWMZFFJQQQQYXACLBHKDJXDGMMYDJXZLLSYGXGKJRYWZWYCLZMSSJZLDBYD[FCXYHLXCHYZJQ[[QAGMNYXPFRKSSBJLYXYSYGLNSCMHZWWMNZJJLXXHCHSY[[TTXRYCYXBYHCSMXJSZNPWGPXXTAYBGAJCXLY[DCCWZOCWKCCSBNHCPDYZNFCYYTYCKXKYBSQKKYTQQXFCWCHCYKELZQBSQYJQCCLMTHSYWHMKTLKJLYCXWHEQQHTQH[PQ[QSCFYMNDMGBWHWLGSLLYSDLMLXPTHMJHWLJZYHZJXHTXJLHXRSWLWZJCBXMHZQXSDZPMGFCSGLSXYMJSHXPJXWMYQKSMYPLRTHBXFTPMHYXLCHLHLZYLXGSSSSTCLSLDCLRPBHZHXYYFHB[GDMYCNQQWLQHJJ[YWJZYEJJDHPBLQXTQKWHLCHQXAGTLXLJXMSL[HTZKZJECXJCJNMFBY[SFYWYBJZGNYSDZSQYRSLJPCLPWXSDWEJBJCBCNAYTWGMPAPCLYQPCLZXSBNMSGGFNZJJBZSFZYNDXHPLQKZCZWALSBCCJX[YZGWKYPSGXFZFCDKHJGXDLQFSGDSLQWZKXTMHSBGZMJZRGLYJBPMLMSXLZJQQHZYJCZYDJWBMYKLDDPMJEGXYHYLXHLQYQHKYCWCJMYYXNATJHYCCXZPCQLBZWWYTWBQCMLPMYRJCCCXFPZNZZLJPLXXYZTZLGDLDCKLYRZZGQTGJHHGJLJAXFGFJZSLCFDQZLCLGJDJCSNZLLJPJQDCCLCJXMYZFTSXGCGSBRZXJQQCTZHGYQTJQQLZXJYLYLBCYAMCSTYLPDJBYREGKLZYZHLYSZQLZNWCZCLLWJQJJJKDGJZOLBBZPPGLGHTGZXYGHZMYCNQSYCYHBHGXKAMTXYXNBSKYZZGJZLQJDFCJXDYGJQJJPMGWGJJJPKQSBGBMMCJSSCLPQPDXCDYYKY[CJDDYYGYWRHJRTGZNYQLDKLJSZZGZQZJGDYKSHPZMTLCPWNJAFYZDJCNMWESCYGLBTZCGMSSLLYXQSXSBSJSBBSGGHFJLYPMZJNLYYWDQSHZXTYYWHMZYHYWDBXBTLMSYYYFSXJC[DXXLHJHF[SXZQHFZMZCZTQCXZXRTTDJHNNYZQQMNQDMMG[YDXMJGDHCDYZBFFALLZTDLTFXMXQZDNGWQDBDCZJDXBZGSQQDDJCMBKZFFXMKDMDSYYSZCMLJDSYNSBRSKMKMPCKLGDBQTFZSWTFGGLYPLLJZHGJ[GYPZLTCSMCNBTJBQFKTHBYZGKPBBYMTDSSXTBNPDKLEYCJNYDDYKZDDHQHSDZSCTARLLTKZLGECLLKJLQJAQNBDKKGHPJTZQKSECSHALQFMMGJNLYJBBTMLYZXDCJPLDLPCQDHZYCBZSCZBZMSLJFLKRZJSNFRGJHXPDHYJYBZGDLQCSEZGXLBLGYXTWMABCHECMWYJYZLLJJYHLG[DJLSLYGKDZPZXJYYZLWCXSZFGWYYDLYHCLJSCMBJHBLYZLYCBLYDPDQYSXQZBYTDKYXJY[CNRJMPDJGKLCLJBCTBJDDBBLBLCZQRPPXJCJLZCSHLTOLJNMDDDLNGKAQHQHJGYKHEZNMSHRP[QQJCHGMFPRXHJGDYCHGHLYRZQLCYQJNZSQTKQJYMSZSWLCFQQQXYFGGYPTQWLMCRNFKKFSYYLQBMQAMMMYXCTPSHCPTXXZZSMPHPSHMCLMLDQFYQXSZYYDYJZZHQPDSZGLSTJBCKBXYQZJSGPSXQZQZRQTBDKYXZKHHGFLBCSMDLDGDZDBLZYYCXNNCSYBZBFGLZZXSWMSCCMQNJQSBDQSJTXXMBLTXZCLZSHZCXRQJGJYLXZFJPHYMZQQYDFQJJLZZNZJCDGZYGCTXMZYSCTLKPHTXHTLBJXJLXSCDQXCBBTJFQZFSLTJBTKQBXXJJLJCHCZDBZJDCZJDCPRNPQCJPFCZLCLZXZDMXMPHJSGZGSZZQLYLWTJPFSYASMCJBTZKYCWMYTCSJJLJCQLWZMALBXYFBPNLSFHTGJWEJJXXGLLJSTGSHJQLZFKCGNNNSZFDEQFHBSAQTGYLBXMMYGSZLDYDQMJJRGBJTKGDHGKBLQKBDMBYLXWCXYTTYBKMRTJZXQJBHLMHMJJZMQASLDCYXYQDLQCAFYWYXQHZ';
    SET str=REPLACE(REPLACE(str, ' ', ''),'??','');

    WHILE i <=length(str) DO
        SET lcode = ascii(substring(str,i,1));
        SET rcode = ascii(substring(str,i+1,1));
        -- when is chinese
        IF lcode>160 and rcode>160 THEN
            SET mycode = (lcode-160)*100+rcode-160;
            SET i=i+1;
            -- 1level chinese
            IF mycode > 1600 AND mycode < 5590 THEN
                Select concat(mypy,PY) INTO mypy from
                (
                SELECT 'A' as PY,1601 as areacode UNION ALL
                SELECT 'B',1637 UNION ALL
                SELECT 'C',1833 UNION ALL
                SELECT 'D',2078 UNION ALL
                SELECT 'E',2274 UNION ALL
                SELECT 'F',2302 UNION ALL
                SELECT 'G',2433 UNION ALL
                SELECT 'H',2594 UNION ALL
                SELECT 'J',2787 UNION ALL
                SELECT 'K',3106 UNION ALL
                SELECT 'L',3212 UNION ALL
                SELECT 'M',3472 UNION ALL
                SELECT 'N',3635 UNION ALL
                SELECT 'O',3722 UNION ALL
                SELECT 'P',3730 UNION ALL
                SELECT 'Q',3858 UNION ALL
                SELECT 'R',4027 UNION ALL
                SELECT 'S',4086 UNION ALL
                SELECT 'T',4390 UNION ALL
                SELECT 'W',4558 UNION ALL
                SELECT 'X',4684 UNION ALL
                SELECT 'Y',4925 UNION ALL
                SELECT 'Z',5249
                ) as tb WHERE areacode <=mycode ORDER BY  areacode DESC LIMIT 1;
            -- 2level chinese
            ELSEIF  mycode > 5600 AND mycode < 8795  THEN
                -- find the place
                SET mypos=((lcode-160)*100 - 56) * 94 + rcode-160;
                SET mypy = CONCAT(mypy,substring(slv,mypos,1));
            -- other chinese
            ELSE
                SET mypy=CONCAT(mypy,'');
            END IF;
        ELSE
            SET mypy:=concat(mypy,substring(str,i,1));
        END IF;
        SET i=i+1;
    END WHILE;
    Return mypy;
    END$$

DELIMITER ;
以上三个函数建立成功后，
select radius.strtopy('汤婧'),radius.strtopy2('汤婧'),radius.strtopy3('汤婧')
结果：T,TJ,T
由此函数想到mysql中对汉字的精确查找及模糊查找。可以用ascii编码方式进行。
GB2312-80区位编码表　与 GBK编码表（汉字内码扩展规范(GBK)）可在网上搜索到