Hello, I am trying to sync between 2 tables: I have active table where has auto_increment, and I have archive table with the same values. I would like both ID's to be unique (between the tables as well) - I mean, I would like to save auto incremenet, and if I UNION both table I still have uniqness. How can I do that? Is there a possibility to save auto increment when mysql is off?
A:
First off, this sounds like a bad idea - you shouldn't have an auto_increment in your archive table, since all the data is presumably copied directly from the live table (including its IDs).
However, here are some hacky solutions:
You can change the current
AUTO_INCREMENTof a table using:ALTER TABLE tbl_name AUTO_INCREMENT = some_new_valueSet it to a large offset for one of the tables, and you'll be safe for a while. Except when you reach the offset, having forgot this little hack, the whole house of cards will fall on your head.
You can simply add an offset by a constant when selecting (e.g.
SELECT id + 1000000 AS id) in one of the tables. Still a hack, but at least it's closer to the surface, and when you eventually reach an overlapping area, it's easier to fix.Finally, you can select the maximum ID from one table, then offset all the IDs in the other table by this value during the same select. Make sure you have a large enough datatype for the ID though.
Max Shawabkeh
2010-02-08 08:19:42
Hey, thanks! I dnot have archive auto_incremenet... only in the active. so you're suggesting to select the MAX id from archive table and modify the active's auto_increment to start from that point?
oshafran
2010-02-08 08:22:10
If you don't have `AUTO_INCREMENT` on the archive, and are copying the data with the IDs, why would the archive have conflicting IDs in the first place? If you generate the live table from scratch each time, you can set `AUTO_INCREMENT` in the `CREATE` statement.
Max Shawabkeh
2010-02-08 08:27:45
lets say I have in the active IDs 1 to 10. and I move them as-is to the archive, next time the Mysql will start, it will fill them again with 1 to 10, so i will have a problem copying them again to the archive.
oshafran
2010-02-08 08:40:04
Unless you recreate the table, the current value of `AUTO_INCREMENT` is saved by default. If you insert 10 records, remove them, then insert 10 more, the latter will have IDs 11-20.
Max Shawabkeh
2010-02-08 08:43:40
Wrong. restart mysql, and those values are reset.
oshafran
2010-02-08 08:50:53
MyISAM tables do retain their `AUTO_INCREMENT` index between server restarts, but the behaviour is different for for InnoDB (which resets to maximum each time). If you use InnoDB, you'll have to refresh the `AUTO_INCREMENT` on each restart by using `ALTER TABLE` and setting it to the maximum of both tables.
Max Shawabkeh
2010-02-08 09:58:40