sqlalchemy:查询结果的顺序意外 [英] sqlalchemy: order of query result unexpected

问题描述

我在 MySQL 中使用 SQLAlchemy 并且有一个带有两个外键的表:

I'm using SQLAlchemy with MySQL and have a table with two foreign keys:

class CampaignCreativeLink(db.Model):
  __tablename__ = 'campaign_creative_links'
  campaign_id = db.Column(db.Integer, db.ForeignKey('campaigns.id'),
    primary_key=True)
  creative_id = db.Column(db.Integer, db.ForeignKey('creatives.id'),
    primary_key=True)

然后我使用 for 循环将 3 个项目插入表中，如下所示:

Then I use a for loop to insert 3 items into the table like this:

session.add(8, 3)
session.add(8, 2)
session.add(8, 1)

但是当我检查表格时，项目的顺序是相反的

But when I checked the table, the items are ordered reversely

8 1
8 2
8 3

并且查询也反向显示顺序.这是什么原因，我如何才能保持添加顺序的顺序?

And the query shows the order reversely too. What's the reason for this and how can I keep the order same as when they were added?

推荐答案

表是一组的行，因此不保证有任何顺序，除非您指定 ORDER BY.

A table is a set of rows and are therefore not guaranteed to have any order unless you specify ORDER BY.

在 MySQL (InnoDB) 中，主键充当聚集索引.这意味着行以主键指定的顺序物理存储，在本例中为 (campaign_id, created_id)，与插入顺序无关.如果您不指定 ORDER BY，这通常是返回行的顺序.

In MySQL (InnoDB), the primary key acts as the clustered index. This means that the rows are physically stored in the order specified by the primary key, in this case (campaign_id, created_id), regardless of the order of insertion. This is usually the order the rows are returned in if you don't specify an ORDER BY.

如果您需要按特定顺序返回行，请在查询时指定ORDER BY.

If you need your rows returned in a certain order, specify ORDER BY when you query.

这篇关于sqlalchemy:查询结果的顺序意外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！