更新是否会更改 PostgreSQL 中表中记录的顺序? [英] Does update change the order of records in a table in PostgreSQL?

问题描述

我的代码取决于表中记录的顺序.我的假设是一个表可以被视为一个列表，以便记录保持顺序.我有一个小的更新代码，如下所示，它将更新表中特定索引处的记录.

My code depends on the order of records in the table. My assumption was that a table can be considered a list so that the records maintain order. I have a small update code as shown below that will update a record at a particular index in the table.

p = pieces[index]

p.position = 0

p.save

我在此更新之前和此更新之后检查记录的顺序，然后我看到更新后更新的记录移动到列表的最后一个.我打印 Piece.all 来打印列表.订单在 mysql 中维护，但是当我将它部署到使用 postgre 的 heroku 时，订单没有得到维护，所以这对我来说是一个令人惊讶的发现.

I check the order of records before this update and after this update then i see that after the update the record that is updated is moved to the last of the list. I print Piece.all to print the list. The order is maintained in mysql but when i deploy it to heroku which uses postgre the order was not maintained so this was a surprising find for me.

表格中的顺序是否没有保证，不应该依赖于顺序?请纠正我的误解并感谢您的澄清.

Is there no guarantee of order in tables and one should not depend on the order? Please correct my misunderstanding and thanks for the clarification.

推荐答案

在我看来，你永远不应该依赖顺序.

You should NEVER depend on the order in my honest opinion.

根据 sql 规范，以未指定的顺序返回行，除非您添加 order by 子句.在 Postgres 中，这意味着您将获得行，基本上是在磁盘上读取活动行的顺序.

Rows are returned in an unspecified order, per sql specs, unless you add an order by clause. In Postgres, that means you'll get rows in, basically, the order that live rows read on the disk.

MySQL 倾向于按照行的插入顺序返回行，这就是为什么您会看到不同的行为.

MySQL tends to return rows in the order they're inserted, and this is why you see the different in behavior.

如果您希望它们始终按照创建的顺序返回，您可以使用 Item.order("created_at")

If you want them to always be returned in the order they were created, you can use Item.order("created_at")

这篇关于更新是否会更改 PostgreSQL 中表中记录的顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！