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]
