mysql 拼音查询_在mysql 里实现查询汉字的拼音首字母

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),

('I',-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),

('V',-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 ;

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

分享到：

2008-02-20 14:36

浏览 5005

评论