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>

转载请注明：谷谷点程序 » mysql数据库中查询汉字的拼音首字母