Is MySQL's `SELECT * FROM table_name` Row Order Guaranteed?

MySQL Row Order for "SELECT * FROM table_name"

In MySQL, the order of rows returned by the "SELECT * FROM table_name;" query, which lacks an ORDER BY clause, is not guaranteed. The RDBMS engine determines the order based on its internal implementation details.

Absence of Guarantees

Unlike insertion order, MySQL does not provide any guarantees that the retrieved rows will be in the same sequence as when they were inserted into the table. Porting an application to a different RDBMS or upgrading MySQL versions can result in changes to the order of row retrieval.

Default Order in InnoDB

InnoDB storage engine may return rows in the order they are read from the index. However, the order may vary depending on the index used by the optimizer.

Variations for Different Storage Engines

Different storage engines have different implementations that can affect the default row order. For instance:

• MyISAM: Typically stores rows in insertion order.
• InnoDB: Rows may be stored in order of primary key or index.

In the case of MyISAM, rows occupy contiguous storage spaces. Deleting rows creates gaps that may be reused in reverse order when inserting new rows. Therefore, the order of rows in MyISAM is not strictly based on insertion order either.

The above is the detailed content of Is MySQL's `SELECT * FROM table_name` Row Order Guaranteed?. For more information, please follow other related articles on the PHP Chinese website!