关于mysql中select出数据未排序问题

最新推荐文章于 2022-04-06 08:08:00 发布
最新推荐文章于 2022-04-06 08:08:00 发布
阅读量128 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： 数据库
原文链接：http://www.cnblogs.com/ellisonzhang/p/10490197.html
本文精选自《MySQL必知必会》，深入浅出地讲解了MySQL数据库的基础知识与核心概念，适合初学者及有一定经验的数据库管理员阅读，旨在帮助读者掌握MySQL的使用技巧。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 

 

来自  mysql必知必会

 

 

 

转载于:https://www.cnblogs.com/ellisonzhang/p/10490197.html

MySQL中select语句使用order按行排序
12-15
在MySQL数据库中，`SELECT`语句用于从一个或多个表中检索数据，而`ORDER BY`子句则用于对查询结果进行排序。本篇文章将深入探讨如何在MySQL中使用`SELECT`语句配合`ORDER BY`进行数据的排序操作。 `SELECT`语句的...
MySQL中利用索引对数据进行排序的基础教程
12-15
MySQL中，有两种方式生成有序...MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。 通过索
mysql 强制不排序
lixixi
05-12 4485
order by null order by null: 强制不排序 有的sql语句在执行完毕之后把结果给排序了，而我们对该结果没要求有排序效果， 这个排序是多余的，反而还消耗mysql的资源。为了使得获得数据的自然性、不要排序、降低mysql资源的开销，可以强制不排序。 例如： 获得每个分类下商品的总数量： select c.cat_id,count(g.goods_id) from...
让sql语句不排序,按照in语句的顺序返回结果mysql:SELECT*FROMEVENTWHER
航天飞哥的博客
05-30 1711
让sql语句不排序,按照in语句的顺序返回结果 　　mysql: 　　SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509) ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,',')) 　　oracle: 　　select name
mysql不排序 in_让sql语句不排序,按照in语句的顺序返回结果
weixin_39662611的博客
02-02 381
mysql写法:SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509) ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,','))oracle写法:select name from order where oderid in(111,22...
mysql查询的时候默认排序不是主键自增的顺序？
a7442358的专栏
04-09 1万+
一直都有一种错觉，认为查询出来的数据如果没有设置排序的话，就是按照id（主键）自增的顺序排列，也就是id的正序排列。但是偶然的一个机会，发现了如果没有加排序的话，出现的不是想象中的自增顺序（逻辑顺序），那是什么顺序呢？答案就是物理存储顺序。 虽然一般情况，物理顺序和逻辑顺序保持一致，但是有个前提，那就是再你不删不改的情况下。如果你细心观察过会发现，当你进行大量的删除操作的时候，虽然数据量会减少，但...
MySQL使用SELECT 语句不加ORDER BY默认是如何排序的？
热门推荐
阿飞云漫步
04-06 1万+
大家好，我是阿飞云怕什么真理无穷，进一步有近一步的欢喜记录一个MySQL查询排序的问题，一个SQL语句没有加order by，那么查询出来的结果到底是按照什么规则排序的呢？查询了网上的一些资料，分享如下：•MyISAM 表MySQL Select 默认排序是按照物理存储顺序显示的（不进行额外排序）。也就是说SELECT * FROM tbl – 会产生“表扫描”。如果表没...
mysql的中文数据按拼音排序的2个方法
01-19
客服那边需要我对一些酒店进行中文拼音排序，以前没有接触过，在php群里问了一些大牛。。得到了2种答案，都可以。哈哈·~ 以下既是msyql 例子,表结构是utf-8的 方法一、 代码如下: SELECT `hotel_name` FROM `hotel...
MySQL关于字符串中数字排序的问题分析
09-10
在MySQL中，处理字符串中的数字排序可能会遇到一些特殊的情况，因为MySQL默认会按照字符串的字典顺序进行排序，而不是按照数字的大小。当字段的定义是字符串类型（如VARCHAR），但实际存储的是数字时，如果不做特殊...
MySQL对中文进行排序详解及实例
12-16
在MySQL数据库中，对中文数据进行排序并非像处理英文数据那样简单。MySQL默认的排序规则是基于字节的，这适用于ASCII字符集，但不适用于包含多字节字符集如UTF-8或GBK的中文字符。当我们尝试用`ORDER BY`语句对中文...
select in 查询结果无顺序及解决办法
shining的专栏
11-19 2412
现有如下mysql表： mysql> select * from in_demo; +----+-----+ | id | num | +----+-----+ | 1 | 8 | | 2 | 7 | | 3 | 6 | +----+-----+ 3 rows in set 按如下sql查询结果如下： mysql> SELECT * FROM in_demo ...
mysql不排序 in,让sql语句不排序,按照in语句的顺序返回结果
weixin_39842682的博客
03-23 404
mysql: SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509) ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,',')) oracle: SELECT NAME FROM order WHERE oderid IN (111,222...
记录mysql中连表查询后根据时间排序，结果没有排序
qq_42643690的博客
10-17 642
连表查询订单下单时间后根据下单时间倒叙排序，如下sql执行后没有实现排序 select items.id as id, items.reap_user_mess as reapUserMess, items.sum_profit as sumProfit, items.shop_user_id as shopUserI...
select查询之过滤和排序数据
weixin_43945486的博客
08-02 867
目录2.过滤和排序数据2.1 使用where子句比较运算2.2 between...and...2.3 in(set)表示在某些数值内2.4 like选择类似的值2.5 is (not) NULL判断空值2.6 AND 要求并的关系为真2.7 OR 要求或关系为真2.8 NOT2.9 ORDER BY子句排序 2.过滤和排序数据 2.1 使用where子句比较运算 select * from employees where department_id = 90; 比较运算 操作符：=、>、<、
mysql select 无order by 默认排序 出现乱序的问题
牧羊老人
01-15 4887
问题原因： user表默认： 表结构信息： 第一个sql：select UID,USER_NAME from user; 查询结果： 第二个sql：select * from user; 查询结果： 查找的都说，与数据库表引擎有关：但在我这个问题状态下未能验证 innodb 默认按照主键自增排序 myisam 默认按照物理存储顺序排序 2、建议： 必须养成追加写o...
mysql 怎么按照不在select中的列排序
最新发布
08-10
<think>我们面对的问题是：在MySQL中，当我们使用DISTINCT时，如果ORDER BY子句引用了不在SELECT列表中的列，就会报错：“Expression #1 of ORDER BY clause is not in SELECT list, references column '...' which is not in SELECT list; this is incompatible with DISTINCT”。 根据引用[1][2][3]的分析，这是因为MySQL 8.0及更高版本默认启用了sql_mode中的ONLY_FULL_GROUP_BY模式，该模式要求在使用DISTINCT时，ORDER BY子句中的列必须在SELECT列表中。 解决方案有以下几种： 1. 将ORDER BY中引用的列添加到SELECT列表中（推荐）。 2. 修改sql_mode，移除ONLY_FULL_GROUP_BY（不推荐，因为可能影响其他查询）。 3. 使用子查询或派生表，将排序操作放在外层查询。 针对用户提供的SQL，我们可以这样修改（采用第一种方法）： 原SQL： ``` with a1 as( select * from music_likes m left join music l on m.music_id = l.id order by music_id ), a2 as( select f.user_id,music_name,music_id from follow f left join a1 on f.follower_id=a1.user_id order by music_id ) select distinct music_name from a2 where user_id = 1 and not exists ( select 1 from a1 where a1.music_id = a2.music_id and a1.user_id = 1 ) order by music_id ``` 错误原因：最外层的SELECT使用了DISTINCT，并且ORDER BY引用了music_id，但music_id不在SELECT列表中（只选了music_name）。 修改方案：在SELECT列表中加入music_id，这样DISTINCT会同时根据music_name和music_id去重，但注意这样可能改变结果集（因为同一个music_name可能有多个music_id？需要根据业务逻辑判断）。如果业务允许，我们可以这样改： 修改后的SQL： ``` ... select distinct music_name, music_id -- 添加music_id from a2 where user_id = 1 and not exists ... order by music_id -- 现在music_id在SELECT列表中了 ``` 但是，如果同一个音乐名称（music_name）对应多个music_id，那么这样查询会返回多条记录（每个music_id一条）。如果这不是我们想要的（即我们只需要音乐名称，不管music_id），那么我们可以考虑第二种方法：使用子查询。 方法二：使用子查询，将去重和排序分离 ``` select music_name from ( select distinct music_name, music_id -- 内层同时选择music_id用于排序 from a2 where user_id = 1 and not exists ... ) as tmp order by music_id -- 外层排序 ``` 注意：内层查询中我们同时选择了music_name和music_id（为了排序），然后外层只选择music_name并按照music_id排序。这样，内层去重后的结果（包括music_id）被用作临时表，外层排序时可以使用music_id，因为它在临时表中存在。 但是，这里有一个问题：同一个music_name可能对应多个music_id，那么内层使用distinct music_name, music_id会返回多条记录，而外层排序时，同一个music_name可能因为不同的music_id而出现多次。所以，我们需要根据业务逻辑决定是否可行。 如果业务上要求每个music_name只出现一次，那么我们可以考虑使用聚合函数，比如取最小的music_id或最大的music_id来代表这个音乐名称： ``` select music_name from ( select music_name, min(music_id) as min_music_id -- 或者max(music_id) from a2 where user_id = 1 and not exists ... group by music_name -- 按音乐名称分组，每组取一个music_id ) as tmp order by min_music_id -- 然后按照这个取出的music_id排序 ``` 这样，每个音乐名称只出现一次，并且按照其最小的music_id排序。 因此，具体修改方法需要根据业务需求来定。 如果不希望修改sql_mode（因为修改后可能影响其他查询），那么上述两种方法（添加列或使用子查询）都是可行的。 总结步骤： 1. 分析错误：确认ORDER BY引用的列是否在SELECT列表中。 2. 根据业务需求，选择以下方法之一： a) 将ORDER BY中的列添加到SELECT列表（可能改变结果集的行数，需谨慎）。 b) 使用子查询，在子查询中包含排序所需的列，然后在外层查询中排序（注意结果去重后的影响）。 c) 使用GROUP BY代替DISTINCT，并通过聚合函数处理排序列（如MIN、MAX等）。 针对用户提供的SQL，如果业务上允许同一个music_name对应多个music_id，那么直接添加music_id到SELECT列表即可。如果要求每个music_name只出现一次，那么就需要使用聚合函数。 另外，根据引用[3][4]的方法，我们可以通过修改sql_mode来避免这个错误，但不推荐在生产环境中这样做，因为可能会影响其他查询。 修改sql_mode的方法（仅当确实需要时）： 在MySQL配置文件（my.ini或my.cnf）的[mysqld]部分添加： sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 然后重启MySQL服务。 但强烈建议优先修改查询语句。 因此，针对用户提供的SQL，我给出两种修改方案（假设业务要求每个music_name只出现一次）： 方案一（使用聚合函数）： ``` with a1 as( select * from music_likes m left join music l on m.music_id = l.id ), a2 as( select f.user_id, music_name, music_id from follow f left join a1 on f.follower_id = a1.user_id ) select music_name from a2 where user_id = 1 and not exists ( select 1 from a1 where a1.music_id = a2.music_id and a1.user_id = 1 ) group by music_name -- 按音乐名称分组 order by min(music_id) -- 按最小的music_id排序 ``` 方案二（使用子查询，并保留music_id用于排序）： ``` select music_name from ( select distinct music_name, music_id from a2 where user_id = 1 and not exists ( select 1 from a1 where a1.music_id = a2.music_id and a1.user_id = 1 ) ) as tmp order by music_id ``` 注意：方案二中，如果同一个music_name有多个music_id，那么会出现多次。所以需要根据业务选择。 由于原SQL中使用了DISTINCT music_name，所以原意应该是每个music_name只出现一次。因此，方案一更符合原意，同时通过聚合函数（min）来获得一个music_id用于排序。 因此，我推荐方案一。 修改后的完整SQL（使用方案一）： ``` with a1 as( select * from music_likes m left join music l on m.music_id = l.id ), a2 as( select f.user_id, music_name, music_id from follow f left join a1 on f.follower_id = a1.user_id ) select music_name from a2 where user_id = 1 and not exists ( select 1 from a1 where a1.music_id = a2.music_id and a1.user_id = 1 ) group by music_name order by min(music_id) -- 或者使用max(music_id)，根据需求 ``` 这样，我们既保证了每个music_name只出现一次，又能够按照music_id排序（通过聚合函数取一个代表值），同时避免了MySQL的语法错误。 最后，关于检查当前sql_mode的方法，可以使用： SELECT @@sql_mode; 关于GROUP BY和DISTINCT的性能区别，通常GROUP BY在功能上更强大（可以配合聚合函数），而DISTINCT只是去重。在去重的场景下，两者性能相近，但具体取决于索引和查询优化器。 以上解决方案适用于MySQL 8.0及以上版本。</think>### 解决 MySQL 中 "ORDER BY 字段不在 SELECT 列表" 的错误 #### 错误原因分析 在 MySQL 8.0+ 版本中，默认启用了 `ONLY_FULL_GROUP_BY` 模式，导致以下冲突： 1. 使用 `DISTINCT` 时，`ORDER BY` 引用了未出现在 `SELECT` 列表中的字段（如 `music_id`） 2. 违反了 SQL 标准：`DISTINCT` 操作后，排序字段必须明确存在于结果集中[^1][^3] #### 解决方案（推荐三种方法） ##### 方法 1：将排序字段添加到 SELECT 列表（最佳实践） ```sql WITH a1 AS ( SELECT * FROM music_likes m LEFT JOIN music l ON m.music_id = l.id ), a2 AS ( SELECT f.user_id, music_name, music_id -- 确保music_id被选择 FROM follow f LEFT JOIN a1 ON f.follower_id = a1.user_id ) SELECT DISTINCT music_name, music_id -- 添加music_id到SELECT FROM a2 WHERE user_id = 1 AND NOT EXISTS ( SELECT 1 FROM a1 WHERE a1.music_id = a2.music_id AND a1.user_id = 1 ) ORDER BY music_id; -- 现在字段已存在于SELECT列表 ``` **优点**：完全兼容所有 MySQL 版本，符合 SQL 标准[^1][^3] ##### 方法 2：使用子查询分离操作 ```sql SELECT music_name FROM ( SELECT DISTINCT music_name, music_id -- 内层包含排序字段 FROM a2 WHERE user_id = 1 AND NOT EXISTS (...) ) AS tmp ORDER BY music_id; -- 外层使用排序字段 ``` **适用场景**：当不希望排序字段出现在最终结果中时 ##### 方法 3：使用聚合函数（替代 DISTINCT） ```sql SELECT music_name FROM a2 WHERE user_id = 1 AND NOT EXISTS (...) GROUP BY music_name -- 替代DISTINCT ORDER BY MIN(music_id); -- 使用聚合函数处理排序字段 ``` **原理**：通过 `GROUP BY` 分组后，使用聚合函数（`MIN/MAX`）引用排序字段[^1] #### 不推荐方案：修改 sql_mode ```sql -- 临时修改（重启失效） SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- 永久修改需编辑 my.cnf 文件 [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE... ``` **风险**：禁用 `ONLY_FULL_GROUP_BY` 可能导致其他查询出现意外行为[^3][^4] #### 最佳实践建议 1. **优先使用方法 1**，保持查询逻辑清晰 2. 生产环境避免修改 `sql_mode`，确保版本兼容性 3. 使用 `EXPLAIN` 分析查询性能 4. 重要操作前备份数据：`mysqldump -u root -p database > backup.sql` > 通过将 `music_id` 显式添加到 `SELECT` 列表，即可解决该错误。此修改符合 SQL 标准且兼容所有 MySQL 版本[^1][^3]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值