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

小潘seo 重庆小潘seo博客和你一起学习SEO知识,共同分享SEO优化~

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

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

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

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

  1. DELIMITER$$
  2. CREATEFUNCTION`PYFIRST`(P_NAMEVARCHAR(255))RETURNSvarchar(255)CHARSETutf8
  3. BEGIN
  4. DECLAREV_RETURNVARCHAR(255);
  5. SETV_RETURN=ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAMEUSINGgbk),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. RETURNV_RETURN;
  10. END$$
  11. DELIMITER;
  12. DELIMITER$$
  13. CREATEFUNCTION`PY`(P_NAMEVARCHAR(255))RETURNSvarchar(255)CHARSETutf8
  14. BEGIN
  15. DECLAREV_COMPAREVARCHAR(255);
  16. DECLAREV_RETURNVARCHAR(255);
  17. DECLAREIINT;
  18. SETI=1;
  19. SETV_RETURN='';
  20. whileI<LENGTH(P_NAME)do
  21. SETV_COMPARE=SUBSTR(P_NAME,I,1);
  22. IF(V_COMPARE!='')THEN
  23. #SETV_RETURN=CONCAT(V_RETURN,',',V_COMPARE);
  24. SETV_RETURN=CONCAT(V_RETURN,PYFIRST(V_COMPARE));
  25. #SETV_RETURN=PYFIRST(V_COMPARE);
  26. ENDIF;
  27. SETI=I+1;
  28. endwhile;
  29. IF(ISNULL(V_RETURN)orV_RETURN='')THEN
  30. SETV_RETURN=P_NAME;
  31. ENDIF;
  32. RETURNV_RETURN;
  33. END$$
  34. DELIMITER;

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

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

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

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

  1. DROPFUNCTIONIFEXISTShzcode;
  2. delimiter//
  3. CREATEFUNCTIONhzcode(sCHAR(255))RETURNSchar
  4. BEGIN
  5. DECLAREhz_codeint;
  6. DECLAREhz_pychar;
  7. SEThz_code=ord(substring(s,1,1))*256+ord(substring(s,2,1))-65536;
  8. selectpyintohz_pyfrompykwherehz_code>=pyk.hz1andhz_code<=pyk
  9. .hz2;
  10. RETURNhz_py;
  11. END
  12. //

delimiter ;

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

  1. delimiter$$
  2. DROPFUNCTIONIFEXISTS`hzcode`$$
  3. CREATEFUNCTION`hzcode`(sCHAR(255))RETURNSchar
  4. BEGIN
  5. DECLAREhz_codeint;
  6. DECLAREhz_pychar;
  7. declarestrvarchar(400);
  8. SEThz_code=ord(substring(s,1,1))-65536;
  9. selectpyintohz_pyfrompykwherehz_code>=pyk.hz1andhz_code<=pyk.hz2;
  10. RETURNhz_py;
  11. END$$
  12. delimiter$$

3、先测试一下

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

4、建立个测试表

  1. DROPTABLEIFEXISTS`f1`;
  2. createtablef1(
  3. namevarchar(30),
  4. pykeyvarchar(1)
  5. );
  6. insertintof1(name)values
  7. ('张三'),
  8. ('李四'),
  9. ('王五'),
  10. ('赵六'),
  11. ('钱七');

5、测试

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

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

相关广告
  • mysql自定义函数实现中文首字母拼音查询 mysql自定义函数实现中文首字母拼音查询 mysql自定义函数实现中文首字母拼音查询
相关阅读

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

2019/10/10 17:37:53 | 谷歌SEO算法 | 小明SEO博客