sqlite3 - SQLite increases auto-inc value even if record isn't added -

i believe there's way in innodb stop auto-increment value increasing if record insert attempt failed/was ignored:

innodb_autoinc_lock_mode

however, each time connects server, use query on sqlite3:

"insert or ignore iplist (ip) values (" + string(ip) + "); "

unfortunately, if ip in table, auto increment value increases anyways. if lots of people connect server value incredibly high.

how stop doing in sqlite3?

the documentation says:

note "monotonically increasing" not imply rowid increases one. 1 usual increment. however, if insert fails due (for example) uniqueness constraint, rowid of failed insertion attempt might not reused on subsequent inserts, resulting in gaps in rowid sequence. autoincrement guarantees automatically chosen rowids increasing not sequential.

to ensure autoincrement values sequential, drop autoincrement keyword table definition , use plain integer primary key:

the autoincrement keyword imposes cpu, memory, disk space, , disk i/o overhead , should avoided if not strictly needed. not needed.

on insert, if rowid or integer primary key column not explicitly given value, filled automatically unused integer, 1 more largest rowid in use. true regardless of whether or not autoincrement keyword used.