On occasion I come across a database table on a project where when no sorting is applied (
ORDER clause) that the ids are not in their inherit logical order.
1 178 3 144 2
Under the presumption that no one went out of their way and started manually changing the
id column values for any reason and that the
id column is set to
autoincrement what would lead to this scenario and should whatever that cause/those causes should be an issue to be addressed for security or data integrity purposes?
I’ll just elaborate on @a_horse_with_no_name ‘s comment. A table (relation) is a set of rows (tuples). By definition, a set is unordered. Asking what the next row is without specifying what you mean by next (ORDER BY), is like asking what colour joy has (well when it comes to power tools everyone knows that it is green and black ;-).
Assume that no rows reside in memory and that another connection reads id=186 into memory. Now your query asks for:
select id from T limit 1;
What would you consider the most sensible thing for the DBMS to do, return 186 from memory, or read a bunch of rows from disk to return the lowest id?
What’s the benefit of treat a table as unordered one might ask? The optimizer tries to execute the query as efficient as possible. It can re-arrange the query in a number of ways, as long as it does not violate the semantics of the query. If it would have to take the ordering into consideration, it would severely cripple the freedom to choose the most efficient plan for the query.
DELETEmight leave a ‘hole’ in the data; a subsequent
INSERTmay fill in that hole with the next
UPDATEcan change the length of a record, thereby perhaps necessitating putting it somewhere else.
REPLACEdeletes a row(s) and inserts a row, thereby creating a new
GROUP BY(or other constructs) may pick a different ordering.
JOINmay pick either table to ‘start’ with.
ROLLBACK'dtransaction may lose ids.
- MyISAM, especially after a bunch of deletes/updates/inserts, is very likely to provide rows in an unpredictable order. (InnoDB stores the data in
PRIMARY KEYorder, so it is usually predictable.)
- etc, etc.
If you want specifics, please provide
SHOW CREATE TABLE and the full