MySQL中order by的使用详情

吾爱主题阅读：93 2024-04-02 08:07:13 评论：0

1、简介

在使用select语句时可以结合order by对查询的数据进行排序。如果不使用order by默认情况下mysql返回的数据集，与它在底层表中的顺序相同，可能与你添加数据到表中的顺序一致，也可能不一致（在你对表进行修改、删除等操作时mysql会对内存进行整理，此时数据的顺序会发生改变）因此如果我们希望得到的数据是有顺序的，就应该明确排序方式。

2、正文

首先准备一张user表，ddl和表数据如下所示，可以直接复制使用。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 set names utf8mb4; set foreign_key_checks = 0; -- ---------------------------- -- table structure for user -- ---------------------------- drop table if exists ` user `; create table ` user ` ( `id` bigint (20) not null auto_increment comment '主键' , ` name ` varchar (255) character set utf8 collate utf8_general_ci not null comment '用户名' , `age` int (11) not null comment '年龄' , `sex` smallint (6) not null comment '性别' , primary key (`id`) using btree ) engine = innodb auto_increment = 8 character set = utf8 collate = utf8_general_ci row_format = dynamic ; -- ---------------------------- -- records of user -- ---------------------------- insert into ` user ` values (1, '李子捌' , 18, 1); insert into ` user ` values (2, '张三' , 22, 1); insert into ` user ` values (3, '李四' , 38, 1); insert into ` user ` values (4, '王五' , 25, 1); insert into ` user ` values (5, '六麻子' , 13, 0); insert into ` user ` values (6, '田七' , 37, 1); insert into ` user ` values (7, '谢礼' , 18, 1); set foreign_key_checks = 1;

数据的初始顺序如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> select * from user ; + ----+--------+-----+-----+ | id | name | age | sex | + ----+--------+-----+-----+ | 1 | 李子捌 | 18 | 1 | | 2 | 张三 | 22 | 1 | | 3 | 李四 | 38 | 1 | | 4 | 王五 | 25 | 1 | | 5 | 六麻子 | 13 | 0 | | 6 | 田七 | 37 | 1 | | 7 | 谢礼 | 18 | 1 | + ----+--------+-----+-----+ 7 rows in set (0.00 sec)

2.1 单个列排序

我们首先来看使用order by对单个列进行排序。

需求：

根据用户年龄进行升序排序。

语句：

1	`select` `*` `from` `user` `order` `by` `age;`

结果：

1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> select * from user order by age; + ----+--------+-----+-----+ | id | name | age | sex | + ----+--------+-----+-----+ | 5 | 六麻子 | 13 | 0 | | 1 | 李子捌 | 18 | 1 | | 7 | 谢礼 | 18 | 1 | | 2 | 张三 | 22 | 1 | | 4 | 王五 | 25 | 1 | | 6 | 田七 | 37 | 1 | | 3 | 李四 | 38 | 1 | + ----+--------+-----+-----+ 7 rows in set (0.00 sec)

分析：

可以看到user表输出顺序按照age升序排序输出，因此可以猜测mysql默认的排序方式是升序。此外我这里使用*通配符查询所有列，并未明确指明查询age列；其实mysql的order by后跟的列不一定要查询出来，比如 select name from user order by age;这样也是一样的效果。

2.2 多个列排序

order by不仅可以对单个列进行排序，它也可以对多个列进行排序，只需要把需要排序的列依次跟在order by之后就可以了。

在测试之前，我们先往表中添加一条年龄相等的数据

1 2	`mysql>` `insert` `into` `user` `(` `name` `, age, sex)` `values` `(` `'李子柒'` `, 18, 1);` `query ok, 1 row affected (0.01 sec)`

需求：

根据用户年龄升序排序之后再更加用户名称排序。

语句：

1	`select` `*` `from` `user` `order` `by` `age,` `name` `;`

结果：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> select * from user order by age, name ; + ----+--------+-----+-----+ | id | name | age | sex | + ----+--------+-----+-----+ | 5 | 六麻子 | 13 | 0 | | 1 | 李子捌 | 18 | 1 | | 8 | 李子柒 | 18 | 1 | | 7 | 谢礼 | 18 | 1 | | 2 | 张三 | 22 | 1 | | 4 | 王五 | 25 | 1 | | 6 | 田七 | 37 | 1 | | 3 | 李四 | 38 | 1 | + ----+--------+-----+-----+ 8 rows in set (0.00 sec)

分析：

order by可以作用于多个列，mysql会完全根据order by后列的顺序进行排序。不过mysql对于中文的排序需要考虑数据库字符集编码的问题，如果不是很懂建议不要对中文进行排序，因为大多数情况我们需要得到的是拼音的排序结果，但是往往不尽人意哦！此外这里我们在age和name后都是没有跟排序方式的，所以默认都是升序，先根据age升序排序之后再根据name升序排序。如果需要使用降序，则需要指明desc，比如 select id, name, age from user order by age, name desc; 。

2.3 排序的方式

order by有两种排序方式，它们分别是：

asc -> 升序排序（默认排序方式）
desc -> 降序排序

注意： 在上面说了order by对多个列进行排序，排序方式只会作用于一个列，比如你需要对user表中的数据同时按照age和name进行降序，就应该两个列都指明降序。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> select * from user order by age desc , name desc ; + ----+--------+-----+-----+ | id | name | age | sex | + ----+--------+-----+-----+ | 3 | 李四 | 38 | 1 | | 6 | 田七 | 37 | 1 | | 4 | 王五 | 25 | 1 | | 2 | 张三 | 22 | 1 | | 7 | 谢礼 | 18 | 1 | | 8 | 李子柒 | 18 | 1 | | 1 | 李子捌 | 18 | 1 | | 5 | 六麻子 | 13 | 0 | + ----+--------+-----+-----+ 8 rows in set (0.00 sec)

如果你只指定age列降序排序，name列不指定，那么mysql会根据age降序排序后，再根据name列进行默认升序排序。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> select * from user order by age desc , name ; + ----+--------+-----+-----+ | id | name | age | sex | + ----+--------+-----+-----+ | 3 | 李四 | 38 | 1 | | 6 | 田七 | 37 | 1 | | 4 | 王五 | 25 | 1 | | 2 | 张三 | 22 | 1 | | 1 | 李子捌 | 18 | 1 | | 8 | 李子柒 | 18 | 1 | | 7 | 谢礼 | 18 | 1 | | 5 | 六麻子 | 13 | 0 | + ----+--------+-----+-----+ 8 rows in set (0.00 sec)

可以看到李子捌、李子柒、谢礼三行数据排序方式发生了改变。

2.4 order by结合limit

order by结合limit可以获取排序后的数据行记录数量。比如我们从user表中获取年龄最大的一个用户。可以看到李四同志38岁了，比较老了，属于年龄最大的人之一。

1 2 3 4 5 6 7 mysql> select * from user order by age desc limit 1; + ----+------+-----+-----+ | id | name | age | sex | + ----+------+-----+-----+ | 3 | 李四 | 38 | 1 | + ----+------+-----+-----+ 1 row in set (0.00 sec)

limit需要跟在order by之后，如果位置不对，mysql会抛出异常信息。

1 2	`mysql>` `select` `*` `from` `user` `limit 1` `order` `by` `age des;` `error 1064 (42000): you have an error` `in` `your sql syntax;` `check` `the manual that corresponds` `to` `your mysql server version` `for` `the` `right` `syntax` `to` `use near` `'order by age des'` `at` `line 1`