[KLUG Members] Sequences & MySQL

[Bert Bbbink]

> This question was asked last night by an OpenGroupware developer; and
> since we have lots of MySQL users/(developers?) I thought someone here
> might know an answer.
> --------------
> I'm currently working on an MySQL adaptor for SOPE 4.5 (no comments,
> please! ;-)
> Does someone know how to 'emulate' sequences or a primary key generator
> with this high profile database system?
> And no, auto-increment fields are not sufficient - we create a bunch of
> connected records in one step so we need a unique (integer) key in
> advance.
> As far as I can see the only option is to create a helper table which
> contains one otherwise unused record for each unique key. Creating a
> new key would imply inserting a record in the helper and retrieving the
> auto-increment value (which is then used in the real tables).
> Obviously this is kinda fragile, so maybe someone knows a better
> workaround? :-|
> -------------

If I understand the problem than this could work.
use something like connection_id or session id to make an unique number,
(time is probally not good enough)
Insert the rows; then get the id of first row just inserted.
( select id from mytable where mykey = session_id order by id desc limit 1);
You'll would get the id first unique auto increment id from the inserted
rows, then use
update mytable set mykey = first_id where mykey = session_id;

or something like this.

Basically just insert the rows holding an unique indentifier and update
afterwards.

Bert.