I am migrating a MongoDB database to MySQL and I'm not sure how I should go about managing the IDs, I need to still have access to the ObjectIDs since they are being used in QR codes printed in labels.
The first kind of obvious thought is just having the original ObjectIDs as the IDs. This would be the simplest way to do it, I'm only concerned if MySQL has any trouble or performance hits with that since the new rows post-migration will be UUIDs.
Another solution I could think of was to let them auto generate UUIDs on the migration and have a separate table relating the old ObjectIDs to the new UUIDs. I could check before doing the query whether the ID I'm getting is an ObjectID or a UUID and determine if I need to fetch through this table or just directly on the main table.
And a third solution would be to have a column hold the ObjectID and index through it, but this doesn't look correct since none of the newly added rows will ever use it.
Which of these three would be the best solution? Could there be a better solution that I'm missing?
If your target is MySQL (or MariaDB), each row should have some column that is Unique throughout the table. That could be an auto_increment, a UUID, or some "natural" key. If the current ObjectID is Unique, plus numeric, string, or BINARY, then it is probably fine. There is probably some size limit on a PRIMARY KEY.
Since your current ObjectIDs are 12 bytes, there should be no problem. If that is a fixed size and "binary", use BINARY(12).
Keep it simple for now. Don't worry about performance unless you have millions or rows. (Even then, there may not be a performance issue.)