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" <[EMAIL PROTECTED]>
To: "Konrad Kieling" <[EMAIL PROTECTED]>,
"mysql List" <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
