0

I have searched for an answer for days, however I can't seem to find the right solution. Therefore, I ask the following question:

Suppose I have a table with a column ID which is an AUTO_INCREMENT field and a column Word which is unique. I run the following queries:

"INSERT IGNORE INTO Table (Word) VALUES('Test')"
"INSERT IGNORE INTO Table (Word) VALUES('Test1')"
"INSERT IGNORE INTO Table (Word) VALUES('Test2')"
"INSERT IGNORE INTO Table (Word) VALUES('Test')" //THIS ONE WILL BE IGNORED

The problem is I can't get the last $mysqli->insert_id from the last query, because it isn't inserting anything. However I need this ID which is already in the DB. therefore, I thought I should use a ON DUPLICATE KEY UPDATE statement, however this leads to the situation where AUTO_INCREMENT is skipping values, because it updates the value but ALSO increments the AUTO_INCREMENT value although this value isn't assigned to any row.

So in the end, I end up with a table like this:

ID |Word
1  |Test
2  |Test1
3  |Test2
//Trying to insert words that where already in the table..
12 |Test3
//Trying to insert words that where already in the table..
17 |Test4
TVA van Hesteren
  • 1,031
  • 3
  • 20
  • 47
  • 2
    Why does this matter? – Niet the Dark Absol Apr 06 '17 at 09:24
  • I think this is a duplicate of http://stackoverflow.com/a/6291568/1625955, maybe that helps. – Jens Apr 06 '17 at 09:27
  • @NiettheDarkAbsol, because I have many rows that get a IGNORE or DUPLICATE UPDATE and my integers grow substantially due to this. Therefore, I get very large integers which could be much smaller (over 1000th times as small, thats why) – TVA van Hesteren Apr 06 '17 at 09:31
  • @Jens, thanks for your link. However, the statements I'm executing are in a transaction already. Therefore, this is not the optimal solution for me, since I will need another transaction? – TVA van Hesteren Apr 06 '17 at 09:31
  • 1
    Integers are always 4 bytes anyway, and you have room for 4 billion. If that's not enough, use `BIGINT`. But realistically you aren't gonna need it. – Niet the Dark Absol Apr 06 '17 at 10:52
  • @NiettheDarkAbsol, I understand form a storage perspective that it isn't relevant, however it's not working as it is supposed to which I am trying to achieve though. – TVA van Hesteren Apr 06 '17 at 10:53

1 Answers1

1

My answer would be to first retrieve the id for the word from the table and only if it fails to insert it. In both cases you have the id ready.

My guess is also that it will be faster this way around since you are not creating any ignored errors in mysql.

snitch182
  • 723
  • 11
  • 21