Avoiding auto-increment holes on InnoDB with INSERT IGNORE

MYSQL

Short answer:

  • Stop MYSQL server
  • Set innodb_autoinc_lock_mode=0 in my.ini. Note that there are multiple my.ini files in Mysql directories.  The one you want to change is in the $MYSQL_HOME directory.  You can use phpinfo() to view this environment variable.
  • Restart server
  • Use INSERT with ON DUPLICATE KEY UPDATE clause will not cause auto-increment holes.  MYSQL returns the following:
    • If the new row is inserted, the number of affected-rows is 1.
    • If the existing row is updated, the number of affected-rows is 2.
    • If the existing row is updated using its current values, the number of affected-rows is 0.