Re: feature proposal: auto increment prefix

On Tue, Apr 29, 2003 at 03:46:44AM +0300, Michael Widenius wrote:
> 
> Hi!
> 
> Catching up with some very old emails...
> 
> >>>>> "Tim" == Tim Bunce <Tim.Bunce@xxxxxxxxx> writes:
> 
> Tim> This thread reminds me of a separate but related issue.
> Tim> It can often be useful to have a way to generate globally unique
> Tim> values that are guaranteed to not clash with any other value anywhere
> Tim> at anytime (even when merging data from systems that were previously
> Tim> not connected).
> 
> <cut>
> 
> Here is a description of a function that we plan to add to 4.1:
> 
> -------------
> 
> Implement a function OID() that should return a 64 bit unique number for each 
> invocation.
> 
> To make this independent of the server, it should construct the number as 
> follows:
> 
> server_id << 45 +
> (unix_timestamp_for_mysqld_start & (1 << 32-1)) << 24 +
> count++
> 
> (Here count is a 64 bit number)
> 
> The function has the following restrictions:
> 
> - We assume that server id < 512 (or at least the lowest 9 bits are unique)
> - The high bit for timestamps are not relevant (shouldn't be safe)
> - We don't execute more than 15 million OID() during one second
>   (To be exact, during X seconds of server uptime, we are not executing
>    more then X * 15 milllion OID() queries)
> - That the timestamp is different if the server restarts
> ­-----------

I'd suggest using the port number (lowest 9 bits) instead of server_id
*if* the server_id has not been explicitly set.

> One major benefit of the above is that it 'only' takes up 64 bits

True. I'm glad you've called it OID so GUID is available for a later
implementation of a truly globally unique id.


An natural extension would be to support using OID() as a default value...

        create table foo (
          id bigint default OID() not null primary key,
          ...

I'm sure that would be very useful in multiple-master environments.

Tim.

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=gcdmd-internals@xxxxxxxxxxx