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