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]