夏日博客

A-A+

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

2017年10月15日 PHP技术文章 暂无评论 阅读 0 views 次

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

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

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

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

  1. DELIMITER $$   
  2. CREATE FUNCTION `PYFIRST`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8     
  3. BEGIN     
  4.     DECLARE V_RETURN VARCHAR(255);     
  5.     SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),      
  6.         0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,      
  7.         0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,     
  8.         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');     
  9.     RETURN V_RETURN;     
  10. END$$   
  11. DELIMITER ;   
  12.    
  13. DELIMITER $$   
  14. CREATE FUNCTION `PY`(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8    
  15. BEGIN     
  16.     DECLARE V_COMPARE VARCHAR(255);     
  17.     DECLARE V_RETURN VARCHAR(255);     
  18.     DECLARE I INT;     
  19.    
  20.     SET I = 1;     
  21.     SET V_RETURN = '';     
  22.     while I < LENGTH(P_NAME) do     
  23.         SET V_COMPARE = SUBSTR(P_NAME, I, 1);     
  24.         IF (V_COMPARE != '') THEN     
  25.             #SET V_RETURN = CONCAT(V_RETURN, ',', V_COMPARE);     
  26.             SET V_RETURN = CONCAT(V_RETURN, PYFIRST(V_COMPARE));     
  27.             #SET V_RETURN = PYFIRST(V_COMPARE);     
  28.         END IF;     
  29.         SET I = I + 1;     
  30.     end while;     
  31.    
  32.     IF (ISNULL(V_RETURN) or V_RETURN = '') THEN     
  33.         SET V_RETURN = P_NAME;     
  34.     END IF;     
  35.    
  36.     RETURN V_RETURN;     
  37. END$$   
  38. DELIMITER ;   

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

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

  1. DROP   TABLE   IF   EXISTS   `pyk`;      
  2.   CREATE   TABLE   `pyk`   (      
  3.       `PY`   varchar(1)    ,      
  4.       `HZ1`   int  ,   
  5.       `HZ2`   int         
  6.   ) ;   
  7.        
  8. INSERT   INTO   `pyk`   (`PY`,`HZ1`,`HZ2`)   VALUES        
  9. ('A',-20319,-20284),   
  10. ('B',-20283,-19776),   
  11. ('C',-19775,-19219),    
  12. ('D',-19218,-18711),    
  13. ('E',-18710,-18527),    
  14. ('F',-18526,-18240),    
  15. ('G',-18239,-17923),    
  16. ('H',-17922,-17418),                 
  17. ('J',-17417,-16475),                 
  18. ('K',-16474,-16213),                 
  19. ('L',-16212,-15641),                 
  20. ('M',-15640,-15166),                 
  21. ('N',-15165,-14923),                 
  22. ('O',-14922,-14915),                 
  23. ('P',-14914,-14631),                 
  24. ('Q',-14630,-14150),                 
  25. ('R',-14149,-14091),                 
  26. ('S',-14090,-13319),                 
  27. ('T',-13318,-12839),                 
  28. ('W',-12838,-12557),                 
  29. ('X',-12556,-11848),                 
  30. ('Y',-11847,-11056),                 
  31. ('Z',-11055,-10247);   

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

  1. DROP FUNCTION IF EXISTS hzcode;   
  2.    
  3. delimiter //   
  4.    
  5. CREATE FUNCTION hzcode (s CHAR(255)) RETURNS char   
  6. BEGIN   
  7. DECLARE hz_code int;   
  8. DECLARE hz_py char;   
  9. SET hz_code = ord(substring(s,1,1))*256+ord(substring(s,2,1))-65536 ;   
  10. select py into hz_py from pyk where  hz_code>=pyk.hz1 and hz_code<=pyk   
  11. .hz2;                     
  12. RETURN hz_py;   
  13. END   
  14. //   
  15. delimiter ;  

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

  1. delimiter $$   
  2.    
  3. DROP FUNCTION IF EXISTS `hzcode`$$   
  4. CREATE FUNCTION `hzcode` (s CHAR(255)) RETURNS char   
  5.         BEGIN   
  6.                 DECLARE hz_code int;   
  7.                 DECLARE hz_py char;   
  8.                 declare str varchar(400);   
  9.                 SET hz_code = ord(substring(s,1,1))-65536 ;   
  10.                 select py into hz_py from pyk where  hz_code>=pyk.hz1 and hz_code<=pyk.hz2;                  
  11.                 RETURN hz_py;   
  12.    
  13.         END$$          
  14. delimiter $$   

3、先测试一下

  1. mysql> select hzcode('南海龙王');   
  2. +--------------------+   
  3. | hzcode('南海龙王') |   
  4. +--------------------+   
  5. | N |   
  6. +--------------------+   
  7. 1 row in set (0.00 sec)   

4、建立个测试表

  1. DROP   TABLE   IF   EXISTS  `f1`;   
  2. create table f1 (   
  3.  name varchar(30),   
  4.  pykey varchar(1)   
  5.  );   
  6.    
  7. insert into f1(name) values   
  8. ('张三'),   
  9. ('李四'),   
  10. ('王五'),   
  11. ('赵六'),   
  12. ('钱七');   

5、测试

  1. mysql> select * from f1;   
  2. +------+-------+   
  3. | name | pykey |   
  4. +------+-------+   
  5. | 张三 | NULL |   
  6. | 李四 | NULL |   
  7. | 王五 | NULL |   
  8. | 赵六 | NULL |   
  9. | 钱七 | NULL |   
  10. +------+-------+   
  11. 5 rows in set (0.00 sec)   
  12. mysql> update f1 set pykey = hzcode(name);   
  13. Query OK, 5 rows affected (0.05 sec)   
  14. Rows matched: 5 Changed: 5 Warnings: 0   
  15. mysql> select * from f1;//开源代码phpfensi.com   
  16. +------+-------+   
  17. | name | pykey |   
  18. +------+-------+   
  19. | 张三 | Z |   
  20. | 李四 | L |   
  21. | 王五 | W |   
  22. | 赵六 | Z |   
  23. | 钱七 | Q |   
  24. +------+-------+   
  25. 5 rows in set (0.00 sec)   

这样就很方便地在MYSQL里查询汉字的首字母了,类似地也可以直接在MYSQL得到汉字拼音,不过需要拼音表,函数写法也不一样.

标签：

给我留言