CREATE DEFINER=`root`@`localhost` PROCEDURE `queryPinyinHan`( IN `pinyin_` VARCHAR ( 20 ) charset 'utf32' )
    DETERMINISTIC
    COMMENT '通过全拼查询汉字'
BEGIN
-- # 使用方法
-- # call queryPinyinHan("chuāng"); 
-- # 去掉空格
SET @str_=""; 
set @j=1;
-- # 去掉空格
SELECT REPLACE(pinyin_,' ','') into @pin_ ;
-- # 计算有多少个拼音
SELECT LENGTH(@pin_)  / 4 into @count; 	

WHILE @j <= @count DO		
    SELECT SUBSTRING(@pin_,@j,1) into @p_;
    SELECT CONV(HEX(CONVERT(@p_ using 'utf32')) ,16,10)  into @s_;
    SELECT CONCAT(CONVERT(@str_ USING 'utf32'),@s_) into @str_;
    SET @j=@j+1;
END WHILE;
	
SELECT hex(`dec_`) as utf32,CONVERT(UNHEX(CONV(`dec_`,10,16)) using 'utf32') as `han`,GROUP_CONCAT(CONVERT(UNHEX(CONV(`pin_`,10,16)) using 'utf32')  ORDER BY `id` asc SEPARATOR '') as `py`,GROUP_CONCAT(`pin_` ORDER BY `id` asc SEPARATOR '') as `deci` from p_pinyin as p GROUP BY `dec_`,`group_`  HAVING `deci`=@str_;

END;