投稿
  1. 首页
  2. 安全

Mysql按汉语拼音首字母查询数据

barometer • 2023-7-30 • 安全 • 阅读 49

Mysql按汉语拼音首字母查询数据,第1张

网络上类似的代码大多只能在gb2312编码下使用，下面这个类同时能在utf-8编码下将汉字转换为拼音，具体的代码和用法如下：
<php
function Pinyin($_String, $_Code='gb2312')
{
$_DataKey = "a|ai|an|ang|ao|ba|bai|ban|bang|bao|bei|ben|beng|bi|bian|biao|bie|bin|bing|bo|bu|ca|cai|can|cang|cao|ce|ceng|cha"
"|chai|chan|chang|chao|che|chen|cheng|chi|chong|chou|chu|chuai|chuan|chuang|chui|chun|chuo|ci|cong|cou|cu|"
"cuan|cui|cun|cuo|da|dai|dan|dang|dao|de|deng|di|dian|diao|die|ding|diu|dong|dou|du|duan|dui|dun|duo|e|en|er"
"|fa|fan|fang|fei|fen|feng|fo|fou|fu|ga|gai|gan|gang|gao|ge|gei|gen|geng|gong|gou|gu|gua|guai|guan|guang|gui"
"|gun|guo|ha|hai|han|hang|hao|he|hei|hen|heng|hong|hou|hu|hua|huai|huan|huang|hui|hun|huo|ji|jia|jian|jiang"
"|jiao|jie|jin|jing|jiong|jiu|ju|juan|jue|jun|ka|kai|kan|kang|kao|ke|ken|keng|kong|kou|ku|kua|kuai|kuan|kuang"
"|kui|kun|kuo|la|lai|lan|lang|lao|le|lei|leng|li|lia|lian|liang|liao|lie|lin|ling|liu|long|lou|lu|lv|luan|lue"
"|lun|luo|ma|mai|man|mang|mao|me|mei|men|meng|mi|mian|miao|mie|min|ming|miu|mo|mou|mu|na|nai|nan|nang|nao|ne"
"|nei|nen|neng|ni|nian|niang|niao|nie|nin|ning|niu|nong|nu|nv|nuan|nue|nuo|o|ou|pa|pai|pan|pang|pao|pei|pen"
"|peng|pi|pian|piao|pie|pin|ping|po|pu|qi|qia|qian|qiang|qiao|qie|qin|qing|qiong|qiu|qu|quan|que|qun|ran|rang"
"|rao|re|ren|reng|ri|rong|rou|ru|ruan|rui|run|ruo|sa|sai|san|sang|sao|se|sen|seng|sha|shai|shan|shang|shao|"
"she|shen|sheng|shi|shou|shu|shua|shuai|shuan|shuang|shui|shun|shuo|si|song|sou|su|suan|sui|sun|suo|ta|tai|"
"tan|tang|tao|te|teng|ti|tian|tiao|tie|ting|tong|tou|tu|tuan|tui|tun|tuo|wa|wai|wan|wang|wei|wen|weng|wo|wu"
"|xi|xia|xian|xiang|xiao|xie|xin|xing|xiong|xiu|xu|xuan|xue|xun|ya|yan|yang|yao|ye|yi|yin|ying|yo|yong|you"
"|yu|yuan|yue|yun|za|zai|zan|zang|zao|ze|zei|zen|zeng|zha|zhai|zhan|zhang|zhao|zhe|zhen|zheng|zhi|zhong|"
"zhou|zhu|zhua|zhuai|zhuan|zhuang|zhui|zhun|zhuo|zi|zong|zou|zu|zuan|zui|zun|zuo";
$_DataValue = "-20319|-20317|-20304|-20295|-20292|-20283|-20265|-20257|-20242|-20230|-20051|-20036|-20032|-20026|-20002|-19990"
"|-19986|-19982|-19976|-19805|-19784|-19775|-19774|-19763|-19756|-19751|-19746|-19741|-19739|-19728|-19725"
"|-19715|-19540|-19531|-19525|-19515|-19500|-19484|-19479|-19467|-19289|-19288|-19281|-19275|-19270|-19263"
"|-19261|-19249|-19243|-19242|-19238|-19235|-19227|-19224|-19218|-19212|-19038|-19023|-19018|-19006|-19003"
"|-18996|-18977|-18961|-18952|-18783|-18774|-18773|-18763|-18756|-18741|-18735|-18731|-18722|-18710|-18697"
"|-18696|-18526|-18518|-18501|-18490|-18478|-18463|-18448|-18447|-18446|-18239|-18237|-18231|-18220|-18211"
"|-18201|-18184|-18183|-18181|-18012|-17997|-17988|-17970|-17964|-17961|-17950|-17947|-17931|-17928|-17922"
"|-17759|-17752|-17733|-17730|-17721|-17703|-17701|-17697|-17692|-17683|-17676|-17496|-17487|-17482|-17468"
"|-17454|-17433|-17427|-17417|-17202|-17185|-16983|-16970|-16942|-16915|-16733|-16708|-16706|-16689|-16664"
"|-16657|-16647|-16474|-16470|-16465|-16459|-16452|-16448|-16433|-16429|-16427|-16423|-16419|-16412|-16407"
"|-16403|-16401|-16393|-16220|-16216|-16212|-16205|-16202|-16187|-16180|-16171|-16169|-16158|-16155|-15959"
"|-15958|-15944|-15933|-15920|-15915|-15903|-15889|-15878|-15707|-15701|-15681|-15667|-15661|-15659|-15652"
"|-15640|-15631|-15625|-15454|-15448|-15436|-15435|-15419|-15416|-15408|-15394|-15385|-15377|-15375|-15369"
"|-15363|-15362|-15183|-15180|-15165|-15158|-15153|-15150|-15149|-15144|-15143|-15141|-15140|-15139|-15128"
"|-15121|-15119|-15117|-15110|-15109|-14941|-14937|-14933|-14930|-14929|-14928|-14926|-14922|-14921|-14914"
"|-14908|-14902|-14894|-14889|-14882|-14873|-14871|-14857|-14678|-14674|-14670|-14668|-14663|-14654|-14645"
"|-14630|-14594|-14429|-14407|-14399|-14384|-14379|-14368|-14355|-14353|-14345|-14170|-14159|-14151|-14149"
"|-14145|-14140|-14137|-14135|-14125|-14123|-14122|-14112|-14109|-14099|-14097|-14094|-14092|-14090|-14087"
"|-14083|-13917|-13914|-13910|-13907|-13906|-13905|-13896|-13894|-13878|-13870|-13859|-13847|-13831|-13658"
"|-13611|-13601|-13406|-13404|-13400|-13398|-13395|-13391|-13387|-13383|-13367|-13359|-13356|-13343|-13340"
"|-13329|-13326|-13318|-13147|-13138|-13120|-13107|-13096|-13095|-13091|-13076|-13068|-13063|-13060|-12888"
"|-12875|-12871|-12860|-12858|-12852|-12849|-12838|-12831|-12829|-12812|-12802|-12607|-12597|-12594|-12585"
"|-12556|-12359|-12346|-12320|-12300|-12120|-12099|-12089|-12074|-12067|-12058|-12039|-11867|-11861|-11847"
"|-11831|-11798|-11781|-11604|-11589|-11536|-11358|-11340|-11339|-11324|-11303|-11097|-11077|-11067|-11055"
"|-11052|-11045|-11041|-11038|-11024|-11020|-11019|-11018|-11014|-10838|-10832|-10815|-10800|-10790|-10780"
"|-10764|-10587|-10544|-10533|-10519|-10331|-10329|-10328|-10322|-10315|-10309|-10307|-10296|-10281|-10274"
"|-10270|-10262|-10260|-10256|-10254";
$_TDataKey = explode('|', $_DataKey);
$_TDataValue = explode('|', $_DataValue);
$_Data = (PHP_VERSION>='50') array_combine($_TDataKey, $_TDataValue) : _Array_Combine($_TDataKey, $_TDataValue);
arsort($_Data);
reset($_Data);
if($_Code != 'gb2312') $_String = _U2_Utf8_Gb($_String);
$_Res = '';
for($i=0; $i<strlen($_String); $i++)
{
$_P = ord(substr($_String, $i, 1));
if($_P>160) { $_Q = ord(substr($_String, ++$i, 1)); $_P = $_P256 + $_Q - 65536; }
$_Res = _Pinyin($_P, $_Data);
}
return preg_replace("/[^a-z0-9]/", '', $_Res);
}
function _Pinyin($_Num, $_Data)
{
if ($_Num>0 && $_Num<160 ) return chr($_Num);
elseif($_Num<-20319 || $_Num>-10247) return '';
else {
foreach($_Data as $k=>$v){ if($v<=$_Num) break; }
return $k;
}
}
function _U2_Utf8_Gb($_C)
{
$_String = '';
if($_C < 0x80) $_String = $_C;
elseif($_C < 0x800)
{
$_String = chr(0xC0 | $_C>>6);
$_String = chr(0x80 | $_C & 0x3F);
}elseif($_C < 0x10000){
$_String = chr(0xE0 | $_C>>12);
$_String = chr(0x80 | $_C>>6 & 0x3F);
$_String = chr(0x80 | $_C & 0x3F);
} elseif($_C < 0x200000) {
$_String = chr(0xF0 | $_C>>18);
$_String = chr(0x80 | $_C>>12 & 0x3F);
$_String = chr(0x80 | $_C>>6 & 0x3F);
$_String = chr(0x80 | $_C & 0x3F);
}
return iconv('UTF-8', 'GB2312', $_String);
}
function _Array_Combine($_Arr1, $_Arr2)
{
for($i=0; $i<count($_Arr1); $i++) $_Res[$_Arr1[$i]] = $_Arr2[$i];
return $_Res;
}
//用法：
//第二个参数留空则为gb1232编码
echo Pinyin('中国站长天空');
//第二个参数随意设置则为utf-8编码
echo Pinyin('中国站长天空',1);
>

