MySQL-按ID自动排序不起作用 [英] MySQL - Automatic ordering by ID not working

问题描述

我正在使用MySQL数据库存储学生项目(每个项目都有其自己的唯一ID，如第一个屏幕截图所示).

I'm using MySQL database to store student projects (every single project has its own unique ID, as the first screenshot shows).

"ID"列设置为 auto_increment 和 PRIMARY KEY .

所有项目都必须按其ID升序排序(仅).但是每次我向数据库中插入一个新项目并在年份"字段中设置较低的值(比上次输入时要低的值，当我插入上一个项目时)，该项目便会自动由该年份"字段进行排序(如第二个屏幕截图所示).

All projects must be ordered (only) by their ID ascending. But every time I insert a new project into my database and set lower value in the 'Year' field (lower value than I entered last time, when I was inserting my previous project), my projects become automatically ordered by this 'Year' field (as shown on the second screenshot).

有什么办法，如何设置表格以仅通过"ID"列自动对所有新添加的项目进行排序?是的，我知道我可以在放置每条新记录后使用ALTER TABLE tablename ORDER BY columnname ASC;更改顺序，但是可以自动完成吗?

Is there any way, how to set my table for automatic ordering all newly added projects only by the 'ID' column? Yeah, I know that I can change the ordering with ALTER TABLE tablename ORDER BY columnname ASC;after I place every new record, but can it be done automatically?

感谢所有提供帮助的人.

Thx to everyone who helps.

推荐答案

问:有什么方法，如何设置表以仅通过"ID"列自动对所有新添加的项目进行排序?

Q: Is there any way, how to set my table for automatic ordering all newly added projects only by the 'ID' column?

A:MySQL表中没有自动排序". (某些存储引擎，例如InnoDB，是索引组织的"结构，并按群集键按顺序存储行.)但是，该组织未定义或指定SELECT语句返回的行的顺序.

A: There is no "automatic ordering" in a MySQL table. (Some storage engines, such as InnoDB, are "index organized" structures, and do store rows in order by the cluster key.) But this organization does not define or specify the order of rows returned by a SELECT statement.

在SELECT语句上没有ORDER BY子句，则MySQL服务器可以按其选择的 any 顺序返回行.不能保证任何自动"或默认"顺序.

Without an ORDER BY clause on a SELECT statement, then the MySQL server can return rows in any order it chooses to. There is no guarantee of any "automatic" or "default" ordering.

当我们运行不带ORDER BY子句的SQL SELECT语句时，我们确实观察到行倾向于以一致的顺序返回.这种行为不能得到保证，也不是自动的".

When we run a SQL SELECT statement without an ORDER BY clause, we do observe that rows tend to be returned in a consistent order. This behavior isn't guaranteed, and it isn't "automatic".

我们观察到的这种一致的排序"行为是由于MySQL服务器对一致的数据集执行了一致的操作集.

This consistent "ordering" behavior we observe is due to the MySQL server performing a consistent set of operations, on a consistent set of data.

执行ALTER TABLE语句以重建整个表不是 解决自动排序"问题的方法.

Performing an ALTER TABLE statement to rebuild the entire table is not the solution to the "automatic ordering" issue.

如果您希望MySQL服务器按特定顺序返回行，则解决方案是在SELECT语句中添加ORDER BY子句.

If you want the MySQL server to return rows in a specific order, then the solution is to add an ORDER BY clause to the SELECT statement.

执行SELECT语句的客户端可以自由地对其检索的行进行任何操作.客户端可以执行诸如过滤，排序等之类的操作，以得出在用户界面中返回的内容.

The client that executes the SELECT statement is free to do whatever it wants with the rows it retrieves. The client can perform operations such as filtering, ordering, or whatever, to come up with what gets returned in the user interface.

某些客户端(如mysql命令行)未实现用于过滤"或排序"行的任何功能，因此，客户端指定返回行的顺序的唯一方法是ORDER BY子句声明本身. (MySQL命令行客户端以与检索到的顺序相同的顺序在用户界面中返回行.)

Some clients (like the mysql command line) don't implement any functions for "filtering" or "ordering" rows, so the only way for the client to specify an order that rows should be returned in is the ORDER BY clause on statement itself. (The MySQL command line client returns rows in the user interface in the same order that they are retrieved.)

我希望phpMyAdmin做同样的事情，它以与从MySQL服务器返回的行相同的顺序显示行.

I expect that phpMyAdmin does the same thing, it displays the rows in the same order that they are returned from the MySQL server.

这篇关于MySQL-按ID自动排序不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！