mysq根据首字母模糊检索，拼音首字母模糊查询名称前言一、导入数据库二、使用SQL进行查询总结

239 阅读 0 评论 158 点赞

我是靠谱客的博主轻松夏天，这篇文章主要介绍mysq根据首字母模糊检索，拼音首字母模糊查询名称前言一、导入数据库二、使用SQL进行查询总结，现在分享给大家，希望可以做个参考。

Mysql 数据库根据拼音首字母模糊查询名称

前言
一、导入数据库
二、使用SQL进行查询
总结

前言

当我们的项目运行了一段时间，突然增加个需求，要按照内容标题的首字母进行查询时，这时我们只需导入一张表，一句sql语句就可搞定

提示：以下是本篇文章正文内容，下面案例可供参考

一、导入数据库

DROP TABLE IF EXISTS `app_tcosler`;

CREATE TABLE app_tcosler (
id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘id’,
fPY char(1) NOT NULL COMMENT ‘待查询的拼音’,
cBegin int(11) NOT NULL COMMENT ‘第一个汉字的编号’,
cEnd int(11) NOT NULL COMMENT ‘最后一个汉字的编号’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=gbk;

– Records of app_tcosler

INSERT INTO app_tcosler VALUES (‘1’, ‘A’, ‘45217’, ‘45252’);
INSERT INTO app_tcosler VALUES (‘2’, ‘B’, ‘45253’, ‘45760’);
INSERT INTO app_tcosler VALUES (‘3’, ‘C’, ‘45761’, ‘46317’);
INSERT INTO app_tcosler VALUES (‘4’, ‘D’, ‘46318’, ‘46825’);
INSERT INTO app_tcosler VALUES (‘5’, ‘E’, ‘46826’, ‘47009’);
INSERT INTO app_tcosler VALUES (‘6’, ‘F’, ‘47010’, ‘47296’);
INSERT INTO app_tcosler VALUES (‘7’, ‘G’, ‘47297’, ‘47613’);
INSERT INTO app_tcosler VALUES (‘8’, ‘H’, ‘47614’, ‘48118’);
INSERT INTO app_tcosler VALUES (‘9’, ‘J’, ‘48119’, ‘49061’);
INSERT INTO app_tcosler VALUES (‘10’, ‘K’, ‘49062’, ‘49323’);
INSERT INTO app_tcosler VALUES (‘11’, ‘L’, ‘49324’, ‘49895’);
INSERT INTO app_tcosler VALUES (‘12’, ‘M’, ‘49896’, ‘50370’);
INSERT INTO app_tcosler VALUES (‘13’, ‘N’, ‘50371’, ‘50613’);
INSERT INTO app_tcosler VALUES (‘14’, ‘O’, ‘50614’, ‘50621’);
INSERT INTO app_tcosler VALUES (‘15’, ‘P’, ‘50622’, ‘50905’);
INSERT INTO app_tcosler VALUES (‘16’, ‘Q’, ‘50906’, ‘51386’);
INSERT INTO app_tcosler VALUES (‘17’, ‘R’, ‘51387’, ‘51445’);
INSERT INTO app_tcosler VALUES (‘18’, ‘S’, ‘51446’, ‘52217’);
INSERT INTO app_tcosler VALUES (‘19’, ‘T’, ‘52218’, ‘52697’);
INSERT INTO app_tcosler VALUES (‘20’, ‘W’, ‘52698’, ‘52979’);
INSERT INTO app_tcosler VALUES (‘21’, ‘X’, ‘52980’, ‘53640’);
INSERT INTO app_tcosler VALUES (‘22’, ‘Y’, ‘53689’, ‘54480’);
INSERT INTO app_tcosler VALUES (‘23’, ‘Z’, ‘54481’, ‘55289’);

二、使用SQL进行查询

代码如下（示例）：

     SELECT p. * , c. *
           FROM app_game p, app_tcosler c
           WHERE CONV( HEX( LEFT( CONVERT( game_name
           USING gbk ) , 1 ) ) , 16, 10 )
           BETWEEN c.cBegin
           AND c.cEnd
           AND fPY = 'a'