Auto-Inc gaps - Id value explodes
I call this "burning ids".  I think it happens with all flavors of INSERT, including INSERT IGNORE, IODKU, REPLACE, etc.

Here is a workaround:  http://mysql.rjweb.org/doc.php/staging_table#normalization

That describes how to use 2 SQLs to "normalize" a batch of names and get their ids in an efficient way, and without burning ids.  If you have only a single name to insert/lookup, it is still 2 sqls.

INSERT allocates as many ids as it might need, then burns the ones it did not need.


I think the autoinc_mode is global, and hurts performance.
Sure, BIGINT will work, but I hate to use 8 bytes.
And, yeah, my recommendation is similar to your #3.  But look closely; there may be some useful differences, especially if you can batch things.