On Friday 13 December 2002 20:43, you wrote: Hi Sean,
this is not a bug: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html For MyISAM and BDB tables you can specify AUTO_INCREMENT on secondary column in a multi-column key. In this case the generated value for the autoincrement column is calculated as MAX(auto_increment_column)+1) WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups. Regards Georg > Hi all, I have recently encountered some strange (to me) behaviour with an > auto > increment column. It resulted because of an oversight in table creation. > Here is some SQL statements that will illustrate my issue: > > CREATE TABLE test ( > ID INT AUTO_INCREMENT, > Name CHAR(30), > KEY (Name, ID) > )TYPE=MyISAM; > > INSERT INTO test SET Name="bob"; > INSERT INTO test SET Name="tom"; > > > Now, both of those columns got ID=1 from the insert. > > A few observations: > 1) MySQL requires AUTO_INCREMENT columns to have an index. This table > uses ID as an index, but it's not a leftmost-prefix of any index, which > i thought would give > a create error. (is this a bug?) > 2) considering this non auto increment behaviour, i would think that ID > should be NULL, since > ID is allowed NULL values, and no value was assigned to the ID in the > insert statements. > > Also, this works the same if ID is defined as "ID INT NOT NULL > AUTO_INCREMENT" > > > It seems that this is what's happening: > When MySQL executes the insert queries, it correctly recognizes that ID is > an AUTO_INCREMENT > column, so it tries to get the new auto increment value. But, because ID > isnt "properly" indexed for > an auto increment column, the value is incorrectly reported, and the column > is set to 1 every time. > > weird, huh? > sean peters > [EMAIL PROTECTED] > > > mysql, query > > > --------------------------------------------------------------------- > 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
