I have table and when I add a new row I want to calculat an id number to it.
conditions:
if i have missing number give the smallest one (3)
if i have no missing number than give the next number that comes in a row (5)
How can I build this conditions into my query?
My query:
INSERT INTO sample(product) VALUES ('$product')
table
| product | id |
|---|---|
| name1 | 1 |
| name2 | 2 |
| name4 | 4 |
solution if I have missing id
| product | id |
|---|---|
| name1 | 1 |
| name2 | 2 |
| name4 | 4 |
| name5 | 3 |
solution if I have no missing id
| product | id |
|---|---|
| name1 | 1 |
| name2 | 2 |
| name3 | 3 |
| name4 | 4 |
| name5 | 5 |
>Solution :
MySQL has a feature to generate an auto-increment id. See https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
The number is always incremented. It does not go back and fill in missing id values. This is deliberate and necessary to prevent race conditions while concurrent sessions are inserting rows.
If you want to find unused values as you insert a new row, you find that to prevent two concurrent sessions from using the same value, you end up having to lock the whole table. This hurts performance for many apps.
You should drop the requirement that the id values must be consecutive. They are not ordinal row numbers. You may always have missing id values, because you may delete rows, or rollback a transaction that inserts a row, or the auto-increment mechanism can even skip values as it generates them.