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]
