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
