是否可以在MySQL表顶部插入新行? [英] Is it possible to insert a new row at top of MySQL table?

问题描述

这样插入MySQL表中的所有行:

All rows in MySQL tables are being inserted like this:

1
2
3

1
2
3

有什么方法可以在表格顶部插入新行，以便表格看起来像这样吗?

Is there any way how to insert new row at a top of table so that table looks like this?

3
2
1

3
2
1

是的，是的，我知道排序依据"，但让我解释一下这个问题.我有一个约会网站，用户可以按性别，年龄，城市等搜索个人资料.搜索条件超过20种，因此无法为每种可能的组合创建索引.因此，如果我使用"order by"，则搜索通常以使用临时文件，使用文件排序"结束，这会导致很高的服务器负载.如果我删除"order by"，则最早的配置文件会显示为第一个，用户必须转到最后一页才能看到新的配置文件.这很糟糕，因为搜索结果的首页总是看起来一样，并且用户感觉没有新的配置文件.这就是为什么我问这个问题.如果无法在表格顶部插入最后一行，您还能提出其他建议吗?

Yes, yes, I know "order by" but let me explain the problem. I have a dating website and users can search profiles by sex, age, city, etc. There are more than 20 search criteria and it's not possible to create indexes for each possible combination. So, if I use "order by", the search usually ends with "using temporary, using filesort" and this causes a very high server load. If I remove "order by" oldest profiles are shown as first and users have to go to the last page to see the new profiles. That's very bad because first pages of search results always look the same and users have a feeling that there are no new profiles. That's why I asked this question. If it's not possible to insert last row at top of table, can you suggest anything else?

推荐答案

在没有ORDER BY子句的情况下返回结果的顺序取决于RDBM.对于MySQL或至少大多数引擎而言，如果您未明确指定从最旧条目到新条目的升序，则该顺序将递增.该行物理上"的位置无关紧要.我不确定是否所有的mysql引擎都能那样工作.即，在PostgreSQL中，默认"顺序首先显示最近更新的行.这可能也是某些MySQL引擎工作的方式.

The order in which the results are returned when there's no ORDER BY clause depends on the RDBM. In the case of MySQL, or at least most engines, if you don't explicitly specify the order it will be ascending, from oldest to new entries. Where the row is located "physically" doesn't matter. I'm not sure if all mysql engines work that way though. I.e., in PostgreSQL the "default" order shows the most recently updated rows first. This might be the way some of the MySQL engines work too.

无论如何，关键是-如果要对结果进行排序-始终指定排序顺序，而不仅要依赖似乎有效的默认值.在这种情况下，您需要一些琐碎的东西-您希望用户按降序排列，因此只需使用:

Anyway, the point is - if you want the results ordered - always specify sort order, don't just depend on something default that seems to work. In you case you want something trivial - you want the users in descending order, so just use:

SELECT * FROM users ORDER BY id DESC

这篇关于是否可以在MySQL表顶部插入新行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！