CODEWITHSUNDEEP
mysql
Leb_Broth
Leb_Broth

Reputation: 1111

Does rows shift up if i delete rows in MySQL?

I can't seem to find any convincing reference on the internet that tells whether rows will shift up or NOT in case some rows where DELETED in a MySQL Database. Or should the user do it manually as of DELETE and then UPDATE the other rows e.g. (X-1) where X is the deleted row number.

Appreciate if you can refer me to any thread or article.

Upvotes: 0

Views: 698

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The answer to your question would seem to be this. The data in relational databases (in general) is stored on a page. When a record is deleted, the space is freed on the page and could be used for another record.

This enters into the realm of understanding pages, fill factors, and the like. The important consideration is that space will be reclaimed, the order of the records in a table may have nothing to do with insert order, and gaps are allowed in auto_incremented columns.

Upvotes: 2

Mihai Crăiță
Mihai Crăiță

Reputation: 3417

The short answer is no.

The id of an row does not change if you delete other rows even if the id column is set up as AUTO_INCREMENT.

So you may have the following ids in your table: 1, 2, 4, 5, 7, 8, 10 after you deleted rows with ids 3, 6 and 9.

Upvotes: 0

Related Questions