《MYSQL数据库mysql根据英文首字母查询汉字函数示例》要点: 本文介绍了MYSQL数据库mysql根据英文首字母查询汉字函数示例,希望对您有用。如果有疑问,可以联系我们。
导读:在mysql数据库中,实现根据英文首字母查询汉字,方法如下.方法一:建一个拼音表 t_cosler,存放每个字母开头的第一个汉字的编号和最后...
在mysql数据库中,方法如下.MYSQL实例
方法一: 建一个拼音表 t_cosler,存放每个字母开头的第一个汉字的编号和最后一个汉字的编号. ?MYSQL实例
+------+--------+-------+ | f_PY | cBegin | cEnd? | +------+--------+-------+ | A??? |? 45217 | 45252 | | B??? |? 45253 | 45760 | ..... | Z??? |? 54481 | 55289 | +------+--------+-------+
然后,直接查询: ?MYSQL实例
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 (0.09 sec)MYSQL实例
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 (0.16 sec) Records: 23? Duplicates: 0? Warnings: 0MYSQL实例
mysql> select * from? o_personnel; +------+------------+ | A_Id | A_UserName | +------+------------+ |??? 1 | 首先?????? | |??? 2 | 检查?????? | |??? 3 | 我们?????? | |??? 4 | 的二?????? | |??? 5 | 进制?????? | |??? 6 | 是否?????? | |??? 7 | 适合?????? | |??? 8 | 你的?????? | |??? 9 | 平台?????? | +------+------------+ 9 rows in set (0.00 sec)MYSQL实例
mysql> select p.*,c.* ??? -> from o_personnel p,t_cosler c ??? -> where? CONV(HEX(left(A_UserName,1)),16,10) between c.cBegin and c.cEnd; +------+------------+------+--------+-------+ | 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 (0.00 sec)MYSQL实例
例如,查S开头: ?MYSQL实例
mysql> select p.* ??? -> from o_personnel p,10) between c.cBegin and c.cEnd ??? -> and c.f_PY='S'; +------+------------+ | A_Id | A_UserName | +------+------------+ |??? 1 | 首先?????? | |??? 6 | 是否?????? | |??? 7 | 适合?????? | +------+------------+ 3 rows in set (0.00 sec) (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|