> 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]
