Using an INSERT statement with ON DUPLICATE KEY UPDATE. Each time there is a duplicate entry, nothing is inserted (as expected), but it affects the auto increment ID, which then becomes unavailable and produces gaps when eventually a NON-DUPLICATE INSERT happens. My routine uses hundreds of INSERTS which correctly fails because they are duplicates, but then this leaves large gaps of hundreds between the indexes, for the next unique item that does get inserted. Would rather this not happen.
Is there a way to use this INSERT statement with ON DUPLICATE KEY UPDATE without affecting the index? I think I can figure a longer way work-around, but am hoping there might be a more elegant solution with some minor tweaks.
A bit of my test code is shown below:
//tag insert, update count if existing, insert new item if not existing
//get next available auto increment index - for testing/index verification purposes
$q = $db->query("SHOW TABLE STATUS LIKE 'tags'");
$next = $q->fetch(PDO::FETCH_ASSOC);
echo 'Next Available Index is: '. $next['Auto_increment']; echo '<br>';
$tag_name = 'unique';
$count = 10;
$stmt = $db->prepare('INSERT INTO tags (tag_name, blog_count) VALUES(:tag_name, :blog_count)
ON DUPLICATE KEY UPDATE tag_name= :tag_name, blog_count= :blog_count');
$stmt->bindParam(':tag_name', $tag_name, PDO::PARAM_STR);
$stmt->bindParam(':blog_count', $count, PDO::PARAM_STR);
$stmt->execute();
//get next available auto increment index - for testing/index verification purposes
$q = $db->query("SHOW TABLE STATUS LIKE 'tags'");
$next = $q->fetch(PDO::FETCH_ASSOC);
echo 'Next Available Index is: '. $next['Auto_increment']; echo '<br>';
Each time I run this, the available index increments even though nothing is inserted - which is what is expected, but not desired. Would prefer the available index to remain the same if nothing is inserted.
Thank you in advance for your feedback!
