List:General Discussion« Previous MessageNext Message »
From:Bill Easton Date:February 11 2005 1:13pm
Subject:Re: global object-id
View as plain text  
Well, the answer is "no"--there's no magic way to have an auto_increment do
what you want.

You could use a GUID--basically a pseudo-random number such the expected
time to pick a duplicate is on the order of the lifetime of the universe.
But GUID's are big and ugly, and it would be nice to just have an integer.

Presumably, one of the things you'd like is to have inserts in different
threads run concurrently--so your increment field should be unique, even in
threads that haven't committed yet.

Here's a scheme that may help.  Yes, it has an extra table.  But it mostly
does what you want.

    create table global_increment (
        value int not null primary key auto_increment
    ) type=InnoDB.

You create a new value by

    insert into global_increment values (0);
    select last_insert_id();

Once you've got a new value, it's yours.  You can commit it immediately or
not, as you like, and still use it in your other tables without
interference.

Unfortunately, the global_increment table grows, and you need to delete old
values.  One way to do this is to estimate how many values are likely to
still belong to current transactions.  You can then do:

    commit;
    delete from global_increment where value < (<your_value> -
<said_estimate>);

(We use something like this as part of a scheme to automatically update
information shown on client screens when the database changes.)

HTH


===== original message follows =====

From: "Martijn Tonies" <m.tonies@stripped>
To: "Konrad Kieling" <konrad.kieling@stripped>,
 "mysql List" <mysql@stripped>
Subject: Re: global object-id
Date: Thu, 10 Feb 2005 16:57:49 +0100
> is there a simple way (ie without creating and deleting datasets in an
> extra table) to use an auto_increment id field in several tables. no
> table has to have all ids, so i cannot use the field of a master table
> in childs. it seems one can use a sequence in postgresql in different
> tables. is it possible in a similar way?

"sequences" are separate objects used to generated, guess what,
sequential numbers. The actual usage of the numbers is undefined.

Auto-inc is auto-inc. Plain and simple. It's something related to
a column in a particular table. Each table can have it's own
"instance" of 1 (and only 1) auto-incrementing column.

Short answer: no.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

Thread
global object-idKonrad Kieling10 Feb
  • Re: global object-idMartijn Tonies10 Feb
  • Re: global object-idSergei Golubchik10 Feb
Re: global object-idBill Easton11 Feb