0

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

pradyu
  • 91
  • 8
  • 1
    Can you paste the error message while `INSERT` fails – Gaurav Lad May 04 '16 at 05:07
  • there is no error!! There is 'where' condition if condition is true table is inserted with a row,else its not inserted...but auto increment is incrementing every time the condition is checked.sorry for using 'failed' in my question i will edit it. – pradyu May 04 '16 at 05:59
  • I wrote up a gap answer [here](http://stackoverflow.com/a/38363271). No reason to duplicate it here. – Drew Jul 14 '16 at 00:03

4 Answers4

2

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

Shirishkumar Bari
  • 2,692
  • 1
  • 28
  • 36
  • i had a similar idea but i had a few doubt about it: 1)what should be data type 2)how to set intial value to zero – pradyu May 04 '16 at 06:44
  • Normally we keep it `Integer`. You can use nvl function which would return 0 incase there is no row present in table – Shirishkumar Bari May 04 '16 at 06:54
  • sorry i am pretty late but ,i used the same syntax and got an exception"You can't specify target table 'tablename' for update in FROM clause" – pradyu May 04 '16 at 10:01
  • this is the query :'String queryString = "INSERT INTO tablename(SL_No,candidate,phone,pan,mailid) VALUES((MAX(SL_No)+1 SL_No from tablename), ?, ?, ?, ? ))";' – pradyu May 04 '16 at 10:17
  • you missed `SELECT` in the VALUES clause for finding maximum SL_No – Shirishkumar Bari May 04 '16 at 10:46
  • sorry that was an editing mistake. this is the query:"'String queryString = "INSERT INTO tablename(SL_No,candidate,phone,pan,mailid) VALUES (SELECT(MAX(SL_No)+1 SL_No from tablename), ?, ?, ?, ? ))";'" – pradyu May 04 '16 at 10:51
  • So does this query worked for you... If yes please mention accordingly... So that other users won't be investing time for this question. You can accept appropriate answer. – Shirishkumar Bari May 04 '16 at 12:30
  • Thanks for pointing me in right direction, i had to do some minor tweaks to your code. please check my answer to this question ...it works! – pradyu May 04 '16 at 12:47
0

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

DD77
  • 776
  • 2
  • 8
  • 25
0

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • so, can i create a procedure to replicate increment function depending on the where condition?? – pradyu May 04 '16 at 06:29
0

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";
pradyu
  • 91
  • 8
  • This should work as well provided you have unique columns. It seems "on duplicate key update" avoids this behavior for me. ``` String queryString = "INSERT INTO hcl_candidates(candidate,phone,pan,mailid) SELECT ?, ?, ?, ? FROM hcl_candidates on duplicate key update candidate=values(candidate), phone=values(phone), pan=values(pan), mailid=values(mailid)"; ``` – pycvalade May 21 '20 at 17:13