我在网上搜了一些方法,如下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); 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 ;
但是在执行到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;时,总是得不到应有的hz_code,比如执行select ord(substring('火',1,1))*256+ord(substring('火',2,1))-65536 得到的值是3883969280,根本不在取值范围中
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);
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 ;
select py into hz_py from pyk where hz_code>=pyk.hz1 and hz_code<=pyk
.hz2;时,总是得不到应有的hz_code,比如执行select ord(substring('火',1,1))*256+ord(substring('火',2,1))-65536
得到的值是3883969280,根本不在取值范围中
mysql 根据英文首字母来查询汉字函数 [推荐]