mysql order by 排序技巧

整理一下 order by 的一些排序技巧：

首先我们新建表test，如下：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

插入对应的数据如下：

1.当 order by 中字段存在为null的字段时排序

asc 会将null 排在最前面 ，desc会将null排在最后面 ，这时如果我们想根据自己的想法在asc时将null排在最后或者在desc时将null排在最前方 我们可以如下操作：

SELECT * FROM `test` ORDER BY name is null,name;
或
SELECT * FROM `test` ORDER BY isnull(name),name;

这里的name is null 起到了一个隐藏排序的作用，具体我们看下即可理解了

SELECT isnull(name),t.* from test t;

查询结果如下：

到这大家应该都清楚了，isnull 当为对应的数据为null时 返回1，不为null时返回0，所以在上面实现了一个隐藏排序。

2.当我们需要排序的字符串为纯数字的时候，我们希望通过数值的大小进行排序的时候，有时候我们会发现一些奇怪的问题，如下：

select * from test order by isnull(name),name;

返回结果：

我们明明时按照name排序的，但是结果为什么是这样的呢？ 这是由于mysql对字符串进行排序的时候 先按最左边的开始排序然后在一位一位比下去，所以就导致了如下现象的发生了。这时如果我们想按照我们最初的想法取实现排序，我们可以通过如下方案实现：

（1）在name前加--

（2）在name排序时添加 +0操作

代码如下

select * from test order by isnull(name),--name;
或
select * from test order by isnull(name),name+0;
或
select * from test order by isnull(name),name*0;

结果如下：

这样也就得到了我们想要的结果，这其中呢 *0 或者 +0 都是将name中的字段 从 varchar 转换成 int 类型 然后就可以正常排序了

3.按日期排序，忽略年份

select date, description from table_name order by month(date),dayofmonth(date);

注意：单纯使用dayofyear来排序会导致错误，如2-29与非闰年的3-1日同等级

4.排序点分式IP

select ip from table_name order by inet_aton(ip);

或者在设计表时就使用 int unsigned 来表示ip。

5.将某列中特定值排在最前
例如想把表中lulu的名字排在最前显示，其他按字母排序
 

select name from table_name order by if(name='lulu',0,1),name ;

也可以把if的条件根据需要换成相应的语句。

由上我们可以看出，在进行order操作的时候 其实我们可以配合使用函数，逻辑判断等一些特殊手段来实现我们的需求，函数还有很多，例如substring_index\cast\field\substring 等，在这里就不一一列举了。