Upsert

hey yall,  I just noticed a weird behavior while using the upsert method auto-increment value keeps on incrementing even if it's just updating data.

for example, If I insert a record through Upsert and later update the existing data 2 times, the following insert id will be 4 for the new record.
is there any way we can stop this?

Learning Codeigniter 

Don't use upsert or autoincrement.
As far as I know, this behavior is typical of Mysql. You won't do anything about it.

Try using MariaDB, but I don't know if MariaDB has the same problem.
SQLite has something similar. About other DBMS I heard nothing.

Hi. The issue with ID's jumping is on MySQL with default InnoDB tables. When upserting a lot of data with duplicate entries, there is a possibility to reach the maximum unsigned INT value (4_294_967_295).

I found two solutions, but neither the perfect one:

1. Changing the auto incremented ID column to unsigned BIGINT (18_446_744_073_709_551_615).
   
2. Changing table storage engine from InnoDB to MyISAM, which have not this auto incrementing issue.
   

The reason why InnoDB have this auto incrementing issue is probably because of transactions support with the rollback function, which MyISAM does not have. Another main difference between InnoDB and MyISAM is, that MyISAM have full table lock instead of InnoDB's row lock while inserting/updating, which could cause slowing while upserting a lot of data during query run.

It looks like the MariaDB have this issue too, while it is a design decision of InnoDB as I mentioned above.

---

This MySQL's InnoDB behaviour should be mentioned in CodeIgniter's upsert documentation I think.

This MySQL's InnoDB behaviour is because of concurrency. It allows to use auto incrementing with multiple threads at once, which should have positive impact on performance, because auto incrementing does not need to be locked while other threads using it.

MySQL 8.0 Reference Manual - AUTO_INCREMENT Handling in InnoDB