2

If we delete row from MySQL table, and if there is some auto incremental column exists, after insertion it will be resumed incrementing by last value. That is if the last value was 1000, after deletion, incremented value will be 1001.

But what if I want it to increment that column (e.g. id) according max id each time before insertion. I know that it could be achieved by setting:

ALTER TABLE `tableName` AUTO_INCREMENT = MAX(`ID`) + 1

manually, but this sql should be called every time after the deletion or before insertion.

In MySQL table I am just inserting elements and fetching them to the java.util.List, and I only need to get data according some interval. Getting according id is much faster than by limit.

e.g.

SELECT * FROM `TABLE_NAME` WHERE ID >=START AND ID <=END

is much faster than

SELECT * FROM `TABLE_NAME` LIMIT START, (END-START)

And getting size of the table is faster by MAX(ID) than COUNT(*)

Edit:
Regarding deletions, I only delete row from the end of the table or the rest from the id (including) that should be deleted, so that after deletion MAX(ID) and COUNT(*) will be equal.

The question is: Is there any way of setting "automated" sequential AUTO_INCREMENT in MySQL?

Xylian
  • 241
  • 2
  • 10
  • FYI - I removed the [tag:sql-server] tag since the question is about MySQL and MySQL <> SQL Server. – squillman Jan 26 '18 at 20:22
  • 1
    So what happens if you delete a row in the middle of a table? Your database is no longer sequential, and doing `ALTER TABLE \`tableName\` AUTO_INCREMENT = MAX(\`ID\`) + 1` would not yield you an ID that would fill that spot. – Blue Jan 26 '18 at 20:22
  • By the way I will delete a row only from the end of the table – Xylian Jan 26 '18 at 20:24
  • 2
    I would suggest not worrying about it and check this out. https://stackoverflow.com/questions/2214141/auto-increment-after-delete-in-mysql – Hayden Jan 26 '18 at 20:28
  • 1
    The auto-incrementing ID guarantees uniqueness, nothing more. There are a variety of reasons it would develop gaps in the sequence. And another variety of reasons why you wouldn't want to fill in those gaps afterward. If you're experiencing actual measurable performance problems, address those specifically. Are there actual measurable problems? Or are you just being particular about the ID numbers? – David Jan 26 '18 at 20:28
  • Can help you if you use a indexed column (ex counter int) along with MySql Trigger in order to set the column value. And use it to filter result – Leonardo Neninger Jan 26 '18 at 20:30
  • What problem are you trying to solve by having all of the primary key values in perfect sequence? It can be done but it's not an easy task and once people find out what's involved to make it work, they realize that an auto-increment is all they need. – Sam M Jan 26 '18 at 22:38
  • @SamM In my case autoincrement is not solution, because after some deletion and insertions there might be some gaps. I need primary keys to be just orders of the rows. I want to be sure that nth element's id is n. – Xylian Jan 26 '18 at 22:41
  • @Xylian A primary key is just a number. What purpose does it serve having no gaps? If you need it to be _displayed_ with no gaps, just create a view and use the row number. Primary keys are for computers to process data. Separate that from your business layer. Storing sequential numbers with no gaps is no trivial task. It involves issues of thread-safety, concurrency and dirty reads, among a host of other things. It's much more than simply programming a trigger with select max(id)+1. – Sam M Jan 27 '18 at 04:28
  • Possible duplicate of [Auto Increment after delete in MySQL](https://stackoverflow.com/questions/2214141/auto-increment-after-delete-in-mysql) – Sam M Jan 27 '18 at 04:30
  • Possible duplicate of https://stackoverflow.com/questions/31985556/how-to-block-auto-increment-to-set-the-next-number-always-as-maxnumber-1?rq=1 – Sam M Jan 27 '18 at 04:31
  • Possible duplicate of https://stackoverflow.com/questions/40588369/mysql-set-auto-increment-value-to-maxid-1-shortcut?rq=1 – Sam M Jan 27 '18 at 04:31

0 Answers0