MySQL unique id through multiple tables

Question

hello everybody,

i wanna build a database-wide unique id. that unique id should be one field of every row in every table of that database. i have different ideas on how to achieve this, and really like to know whats your opinion...

First idea is to create one master-table with an auto-increment-field and a trigger in every other table, like "before insert here, insert in master-table -> get the auto-increment value -> and use this value as primary-key here"... i have seen something like this anywhere, but in fact: instead of making one INSERT, i would do 2 INSERTS, which should be not that performant.

The second one is to add a field "uniqueId" to every table, and fill this field with a PHP-generated integer... something like unix-timestamp plus a random number. but i had to use BIGINT as datatype, which means big index_length and big data_length.

Finally the third idea is also a "uniqueId" Field in every table, but instad of BIGINT i use VARCHAR and fill this with PHPs "uniqid()"...

im curious about your thoughts :-)

thanks, alex

p.s. im sorry if my english is not so well, its not my first language.

Answer 1

Since you are looking for opinions... Of the three ideas you give, I would "vote" for the uniqid() solution. It seems pretty low cost in terms of execution (but possibly not implementation).

A simpler solution (I think) would be to just add a field to each table to store a guid and set the default value of the field to be MySQL's function that generates a guid (I think it is UUID). This lets the database do the work for you.

And in the spirit of coming up with random ideas... It would be possible to have some kind of offline process fill in the IDs asynchronously. Make sure every table has the appropriate field and make the default value be 0/empty. Then the offline process could simply run a query on each table to find the rows that do not yet have a unique id and it could fill them in. That would let you control the ID and even use some kind of incrementing integer. This, of course, requires that you do not need the unique ID instantly each time a record is inserted.