mysql自定义函数实现中文首字母拼音查询

时间：2015-01-23 来源：西部数据作者：西部数据

在mysql中没有系统自带的函数可以实现查找中文字符的第一个汉字的首字母了,在这里我整理了两种比较好用的方法,下面一起来看看吧.

例子1:mysql功能函数,实现拼音查询.

功能:输入中文字符串每个字的首字母,即可检索出相应数据.

使用方法:直接使用py(字段名)=‘keywords’,即可,代码如下:

DELIMITER $$

CREATE FUNCTION `PYFIRST`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8

BEGIN

    DECLARE V_RETURN VARCHAR(255);

    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$$

DELIMITER ;

DELIMITER $$

CREATE FUNCTION `PY`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8

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, ',', V_COMPARE);

            SET V_RETURN = CONCAT(V_RETURN, PYFIRST(V_COMPARE));

            #SET V_RETURN = PYFIRST(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$$

DELIMITER ;

例子2,现在给出在mysql 里实现的,测试环境是mysql-5.0.27-win32.

1、建立拼音首字母资料表,代码如下:

DROP   TABLE   IF   EXISTS   `pyk`;

  CREATE   TABLE   `pyk`   (

      `PY`   varchar(1)    ,

      `HZ1`   int  ,

      `HZ2`   int

  ) ;



INSERT   INTO   `pyk`   (`PY`,`HZ1`,`HZ2`)   VALUES

('A',-20319,-20284),

('B',-20283,-19776),

('C',-19775,-19219),

('D',-19218,-18711),

('E',-18710,-18527),

('F',-18526,-18240),

('G',-18239,-17923),

('H',-17922,-17418),

('J',-17417,-16475),

('K',-16474,-16213),

('L',-16212,-15641),

('M',-15640,-15166),

('N',-15165,-14923),

('O',-14922,-14915),

('P',-14914,-14631),

('Q',-14630,-14150),

('R',-14149,-14091),

('S',-14090,-13319),

('T',-13318,-12839),

('W',-12838,-12557),

('X',-12556,-11848),

('Y',-11847,-11056),

('Z',-11055,-10247);

2、建立mysql 函数,代码如下:

DROP FUNCTION IF EXISTS hzcode;

delimiter //

CREATE FUNCTION hzcode (s CHAR(255)) RETURNS char

BEGIN

DECLARE hz_code int;

DECLARE hz_py char;

SET hz_code = ord(substring(s,1,1))*256+ord(substring(s,2,1))-65536 ;

select py into hz_py from pyk where hz_code>=pyk.hz1 and hz_code<=pyk

.hz2;

RETURN hz_py;

END

//

delimiter ;

数据库类型如果是GBK的时候,这个函数好像有点小问题,稍微改动了下,貌似可以在GBK中使用了,代码如下:

delimiter $$

DROP FUNCTION IF EXISTS `hzcode`$$

CREATE FUNCTION `hzcode` (s CHAR(255)) RETURNS char

        BEGIN

                DECLARE hz_code int;

                DECLARE hz_py char;

                declare str varchar(400);

                SET hz_code = ord(substring(s,1,1))-65536 ;

                select py into hz_py from pyk where  hz_code>=pyk.hz1 and hz_code<=pyk.hz2;

                RETURN hz_py;

        END$$

delimiter $$

3、先测试一下

mysql> select hzcode('南海龙王');

+--------------------+

| hzcode('南海龙王') |

+--------------------+

| N |

+--------------------+

1 row in set (0.00 sec)

4、建立个测试表

DROP TABLE IF EXISTS `f1`;

create table f1 (

name varchar(30),

pykey varchar(1)

);

insert into f1(name) values

('张三'),

('李四'),

('王五'),

('赵六'),

('钱七');

5、测试

mysql> select * from f1;

+------+-------+

| name | pykey |

+------+-------+

| 张三 | NULL |

| 李四 | NULL |

| 王五 | NULL |

| 赵六 | NULL |

| 钱七 | NULL |

+------+-------+

5 rows in set (0.00 sec)

mysql> update f1 set pykey = hzcode(name);

Query OK, 5 rows affected (0.05 sec)

Rows matched: 5 Changed: 5 Warnings: 0

mysql> select * from f1;//开源代码phpfensi.com

+------+-------+

| name | pykey |

+------+-------+

| 张三 | Z |

| 李四 | L |

| 王五 | W |

| 赵六 | Z |

| 钱七 | Q |

+------+-------+

5 rows in set (0.00 sec)