【www.bbyears.com--word】
例子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、建立拼音首字母资料表
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;
+------+-------+
| name | pykey |
+------+-------+
| 张三 | Z |
| 李四 | L |
| 王五 | W |
| 赵六 | Z |
| 钱七 | Q |
+------+-------+
5 rows in set (0.00 sec)
这样就很方便地在MYSQL里查询汉字的首字母了。 类似地也可以直接在MYSQL得到汉字拼音。 不过需要拼音表，函数写法也不一样。