mysql 汉字拼音首字母查询_mysql数据库中查询汉字的拼音首字母

mysql数据库中查询汉字的拼音首字母

create table wkcx_cosler(

f_PY char primary key,

cBegin  SMALLINT UNSIGNED not null,

cEnd    SMALLINT UNSIGNED not null

);

INSERT INTO wkcx_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);

SELECT *

FROM wkcx_content AS p,

wkcx_cosler AS c

WHERE CONV(HEX(LEFT(CONVERT(p.news_title USING gbk),1)),16,10)  BETWEEN c.cBegin

AND c.cEnd AND c.f_PY='S'

create table wkcx_cosler(

f_PY char primary key,

cBegin  SMALLINT UNSIGNED not null,

cEnd    SMALLINT UNSIGNED not null

);

insert into wkcx_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);

DROP TABLE IF EXISTS `orderbyname`;

CREATE TABLE `orderbyname` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(255) character set gb2312 default NULL,

PRIMARY KEY  (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------

-- Records

-- ----------------------------

INSERT INTO `orderbyname` VALUES ('1', 'asc');

INSERT INTO `orderbyname` VALUES ('2', 'bsf');

INSERT INTO `orderbyname` VALUES ('3', 'aaa');

INSERT INTO `orderbyname` VALUES ('4', 'avg');

INSERT INTO `orderbyname` VALUES ('5', 'bbb');

INSERT INTO `orderbyname` VALUES ('6', 'cdf');

INSERT INTO `orderbyname` VALUES ('7', 'deg');

INSERT INTO `orderbyname` VALUES ('8', 'ddd');

INSERT INTO `orderbyname` VALUES ('9', '王');

INSERT INTO `orderbyname` VALUES ('10', '王强');

INSERT INTO `orderbyname` VALUES ('11', 'wwww');

INSERT INTO `orderbyname` VALUES ('12', 'w强');

INSERT INTO `orderbyname` VALUES ('13', '啊');

INSERT INTO `orderbyname` VALUES ('14', '吧');

INSERT INTO `orderbyname` VALUES ('15', '车');

INSERT INTO `orderbyname` VALUES ('16', '奥');

INSERT INTO `orderbyname` VALUES ('17', '爱');

INSERT INTO `orderbyname` VALUES ('18', 'avvvv');

//按汉字排序查询

from orderbyname p , wkcx_cosler c

where  CONV(HEX(left(name,1)),16,10) between c.cBegin and c.cEnd order by name;

select p.*

from orderbyname p , wkcx_cosler c

where  CONV(HEX(left(name,1)),16,10) between c.cBegin and c.cEnd

and c.f_PY='a' order by name;

结果：

mysql> select p.*

from orderbyname p , wkcx_cosler c

where  CONV(HEX(left(name,1)),16,10) between c.cBegin and c.cEnd

and c.f_PY='a' order by name;

+----+------+

| id | name |

+----+------+

| 13 | 啊   |

| 17 | 爱   |

| 16 | 奥   |

+----+------+

3 rows in set

mysql> select p.*

from orderbyname p , wkcx_cosler c

where  CONV(HEX(left(name,1)),16,10) between c.cBegin and c.cEnd

and c.f_PY='b' order by name;

+----+------+

| id | name |

+----+------+

| 14 | 吧   |

+----+------+

1 row in set

mysql>