方法一：建一个拼音表 t_cosler ，存放每个字母开头的第一个汉字的编号和最后一个汉字的编号。 +------+--------+-------+ | f_PY | cBegin | cEnd | +------+--------+-------+ | A | 45217 | 45252 | | B | 45253 | 45760 | | Z | 54481 | 55289 | +------+--------+-------+ 然后直接查询就行了。
mysql> create table t_cosler( -> f_PY char primary key, -> cBegin SMALLINT UNSIGNED not null, -> cEnd SMALLINT UNSIGNED not null -> ); Query OK, 0 rows affected (009 sec) mysql> insert into t_cosler values -> ('A',0xB0A1,0xB0C4), -> ('B',0xB0C5,0xB2C0), -> ('C',0xB2C1,0xB4ED), -> ('D',0xB4EE,0xB6E9), -> ('E',0xB6EA,0xB7A1), -> ('F',0xB7A2,0xB8C0), -> ('G',0xB8C1,0xB9FD), -> ('H',0xB9FE,0xBBF6), -> ('J',0xBBF7,0xBFA5), -> ('K',0xBFA6,0xC0AB), -> ('L',0xC0AC,0xC2E7), -> ('M',0xC2E8,0xC4C2), -> ('N',0xC4C3,0xC5B5), -> ('O',0xC5B6,0xC5BD), -> ('P',0xC5BE,0xC6D9), -> ('Q',0xC6DA,0xC8BA), -> ('R',0xC8BB,0xC8F5), -> ('S',0xC8F6,0xCBF9), -> ('T',0xCBFA,0xCDD9), -> ('W',0xCDDA,0xCEF3), -> ('X',0xCEF4,0xD188), -> ('Y',0xD1B9,0xD4D0), -> ('Z',0xD4D1,0xD7F9); Query OK, 23 rows affected (016 sec) Records: 23 Duplicates: 0 Warnings: 0 mysql> select from o_personnel; +------+------------+ | A_Id | A_UserName | +------+------------+ | 1 | 首先 | | 2 | 检查 | | 3 | 我们 | | 4 | 的二 | | 5 | 进制 | | 6 | 是否 | | 7 | 适合 | | 8 | 你的 | | 9 | 平台 | +------+------------+ 9 rows in set (000 sec) mysql> select p,c -> from o_personnel p , t_cosler c -> where CONV(HEX(left(A_UserName,1)),16,10) between ccBegin and ccEnd; +------+------------+------+--------+-------+ | A_Id | A_UserName | f_PY | cBegin | cEnd | +------+------------+------+--------+-------+ | 4 | 的二 | D | 46318 | 46825 | | 2 | 检查 | J | 48119 | 49061 | | 5 | 进制 | J | 48119 | 49061 | | 8 | 你的 | N | 50371 | 50613 | | 9 | 平台 | P | 50622 | 50905 | | 1 | 首先 | S | 51446 | 52217 | | 6 | 是否 | S | 51446 | 52217 | | 7 | 适合 | S | 51446 | 52217 | | 3 | 我们 | W | 52698 | 52979 | +------+------------+------+--------+-------+ 9 rows in set (000 sec) mysql> 查Ｓ开头的
mysql> select p -> from o_personnel p , t_cosler c -> where CONV(HEX(left(A_UserName,1)),16,10) between ccBegin and ccEnd -> and cf_PY='S'; +------+------------+ | A_Id | A_UserName | +------+------------+ | 1 | 首先 | | 6 | 是否 | | 7 | 适合 | +------+------------+ 3 rows in set (000 sec) mysql> 方法二：不用这个t_cosler表，直接写个函数权限汉字得到拼音。
局限性：　以上方法，均依照汉字区位表来实现，对区位后面的复杂字，无法准确判断，对多音字无法准确判断。
方法三：从微软拼音中导出所有汉字的拼音表。方法四不建表 mysql> SELECT , -> ELT(INTERVAL(CONV(HEX(left(A_UserName,1)),16,10), -> 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0 xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCE F4,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') as PY -> from o_personnel; +------+------------+------+ | A_Id | A_UserName | PY | +------+------------+------+ | 1 | 首先 | S | | 2 | 检查 | J | | 3 | 我们 | W | | 4 | 的二 | D | | 5 | 进制 | J | | 6 | 是否 | S | | 7 | 适合 | S | | 8 | 你的 | N | | 9 | 平台 | P | +------+------------+------+ 9 rows in set (000 sec) mysql> 方法五： mysql> SELECT , -> CHAR(INTERVAL(CONV(HEX(left(A_UserName,1)),16,10), -> 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE, -> 0xBBF7,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE, -> 0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCDDA,0xCDDA,0xCEF4, -> 0xD1B9,0xD4D1)+64) as PY -> from o_personnel; +------+------------+------+ | A_Id | A_UserName | PY | +------+------------+------+ | 1 | 首先 | S | | 2 | 检查 | J | | 3 | 我们 | W | | 4 | 的二 | D | | 5 | 进制 | J | | 6 | 是否 | S | | 7 | 适合 | S | | 8 | 你的 | N | | 9 | 平台 | P | +------+------------+------+ 9 rows in set (000 sec) mysql>

第一步，怎么做才能实现目的。
第二步，在界面处理还是在数据库处理哪个效率更高。
可以在数据库写一个函数，将数据库里面的中文条目转换成拼音，写语句用"like"进行模糊查询。
或者在界面先查出一个数据集，写一个方法处理数据集里面的中文条目转换为拼音，在模糊查出。
至于有没其他方法，就没有细想了。
一般上，我自己的做法是能在界面处理的事情尽量在界面处理好不留到数据库去处理。

应该需要把汉字对应的拼音先存在数据表，然后查询吧；
查询语句是：select 数据库中存储的中文名字 from 名字表 where 数据库中英文名 like 输入的英文；

这个问题比较棘手，因为你需要在“被查询的汉字”和“这些汉字的第一个字母”之间建立联系
，而“nh”不只是对应“你好”，还对应“暖和”等等，所以几乎不可能实现。就算你用智能ABC输入法
的逻辑来把字符转换为相应汉字，还有好多分支处理！

具体怎么写也不太清楚，但是我以我所学的！应该可以用ORACLE里面的自定义函数方法（java语言的）写一个首字转换拼音！
要不就是把头文字换成码位，文字的排序跟拼音有关！判断的其范围以断定字母！（在JS语言文字按字母排列时候看到的）

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/yw/13407283.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
barometer barometer 一级用户组
0 0
上一篇 2023-07-30
下一篇 2023-07-30

发表评论

登录后才能评论

评论列表（0条）

保存