Posted by:
努力记

努力记,通过记录,计划,让自己的努力可以触碰彩虹!

8,386

Mysql:拼音首字母查询(超高性能)

  • 2013-06-01
  • 1 评论

注:不用新建表,查询性能高,可以兼容中英文

简单分析

  1. 应用场景:通常对地址或者数量较多的分类会选择首字母查询的方法
  2. 性能考虑:PHP中进行首字母查询,网上可以找到很多脚本实现,但需要将所有数据进行读出,在PHP代码中进行筛选,个人认为性能存在问题
  3. 数据库:SQL Server有对应的存储过程能实现到首字母查询,我日常工作主要使用的是Mysql,所以就对Mysql进行了一番操作
  4. 原理:其实首字母查询的原理大致相同,中文都是通过GBK下的ASNII码进行查询,这个和GBK编码的制定有关
  5. 目前大多数针对Mysql的方案还会选择建立一张字符编码表,用来解析首字母的关系,该操作在查询中是会读取全表的,性能肯定得不到满足

实际问题需考虑

  •  Mysql所需查询对应表的编码,通过原理知道需要的是GBK编码,所以当数据库是UTF-8等其他编码时需要进行转换
  • 所需查询的数据中是否有英文,因为英文的首字母查询规则不是一样的,需要特殊操作下
  • 就是通过Mysql的函数实现对字段的首个字符进行ASNII编码判断

首先放上最终的语句

select * FROM table where (ord(CONVERT(field USING gbk)) = 对应大写编码  or ord(CONVERT(field USING gbk)) = 对应小写编码  or ((ord(CONVERT(field USING gbk))-65535) >=初始范围  and (ord(CONVERT(field USING gbk))-65535) <= 结束范围 ))

分析以上语句:(基本语法不分析)

ord(CONVERT(field USING gbk))

  • 进行编码转换并获取首个字符的ASNII码
  • 如果表本身就是gbk编码,则可以写成:(ord(field))

4个判断

  • 对应大写编码,对应小写编码:主要针对表中是否有英文需求,只需相等即可
  • 初始范围,结束范围:针对中文需求,需要在2个数值之间
  • 根据自己的需求确定实际语句

获取以上的4个的值:这边我写了PHP函数用来处理该问题

/*
Start: 初始范围
End: 结束范围
Da:大写编码
Xiao:小写编码
*/
function getcharnum($str){
$str = strtoupper($str) ;//将字母转换成大写,因为下面是对大写字母判断
		switch($str){
		case 'A':
			$return_arr=array('start'=>'-20319','end'=>'-20284','da'=>'65','xiao'=>'97');
			break;
		case 'B':
			$return_arr=array('start'=>'-20283','end'=>'-19776','da'=>'66','xiao'=>'98');
			break;
		case 'C':
			$return_arr=array('start'=>'-19775','end'=>'-19776','da'=>'67','xiao'=>'99');
			break;
		case 'D':
			$return_arr=array('start'=>'-19218','end'=>'-18711','da'=>'68','xiao'=>'100');
			break;
		case 'E':
			$return_arr=array('start'=>'-18710','end'=>'-18527','da'=>'69','xiao'=>'101');
			break;
		case 'F':
			$return_arr=array('start'=>'-18526','end'=>'-18240','da'=>'70','xiao'=>'102');
			break;
		case 'G':
			$return_arr=array('start'=>'-18239','end'=>'-17923','da'=>'71','xiao'=>'103');
			break;
		case 'H':
			$return_arr=array('start'=>'-17922','end'=>'-17418','da'=>'72','xiao'=>'104');
			break;
		case 'J':
			$return_arr=array('start'=>'-17417','end'=>'-16475','da'=>'74','xiao'=>'106');
			break;
		case 'K':
			$return_arr=array('start'=>'-16474','end'=>'-16213','da'=>'75','xiao'=>'107');
			break;
		case 'L':
			$return_arr=array('start'=>'-16212','end'=>'-15641','da'=>'76','xiao'=>'108');
			break;
		case 'M':
			$return_arr=array('start'=>'-15640','end'=>'-15166','da'=>'77','xiao'=>'109');
			break;
		case 'N':
			$return_arr=array('start'=>'-15165','end'=>'-14923','da'=>'78','xiao'=>'110');
			break;
		case 'O':
			$return_arr=array('start'=>'-14922','end'=>'-14915','da'=>'79','xiao'=>'111');
			break;
		case 'P':
			$return_arr=array('start'=>'-14914','end'=>'-14631','da'=>'80','xiao'=>'112');
			break;
		case 'Q':
			$return_arr=array('start'=>'-14630','end'=>'-14150','da'=>'81','xiao'=>'113');
			break;
		case 'R':
			$return_arr=array('start'=>'-14149','end'=>'-14091','da'=>'82','xiao'=>'114');
			break;
		case 'S':
			$return_arr=array('start'=>'-14090','end'=>'-13319','da'=>'83','xiao'=>'115');
			break;
		case 'T':
			$return_arr=array('start'=>'-13318','end'=>'-12839','da'=>'84','xiao'=>'116');
			break;
		case 'W':
			$return_arr=array('start'=>'-12838','end'=>'-12557','da'=>'87','xiao'=>'119');
			break;
		case 'X':
			$return_arr=array('start'=>'-12556','end'=>'-11848','da'=>'88','xiao'=>'120');
			break;
		case 'Y':
			$return_arr=array('start'=>'-11847','end'=>'-11056','da'=>'89','xiao'=>'121');
			break;
		case 'Z':
			$return_arr=array('start'=>'-11055','end'=>'-10247','da'=>'90','xiao'=>'122');
			break;
		default:
			return false;
		}
		return $return_arr;
	}
$array = getcharnum(‘a’);
//通过获取对应首字母的4个编码,填入对应SQL语句中即可实现首字母查询

性能上:

  • Myqsl对于ord的判断十分的快速
  • 5W条数据在对应字段添加索引的情况下,响应时间在0.00x秒总的来说不慢


back up ↑

无觅相关文章插件,快速提升流量