mysql 根據英文首字母來查詢漢字函數

標籤：style   blog   color   os   ar   sp   div   on   log   

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

$sql = "select p.* from o_personnel as p , t_cosler as c where  CONV(HEX(left(CONVERT(p.A_UserName USING gbk),1)),16,10) between c.cBegin and c.cEnd and c.f_PY=‘".$_GET[‘zm‘]."‘ or left(p.A_UserName,1)=c.f_PY and c.f_PY=‘".$_GET[‘zm‘]."‘";

 

mysql 根據英文首字母來查詢漢字函數