duanbei6427
2018-09-24 19:32
浏览 296
已采纳

PostgreSQL表行在更新时会显示在底部

I am currently migrating from MySQL to PostgreSQL in a Laravel application, and I noticed that when updating, the row goes to the end of the table (bottom).

In the application I know I can use ORDER BY to sort, but I am referring to the internal behavior of the database while performing the UPDATE action.

In Mysql, it remains in the same position it occupied before the update. Is there any way to apply this function? Would it be a InnoDB feature? Using Navicat Premium 12.1 DBMS.

I think this is just an aesthetic factor, but even so I would like to learn how to carry out this "permanent ordination".

The database is in UTF-8 encoding and pt_BR.UTF8 collation and ctype. Following is the table:

CREATE TABLE `properties` (
   `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `title` varchar(255) NOT NULL,
   `description` text NOT NULL,
   `name` varchar(255),
   `rental_price` decimal(10, 2),
   `sale_price` decimal(10, 2)
);

Thank you all!

图片转代码服务由CSDN问答提供 功能建议

我目前正在Laravel应用程序中从MySQL迁移到PostgreSQL,我注意到在更新时,行转到 表的末尾(底部)。

在应用程序中,我知道我可以使用ORDER BY进行排序,但我在执行UPDATE操作时指的是数据库的内部行为。

在Mysql中,它保持在更新前占用的相同位置。 是否有任何方法可以应用此功能? 它会是InnoDB功能吗? 使用Navicat Premium 12.1 DBMS。

我认为这只是一个美学因素,但即使如此,我也想学习如何进行这种“永久性圣职任命”。

数据库采用UTF-8编码和pt_BR.UTF8校对和ctype。 以下是表格:

  CREATE TABLE`properties`(
  id`int(11)NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title`varchar(255)NOT NULL,
`incription`文本NOT NULL,
`name` varchar(255),
`initly_price` decimal(  10,2),
`sale_price`十进制(10,2)
); 
   
 
 

谢谢大家!

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongyuyi5680 2018-09-24 20:04
    已采纳

    Part 1: Generally use ORDER BY

    If you do not use the ORDER BY statement, both MySQL and PostgreSQL (and for that matter most relational DBMS systems) do not make any promises about the order of records.

    You should refactor your application to use the ORDER BY statement. If you want your data set to be ordered by newest first, you could use something like:

    SELECT * FROM yourtable ORDER BY id DESC;
    SELECT * FROM youtable ORDER BY creation_date DESC; -- if your table has such a column
    

    Similarly, you can have oldest objects first by using one of the following:

    SELECT * FROM yourtable ORDER BY id ASC;
    SELECT * FROM youtable ORDER BY creation_date ASC; -- if your table has such a column
    

    Part 2: Looking into the mechanics

    You added to your question a more detailed inquiry:

    [...] I know I can use ORDER BY to sort, but I am referring to the internal behavior of the database while performing the UPDATE action.

    There is multiple things that influence the sequence of database records displayed on your screen, when performing a query. In a real life application, it is not (practially) possible to predict this sequence.

    I assume this is simply an effect of PostgreSQL creating a new record for the updated record as can be found here in the Updating a Row section. I suggest to not rely on this behvaiour in any of your applications.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题