MySQL中GROUP BY如何随机选取每个textid对应单条记录

原写法失效原因 #

MySQL 5.7及更高版本的查询优化器默认会忽略无LIMIT的子查询内部ORDER BY语句，优化器判定子查询内的排序不会影响最终外层查询的结果，执行时直接跳过排序步骤，因此原写法中rand()排序实际没有生效，GROUP BY还是取到了每个textid对应的存储顺序第一条记录。

你原本使用的写法如下：

SELECT * FROM (
  SELECT * FROM textelements ORDER BY rand()
) AS z
GROUP BY z.textid;

LIMIT方案生效逻辑 #

你在子查询中添加LIMIT 1000000后，优化器必须先完成排序才能取出前N条符合条件的记录，此时ORDER BY rand()会真实执行，外层GROUP BY取排序后的每组第一条，就实现了随机取数的效果。

注意：该方案需要保证LIMIT的数值大于等于你的全表行数，否则会出现数据遗漏的问题，同时如果MySQL开启了ONLY_FULL_GROUP_BY模式，该写法会因为SELECT列不符合GROUP BY规范报错。

更稳定的推荐方案（MySQL 8.0+适用） #

使用窗口函数ROW_NUMBER()实现，逻辑更清晰，也符合SQL规范，不会出现模式不兼容、LIMIT值不足的问题：

SELECT * FROM (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY textid ORDER BY RAND()) AS group_rn
  FROM textelements
) AS t
WHERE group_rn = 1;

内容的提问来源于stack exchange，提问作者netAction