sqlalchemy func.group_concat 和随机数据顺序 [英] sqlalchemy func.group_concat and random order of data

问题描述

我有以下查询，它使用 func.group_concat 对一些数据进行分组

I have a following query which is using func.group_concat to group some data

r  = aliased(Rank)
t  = aliased(Team)
p  = aliased(Participant)
players_ranks_data = (
    db.session.query(
        p,
        r.id.label('player_team_skill_id'),
        r.name.label('player_team_skill_name'),
        func.group_concat(t.name),
    )
    .outerjoin(r, r.id == p.team_skill)
    .outerjoin(t, t.id == p.team_id)
    .filter(p.event_id == event_data.id)
    .filter(p.team_id != '')
    .group_by('player_team_skill_name')
    .all()
)

当我打印检索到的内容时

When I'm printing what was retrieved

print('players_ranks_data: {}'.format(players_ranks_data))

我看到了完整的数据，但这样做几次会给我不同的返回数据顺序.例如:第一次执行

I see full data, but doing this few times is giving me different order of returned data. For example: 1st execution

players_ranks_data: [(<Participant 104>, 4, u'advanced', u'text1,item1,other thingy,something else,what is that')]

第二次执行

players_ranks_data: [(<Participant 104>, 4, u'advanced', u'what is that, item1,other thingy, text1, something else')]

这真的很奇怪，因为当我直接在 DB (MySQL) 上执行这个查询时，我结果也不一定总是得到相同顺序的项目.

It's really weird because when I execute this query directly on DB (MySQL) I'm not always getting the same order of items in result too.

SELECT
  `Rank_1`.id AS player_team_skill_id,
  `Rank_1`.name AS player_team_skill_name,
  GROUP_CONCAT(`Team_1`.name) AS group_concat_1
FROM
  `Participant` AS `Participant_1`
  LEFT OUTER JOIN `Rank` AS `Rank_1`
    ON `Rank_1`.id = `Participant_1`.team_skill
  LEFT OUTER JOIN `Team` AS `Team_1`
    ON `Team_1`.id = `Participant_1`.team_id
WHERE `Participant_1`.event_id = 2
  AND `Participant_1`.team_id != ''
GROUP BY player_team_skill_name;

我的原始 sqlalchemy 查询或 MySQL 配置中是否缺少任何内容?

Is there anything missing in my original sqlalchemy query or in MySQL configuration?

EDIT 1:实际上，我再次进行了一些测试，似乎 MySQL (5.7) 中的结果也没有排序.这是怎么回事?

EDIT 1: actually, I did some tests again and it seems that results in MySQL (5.7) are not ordered too. What's going on here?

推荐答案

SQL 结果是(多)集，因此没有排序，除非您明确定义一个.这也适用于 GROUP BY 生成的组行；组内没有顺序，因此聚合会以当前查询根据计划、物理布局、调度等产生的任何顺序接收值.解决办法是明确定义顺序:

SQL results are (multi) sets and as such have no ordering, unless you explicitly define one. This applies to group rows produced by GROUP BY as well; there is no order within the groups, and so aggregates receive values in any which order the current query depending on plan, physical layout, scheduling etc. happens to produce. The solution is to explicitly define the order:

func.group_concat(t.name.op("ORDER BY")(t.name))

这使用了 通用运算符函数 生成所需的 SQL 语法.SQLAlchemy 提供了 aggregate_order_by 语法相同的帮助器，但它只为 Postgresql 方言提供.

This uses a generic operator function to produce the required SQL syntax. SQLAlchemy provides the aggregate_order_by helper for the very same syntax, but it is provided for the Postgresql dialect only.

这篇关于sqlalchemy func.group_concat 和随机数据顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！