CODEWITHSUNDEEP
phpsqlauto-increment
Wottensprels
Wottensprels

Reputation: 3327

mysql auto-increment - reassign after a record has been deleted

I'm using auto-increment to assign a id to every new entry in my database. Also, i've got a php-script which is selecting 10 entrys from this database, according to the current pagenumber. For example, if the current page is 2, the script should select every entry between id=20 and id=29.

Now, if i delete an entry, the id is lost. Therefore the script will only show 9 entrys when id28 has been deleted.

Is there a way to reassign the auto-increment values after e record has been deleted?

Upvotes: 1

Views: 3475

Answers (6)

VoteyDisciple
VoteyDisciple

Reputation: 37803

This is usually considered desirable: if entry number 28 is deleted, there will never again be an entry 28. If someone ever tries to refer to it, they're clearly trying to refer to the one that's been deleted, and you can report an error.

If you went back and reassigned 28 to some new entry, now you have no idea whether the person meant the old 28 or the new record.

Let's take a step back and revisit what you want to do. Your goal is not to show ten entries between 20 and 30. Your goal is to show ten entries that meet your criteria. For that, you can use the built-in LIMIT and OFFSET terms:

SELECT ...
FROM ...
WHERE ...
OFFSET 29
LIMIT 10

The OFFSET tells MySQL where to start counting and LIMIT tells it how many to count. MySQL will put together the whole result set, then give you 10 entries beginning at the 30th one. That's your fourth page of results. It does not remotely matter now what IDs they happen to have.

Upvotes: 5

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99889

You should change the way you select entries for a page.

By using the LIMIT .. OFFSET clause, you will be able to select 10 entries, starting at Nth entry:

SELECT   *
FROM     table
ORDER BY id
LIMIT    10
OFFSET   19
  • LIMIT 10 means return only 10 rows
  • OFFSET 19 means return rows after 19th row

This way, it doesn't matter if some id has been removed and ids are not sequential.

Just change the OFFSET:

  • Page 1 => OFFSET 0
  • Page 2 => OFFSET 9
  • Page 3 => OFFSET 19
  • Page N => OFFSET (N-1)*10-1

Upvotes: 3

Franco
Franco

Reputation: 171

As a direct answer to your question (auto_increment), you can change its value with the following query:

ALTER TABLE `your_table` AUTO_INCREMENT = 200

The next created item would have its next AI collumn value set to 200. This is useful in some cases, although I agree you would be better off using the LIMIT offsets.

Upvotes: 1

Andrey Vorobyev
Andrey Vorobyev

Reputation: 886

Reassign auto-increment values it's bad practice, because id, may used in relation with other tables.Use offset / limit construction, and forget about reassign auto-increment :)

Upvotes: 1

chrisbulmer
chrisbulmer

Reputation: 1245

You can but you shouldn't. If id=29 exists are you reset the auto-increment to 28, then there will be problems when auto-increment wants to use id=29 but the record already exists.

You'd be better off writing a query like so:

select * from table order by ID LIMIT n,10;

Where n is the page number*10

Upvotes: 1

rodneyrehm
rodneyrehm

Reputation: 13557

Your question suggests a query like

SELECT columns FROM table WHERE id BETWEEN 20 AND 29;

or less elegant

SELECT columns FROM table WHERE id >= 20 AND id <= 29;

If so, I suggest you read up on the LIMIT clause and do somethong along the lines of 

SELECT columns FROM table ORDER BY id LIMIT 20, 10;

Upvotes: 1

Related Questions