Maintain unique id across multiple tables

I want to build a database-wide unique id. That unique id should be one field of every row in every table of that database.

There are a few approaches I have considered:

• 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 this before, but instead of making one INSERT, it does 2 INSERTS, whi开发者_如何学Pythonch I expect would not be that performant.

• 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 the datatype, which means big index_length and big data_length.

• Similar to the "uniqueId" idea, but instad of BIGINT I use VARCHAR and use uniqid() to populate this value.

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.