mysql： 字符串排序问题（一）

mysql 实现high middle low排序

1 需求：

表 t_image

字段	字段类型	描述
image_id	bigint	图片id
image_url	varchar（1000）	图片url
image_quality	varchar（10）	图片质量： high/middle/low/unrated
image_click_count	bigint	图片点击数
image_downLoad_count	bigint	图片下载数
enable	tinyint（4）	状态（1 有效； 0 无效）

需求： 获取图片列表，分页， 并支持点击字段 image_quality /image_click_count /image_downLoad_count 实现升序和降序排序

2 实现难点：
在于根据 image_quality 实现升序/降序排序， 因为它本意时要求按照图片质量等级排序，
排序要求如下：
asc ： low - middle - high (unrated 不考虑）
desc: high - middle - low （unrated 不考虑）

但是 image_quality 是varchar类型的，
如果按照 mysql 中 order by image_quality asc（desc） 排序， 实际上根据ASCII码排序，
简单理解为根据字符顺序排序， 如果第一个字符相同时则根据第二个字母前后顺序排序。
排序结果如下：
asc ： high - low - middle
desc ： middle - low - high
很显然， 结果并不符合要求

3 解决方案

思路： 将相应字段转换成数字型， 使用case函数

	//Dao层：
	/**
	* 获取图片列表并分页
	* start: 第几页 ； size：每页数量； orderBy： 排序字段； orderModel：排序方式（asc/desc） 
	*/ 
	List<ImageDto> getImageList(@Param("start")Integer  start，@Param("size")Integer size，
								@Param("orderBy")String orderBy, @Param("orderModel")String orderModel）;
								
    
    //mybatis：
    <select  id ="getImageList" resultType = “imageDto”>
	    select 
	    	image_id as imageId，
	    	image_url as imageUrl,
	    	 (case  
	    	 	when image_quality  = 'high' then 3
	    	 	when image_quality  = 'middle' then 2
	    	 	when image_quality  = 'low' then 1
	    	 	else 0 
	    	 end ) as imageQualityOrder,
	        image_click_count  as imageClickCount,
			image_downLoad_count as imageDownLoadCount
		from t_image
		where 
		    enable = 1
			<choose>
				<when test="orderBy == 'imgae_quality'">
				    and  image_quality != 'unrated'
				    order by imageQualityOrder ${orderModel}
				</when>
				<otherwise>
	            	order by ${orderBy} ${orderModel}
	       	    </otherwise>
	   	 	</choose>
	    <if test="start >= 0 and size >0 ">
	        limit #{start}, #{size}
	    </if>
	</select>

建议： 如果考虑排序，在设计表字段的时候， 尽量考虑数据类型， 而不是字符串类型。

4 知识点

mysql： case when用法
Case具有两种格式。简单Case函数和Case搜索函数。

（1）简单Case函数

	CASE sex 
	         WHEN '1' THEN '我的' 
	         WHEN '2' THEN '你的' 
	ELSE '其他' END 

（2）Case搜索函数

	CASE WHEN sex = '1' THEN '男' 
	     WHEN sex = '2' THEN '女' 
	ELSE '其他' END