On 11/25/07, Chris W <2wsxdr5@stripped> wrote:
> Stut wrote:
> > insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp
> > where p = 2)
> > Probably not very efficient, but it works.
> > -Stut
> Auto increment is much easier to do. If your primary key is made up of
> two fields and one of them is Auto Increment, then it will have the
> desired behavior, just do this experiment and see....
> CREATE TABLE `t` (
> `p` int(10) unsigned NOT NULL default '0',
> `q` int(10) unsigned NOT NULL auto_increment,
> PRIMARY KEY (`p`,`q`)
> ) ;
> INSERT INTO `t` (`p`,`q`) VALUES
> Chris W
OK, I will try that. Thanks for the help.
My assumption in reading your original post was that you didn't understand
what I wanted to do (but you did).
In order for me to use the solution you have proposed, in addition to
working, this behavior would have to be described in the MySQL manual. The
reason is that this seems to be the kind of behavior that could change from
version to version.
<BEGIN IMPORTANT POINT>
I don't suppose you know the section in the manual that defines the behavior
<END IMPORTANT POINT>
The issue is that unless this behavior is defined, changing autoincrement
from the behavior you described to a simpler version that just uses
ascending integers with no other context is the kind of thing where the guys
at MySQL might reason that it won't affect anyone or wasn't defined in a
specific way anyway.
Strictly speaking, this feared change wouldn't affect the logical correct
operation of my database (there would still be key uniqueness), but the neat
n, n+1, n+2 ordering I'm looking for in "q" would confuse humans.
Phrased more compactly: unless MySQL calls out this behavior in the
documentation, your solution scares the snot out of me.
Thank you sincerely, Dave.