From: Martin Gainty Date: November 25 2007 8:48pm Subject: RE: Incrementing a "Private" Integer Space List-Archive: http://lists.mysql.com/mysql/210192 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_1b76660e-2e5a-4721-b9be-005527a6a0be_" --_1b76660e-2e5a-4721-b9be-005527a6a0be_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Dave is trying to establish an algorithm which would fit your requirement I can see column q is auto-increment which makes sense as you retain the ca= pability to generate a unique row but jumping into the middle of a conversa= tion without knowing the prior discussionWhat is/was/will be the purpose of= column p..?Can we denormalise a bit and extrapolate the value of column p = based on known value of column q?Martin-___________________________________= ___________Disclaimer and confidentiality noteEverything in this e-mail and= any attachments relates to the official business of Sender. This transmiss= ion is of a confidential nature and Sender does not endorse distribution to= any party other than intended recipient. Sender does not necessarily endor= se content contained within this transmission.> > On 11/25/07, Chris W <2ws= xdr5@stripped> wrote:> >> > Stut wrote:> > >> > > insert into test1 set p = =3D 2, q =3D (select max(q) + 1 from test1 as tmp> > > where p =3D 2)> > >>= > > Probably not very efficient, but it works.> > >> > > -Stut> > >> > Aut= o 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> > (1,NULL),> > (1,NULL),> > (1,NULL),> > (2,NULL),> > (2,NULL)= ,> > (2,NULL),> > (2,NULL),> > (2,NULL),> > (3,NULL),> > (3,NULL);> >> > --= > > Chris W> > KE5GIX> > > Hi Chris,> > OK, I will try that. Thanks for the= help.> > My assumption in reading your original post was that you didn't u= nderstand> 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 h= ave 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.> > > I don't suppose you know the section in the manual tha= t defines the behavior> you're describing?> > > The is= sue is that unless this behavior is defined, changing autoincrement> from t= he behavior you described to a simpler version that just uses> ascending in= tegers 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 w= ay anyway.> > Strictly speaking, this feared change wouldn't affect the log= ical 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 s= incerely, Dave. _________________________________________________________________ Put your friends on the big screen with Windows Vista=AE + Windows Live=99. http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=3DTXT_TAGLM_C= PC_MediaCtr_bigscreen_102007= --_1b76660e-2e5a-4721-b9be-005527a6a0be_--