Re: Unique Id generation
> Why would the auto_increment not work for you? The only case where you
> would have a problem is if the last record was deleted before mysql
> shutdown. If you are really concerned about this unique scenario,
> insert a dummy record before shutdown to guard against it and delete
> the dummy record after you start back up.
>
Hmm, and what about a server crash?
>
> On Tue, Aug 12, 2008 at 8:29 AM, <[EMAIL PROTECTED]> wrote:
> > Hi all,
> >
> > I try to generate a unique id for each row in a Mysql-InnoDB Table.
> Because of many deletes I can't use an auto_increment column.
> > After a Mysql restart, the next value for an auto_increment-column is
> max(auto_increment-column)+1, and I need a really unique id.
> >
> >
> > My first solution looks like this:
> > I use a second table with an auto-increment-column, and add an insert
> trigger to the first table.
> > The insert trigger adds a row in the second table and uses the
> last_insert_id() to get the unique value.
> > The (last) row in the second table will never be deleted.
> > Does anybody confirm with this solution?
> >
> > ###
> > drop table unique_id_messages_1;
> > create table unique_id_messages_1 (id bigint not null unique, subject
> text);
> >
> > drop table id_sequences_1;
> > create table id_sequences_1 (id bigint not null primary key
> auto_increment);
> >
> > drop trigger trg_unique_id_messages_1;
> >
> > DELIMITER |
> >
> > create trigger trg_unique_id_messages_1 BEFORE INSERT ON
> unique_id_messages_1
> > FOR EACH ROW BEGIN
> > insert into id_sequences_1 values ();
> > set NEW.id = (select last_insert_id());
> > END;
> > |
> > DELIMITER ;
> >
> > insert into unique_id_messages_1 (subject) values ("x1");
> > insert into unique_id_messages_1 (subject) values ("x2");
> > insert into unique_id_messages_1 (subject) values ("x3");
> > insert into unique_id_messages_1 (subject) values ("x4");
> > select * from unique_id_messages_1;
> > ###
> >
> >
> > Thanks in advance
> >
> > Rudi
> >
> >
> >
> > --
> > GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> > Jetzt dabei sein:
> http://www.shortview.de/[EMAIL PROTECTED]
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
--
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Unique Id generation
> >I try to generate a unique id for each row in a Mysql-InnoDB Table.
> >Because of many deletes I can't use an auto_increment column.
> >After a Mysql restart, the next value for an auto_increment-column is
> >max(auto_increment-column)+1, and I need a really unique id.
> >
> [JS] See if the UUID() function will suit you.
unfortunately I forgot to note that I use statement-based replication, so I
can't use the UUID() function.
> >
> >My first solution looks like this:
> >I use a second table with an auto-increment-column, and add an insert
> >trigger to the first table.
> >The insert trigger adds a row in the second table and uses the
> >last_insert_id() to get the unique value.
> >The (last) row in the second table will never be deleted.
> >Does anybody confirm with this solution?
> >
> >###
> >drop table unique_id_messages_1;
> >create table unique_id_messages_1 (id bigint not null unique, subject
> >text);
> >
> >drop table id_sequences_1;
> >create table id_sequences_1 (id bigint not null primary key
> >auto_increment);
> >
> >drop trigger trg_unique_id_messages_1;
> >
> >DELIMITER |
> >
> >create trigger trg_unique_id_messages_1 BEFORE INSERT ON
> >unique_id_messages_1
> >FOR EACH ROW BEGIN
> > insert into id_sequences_1 values ();
> > set NEW.id = (select last_insert_id());
> >END;
> >|
> >DELIMITER ;
> >
> >insert into unique_id_messages_1 (subject) values ("x1");
> >insert into unique_id_messages_1 (subject) values ("x2");
> >insert into unique_id_messages_1 (subject) values ("x3");
> >insert into unique_id_messages_1 (subject) values ("x4");
> >select * from unique_id_messages_1;
> >###
> >
> >
--
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unique Id generation
I agree with Jerry. Take a look at the UUID() function.
http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_uuid
Second Life (http://secondlife.com) uses UUIDs to track millions and millions
of unique items every day.
CheersFish
-Original Message-
>From: Jerry Schwartz <[EMAIL PROTECTED]>
>Sent: Aug 12, 2008 9:46 AM
>To: [EMAIL PROTECTED], mysql@lists.mysql.com
>Subject: RE: Unique Id generation
>
>>I try to generate a unique id for each row in a Mysql-InnoDB Table.
>>Because of many deletes I can't use an auto_increment column.
>>After a Mysql restart, the next value for an auto_increment-column is
>>max(auto_increment-column)+1, and I need a really unique id.
>>
>[JS] See if the UUID() function will suit you.
>>
>>My first solution looks like this:
>>I use a second table with an auto-increment-column, and add an insert
>>trigger to the first table.
>>The insert trigger adds a row in the second table and uses the
>>last_insert_id() to get the unique value.
>>The (last) row in the second table will never be deleted.
>>Does anybody confirm with this solution?
>>
>>###
>>drop table unique_id_messages_1;
>>create table unique_id_messages_1 (id bigint not null unique, subject
>>text);
>>
>>drop table id_sequences_1;
>>create table id_sequences_1 (id bigint not null primary key
>>auto_increment);
>>
>>drop trigger trg_unique_id_messages_1;
>>
>>DELIMITER |
>>
>>create trigger trg_unique_id_messages_1 BEFORE INSERT ON
>>unique_id_messages_1
>>FOR EACH ROW BEGIN
>> insert into id_sequences_1 values ();
>> set NEW.id = (select last_insert_id());
>>END;
>>|
>>DELIMITER ;
>>
>>insert into unique_id_messages_1 (subject) values ("x1");
>>insert into unique_id_messages_1 (subject) values ("x2");
>>insert into unique_id_messages_1 (subject) values ("x3");
>>insert into unique_id_messages_1 (subject) values ("x4");
>>select * from unique_id_messages_1;
>>###
>>
>>
>>Thanks in advance
>>
>>Rudi
>>
>>
>>
>>--
>>GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
>>Jetzt dabei sein:
>>http://www.shortview.de/[EMAIL PROTECTED]
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>infoshop.com
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique Id generation
Why would the auto_increment not work for you? The only case where you
would have a problem is if the last record was deleted before mysql
shutdown. If you are really concerned about this unique scenario,
insert a dummy record before shutdown to guard against it and delete
the dummy record after you start back up.
--
Brent Baisley
On Tue, Aug 12, 2008 at 8:29 AM, <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I try to generate a unique id for each row in a Mysql-InnoDB Table. Because
> of many deletes I can't use an auto_increment column.
> After a Mysql restart, the next value for an auto_increment-column is
> max(auto_increment-column)+1, and I need a really unique id.
>
>
> My first solution looks like this:
> I use a second table with an auto-increment-column, and add an insert trigger
> to the first table.
> The insert trigger adds a row in the second table and uses the
> last_insert_id() to get the unique value.
> The (last) row in the second table will never be deleted.
> Does anybody confirm with this solution?
>
> ###
> drop table unique_id_messages_1;
> create table unique_id_messages_1 (id bigint not null unique, subject text);
>
> drop table id_sequences_1;
> create table id_sequences_1 (id bigint not null primary key auto_increment);
>
> drop trigger trg_unique_id_messages_1;
>
> DELIMITER |
>
> create trigger trg_unique_id_messages_1 BEFORE INSERT ON unique_id_messages_1
> FOR EACH ROW BEGIN
> insert into id_sequences_1 values ();
> set NEW.id = (select last_insert_id());
> END;
> |
> DELIMITER ;
>
> insert into unique_id_messages_1 (subject) values ("x1");
> insert into unique_id_messages_1 (subject) values ("x2");
> insert into unique_id_messages_1 (subject) values ("x3");
> insert into unique_id_messages_1 (subject) values ("x4");
> select * from unique_id_messages_1;
> ###
>
>
> Thanks in advance
>
> Rudi
>
>
>
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unique Id generation
>I try to generate a unique id for each row in a Mysql-InnoDB Table.
>Because of many deletes I can't use an auto_increment column.
>After a Mysql restart, the next value for an auto_increment-column is
>max(auto_increment-column)+1, and I need a really unique id.
>
[JS] See if the UUID() function will suit you.
>
>My first solution looks like this:
>I use a second table with an auto-increment-column, and add an insert
>trigger to the first table.
>The insert trigger adds a row in the second table and uses the
>last_insert_id() to get the unique value.
>The (last) row in the second table will never be deleted.
>Does anybody confirm with this solution?
>
>###
>drop table unique_id_messages_1;
>create table unique_id_messages_1 (id bigint not null unique, subject
>text);
>
>drop table id_sequences_1;
>create table id_sequences_1 (id bigint not null primary key
>auto_increment);
>
>drop trigger trg_unique_id_messages_1;
>
>DELIMITER |
>
>create trigger trg_unique_id_messages_1 BEFORE INSERT ON
>unique_id_messages_1
>FOR EACH ROW BEGIN
> insert into id_sequences_1 values ();
> set NEW.id = (select last_insert_id());
>END;
>|
>DELIMITER ;
>
>insert into unique_id_messages_1 (subject) values ("x1");
>insert into unique_id_messages_1 (subject) values ("x2");
>insert into unique_id_messages_1 (subject) values ("x3");
>insert into unique_id_messages_1 (subject) values ("x4");
>select * from unique_id_messages_1;
>###
>
>
>Thanks in advance
>
>Rudi
>
>
>
>--
>GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
>Jetzt dabei sein:
>http://www.shortview.de/[EMAIL PROTECTED]
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unique Id generation
Hi all,
I try to generate a unique id for each row in a Mysql-InnoDB Table. Because of
many deletes I can’t use an auto_increment column.
After a Mysql restart, the next value for an auto_increment-column is
max(auto_increment-column)+1, and I need a really unique id.
My first solution looks like this:
I use a second table with an auto-increment-column, and add an insert trigger
to the first table.
The insert trigger adds a row in the second table and uses the
last_insert_id() to get the unique value.
The (last) row in the second table will never be deleted.
Does anybody confirm with this solution?
###
drop table unique_id_messages_1;
create table unique_id_messages_1 (id bigint not null unique, subject text);
drop table id_sequences_1;
create table id_sequences_1 (id bigint not null primary key auto_increment);
drop trigger trg_unique_id_messages_1;
DELIMITER |
create trigger trg_unique_id_messages_1 BEFORE INSERT ON unique_id_messages_1
FOR EACH ROW BEGIN
insert into id_sequences_1 values ();
set NEW.id = (select last_insert_id());
END;
|
DELIMITER ;
insert into unique_id_messages_1 (subject) values ("x1");
insert into unique_id_messages_1 (subject) values ("x2");
insert into unique_id_messages_1 (subject) values ("x3");
insert into unique_id_messages_1 (subject) values ("x4");
select * from unique_id_messages_1;
###
Thanks in advance
Rudi
--
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
