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

在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;
13. DELIMITER$$
14. CREATEFUNCTION`PY`(P_NAMEVARCHAR(255))RETURNSvarchar(255)CHARSETutf8
15. BEGIN
16. DECLAREV_COMPAREVARCHAR(255);
17. DECLAREV_RETURNVARCHAR(255);
18. DECLAREIINT;
20. SETI=1;
21. SETV_RETURN='';
22. whileI<LENGTH(P_NAME)do
23. SETV_COMPARE=SUBSTR(P_NAME,I,1);
24. IF(V_COMPARE!='')THEN
25. #SETV_RETURN=CONCAT(V_RETURN,',',V_COMPARE);
26. SETV_RETURN=CONCAT(V_RETURN,PYFIRST(V_COMPARE));
27. #SETV_RETURN=PYFIRST(V_COMPARE);
28. ENDIF;
29. SETI=I+1;
30. endwhile;
32. IF(ISNULL(V_RETURN)orV_RETURN='')THEN
33. SETV_RETURN=P_NAME;
34. ENDIF;
36. RETURNV_RETURN;
37. END$$
38. 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. );
8. INSERTINTO`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. DROPFUNCTIONIFEXISTShzcode;
3. delimiter//
5. CREATEFUNCTIONhzcode(sCHAR(255))RETURNSchar
6. BEGIN
7. DECLAREhz_codeint;
8. DECLAREhz_pychar;
9. SEThz_code=ord(substring(s,1,1))*256+ord(substring(s,2,1))-65536;
10. selectpyintohz_pyfrompykwherehz_code>=pyk.hz1andhz_code<=pyk
11. .hz2;
12. RETURNhz_py;
13. END
14. //

delimiter ;

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

1. delimiter$$
3. DROPFUNCTIONIFEXISTS`hzcode`$$
4. CREATEFUNCTION`hzcode`(sCHAR(255))RETURNSchar
5. BEGIN
6. DECLAREhz_codeint;
7. DECLAREhz_pychar;
8. declarestrvarchar(400);
9. SEThz_code=ord(substring(s,1,1))-65536;
10. selectpyintohz_pyfrompykwherehz_code>=pyk.hz1andhz_code<=pyk.hz2;
11. RETURNhz_py;
13. END$$
14. 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. );
7. insertintof1(name)values
8. ('张三'),
9. ('李四'),
10. ('王五'),
11. ('赵六'),
12. ('钱七');

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得到汉字拼音,不过需要拼音表,函数写法也不一样.