CODEWITHSUNDEEP
mysql
pradyu
pradyu

Reputation: 91

auto increment column increments even when there is NO new row insertion

I have created a table which is having a conditional row insertion function,so at times new rows are not inserted into the column. Here the problem is, even when row insertion is failed the auto_inc column increments and thus the values stored in that will be some what like this:

Sl No.

1

2

4

7

8

9

it looks really messy please help.thanks in advance

Upvotes: 0

Views: 2273

Answers (4)

pradyu
pradyu

Reputation: 91

got an answer for this question thanks to @ juergen d this should be the query:

    String queryString = "INSERT INTO hcl_candidates(SL_No,candidate,phone,pan,mailid) SELECT MAX(SL_No)+1, ?, ?, ?, ? FROM hcl_candidates";

Upvotes: 0

Shirish Bari
Shirish Bari

Reputation: 2722

A sspencer7593 has mentioned "The behavior of AUTO_INCREMENT is fairly well defined. And it's primarily designed to generate unique values. It's not designed to prevent gaps."

However as MySQL allows you to assign a custom value to AUTO_INCREMENT column a workaround to your scenario would be to assign value of Max(SI_No)+1 while inserting the row. In this case you will ensure that you would add next incremented value only when row is actually inserted.

Typical syntax would look like INSERT INTO TABLENAME (ID,SOMECOLUMN) VALUES ((SELECT MAX(ID)+1 NEWID FROM TABLENAME) ,someValue);

Note:- it would prevent gaps you are seeing during insertion and last row deletion cases . If you delete row in between you would still see the Gaps but I think this should be OK with you

Upvotes: 2

spencer7593
spencer7593

Reputation: 108500

This is expected behavior with INSERT ... SELECT, or when an INSERT statement fails or is rolled back. The innodb_autoinc_lock_mode setting can also influence the behavior. We will also see this when a value is supplied for the AUTO_INCREMENT column, or when rows are deleted.

The behavior of AUTO_INCREMENT is fairly well defined. And it's primarily designed to generate unique values. It's not designed to prevent gaps.

Upvotes: 0

DD77
DD77

Reputation: 816

Can you please add your php code and table structure? I think insert query is being executed even condition fails.

Upvotes: 0

Related Questions