例子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; +------+-------+ | name | pykey | +------+-------+ | 张三 | Z | | 李四 | L | | 王五 | W | | 赵六 | Z | | 钱七 | Q | +------+-------+ 5 rows in set (0.00 sec) |
|
这样就很方便地在MYSQL里查询汉字的首字母了。 类似地也可以直接在MYSQL得到汉字拼音。 不过需要拼音表，函数写法也不一样。
时间： 2024-10-27 05:59:41