good point. the only real way to duplicate auto increment logic is to
create a new table with 1 row and 1 field. and lock, select, and update that
row.
thanks,
-- Andrew
----- Original Message -----
From: "Johnny Withers" <[EMAIL PROTECTED]>
To: "'Andrew Schmidt'" <[EMAIL PROTECTED]>
Sent: Thursday, August 16, 2001 1:02 PM
Subject: RE: auto_increment vs. heap tables
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Except, if you have a table like so:
>
> +----+-----+
> | id | num |
> +----+-----+
> | 1 | 1 |
> | 2 | 2 |
> | 3 | 3 |
> | 4 | 4 |
> | 5 | 5 |
> +----+-----+
>
> And remove record 5..
> mysql> delete from aitest where id=5;
> Query OK, 1 row affected (0.02 sec)
>
> mysql> select max(id)+1 as nextid from aitest;
>
> +--------+
> | nextid |
> +--------+
> | 5 |
> +--------+
>
>
> You will see that you get nextid as being 5, again..
> this is wrong, nextid will be 6.
>
> mysql> insert into aitest(num) values(6);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from aitest;
> +----+-----+
> | id | num |
> +----+-----+
> | 1 | 1 |
> | 2 | 2 |
> | 3 | 3 |
> | 4 | 4 |
> | 6 | 6 |
> +----+-----+
>
>
> - ---------------------
> Johnny Withers
> [EMAIL PROTECTED]
> p. 601.853.0211
> c. 601.209.4985
>
>
> - -----Original Message-----
> From: Andrew Schmidt [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 16, 2001 10:18 AM
> To: Leos Bitto; [EMAIL PROTECTED]
> Subject: Re: auto_increment vs. heap tables
>
>
> auto increment is really just a nice way of doing the following
> (really nice
> way actually):
>
> lock table x
> select max(id) + 1 from table x
> insert into table x values (newid)
> unlock table x
>
> this is under the assumption table x as 1 field called id which has a
> primary key on it. and newid is the id recieved from the select.
>
> so doing it manually with heap tables seems quite feasable just a
> little
> extra work.
>
> thanks,
>
> - -- Andrew
>
> - ----- Original Message -----
> From: "Leos Bitto" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, August 14, 2001 6:05 AM
> Subject: auto_increment vs. heap tables
>
>
> > Hello,
> >
> > I am currently changing a simple PHP4-based application from
> > PostgreSQL 7.1 to MySQL 3.23. I have some tables in the database
> > which I would like to create as TYPE=HEAP in MySQL. Because I have
> > read the MySQL manual (applause here, please!) I know that
> > auto_increment cannot be used there. This hurts me a lot, and given
> > that MySQL doesn't support sequences, it nearly prohibits the usage
> > of heap tables in my application.
> >
> > However, I am still not giving up, so here's the question: how
> > difficult would it be to get rid of that restriction for
> > auto_increment in heap tables? The ideal answer I am hoping for is
> > 'yeah, that's easy - wait for 3.23.42' or 'try this patch and let
> > us know if it works for you'.
> >
> > Thanks in advance for any response.
> >
> >
> > Leos Bitto
> >
> >
> >
> > --------------------------------------------------------------------
> > - Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try:
> > http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
> - ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>
>
> iQA/AwUBO3v8qbFNxPoD98ryEQLTugCgoEEhzKz/VraPLlDS4+VTuQBTeosAoKrD
> 1RgfMXrbh3jf6EFdWPFpn9cW
> =I63n
> -----END PGP SIGNATURE-----
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
