Thimble Smith e-mailed me on Wed Mar 1 16:15:11 2000
(Re: "Re: is there any way to AUTO_INCREMENT fields with non-zero DEFAULTs?")
>
>>1) In many cases, I do want a "fresh" ID from an auto_increment field, but
[deletia]
>> small set of data and ids. Is there the possiblity of adding something
>> like a "RECYCLE" flag to the field definition, allowing for the old
>> functionality?
[deletia]
>Once we have "RECYCLE", do we need to add "FIRST_AVAILABLE" too? :)
Excellent point! ;-) Kind of a long identifier, though. I'd want
"SEQUENCE", but I'm not sure if the meaning of either is apparent to a new
user (or what the comparison of the two would be). The MySQL way seems to
go along lines of a "make sure everything is defined - leave no hidden
defaults" philosophy, so we would need the "default" identifier as well
(which would add *even more* length to the already long AUTO_INCREMENT
field line).
>have gotten used to using it that way. But...maybe it would be useful
>and not add too much bulk.
I haven't delved into the MySQL code much, but I'm guessing that in the
.MYI file the last "highest" number is stored and is incremented/used when
the next INSERT() is done. All you'd need to do is update this number
(like you said, with a select max(id)) whenever an update (on the key
itself) or a delete (on a row) is done *and* the updated/deleted row
contains the current MAX number. It would be a performance hit as compared
to the MyISAM way (select when the MAX is changed might get costly), no
doubt about it, but updates/deletes that don't involve the MAX number
shouldn't be much different (a comparison to check for MAX).
I don't know how much of that makes sense. ??
>>2) I'm wondering if any field with an auto_increment should be
>> automatically declared as an unsigned field? Trying to use
>> negative numbers causes an error to occur:
>>
>> CREATE TEMPORARY TABLE foo (
>> ikey SMALLINT AUTO_INCREMENT PRIMARY KEY,
>> name CHAR(30)
>> ) AUTO_INCREMENT=-30
>
>I think that looks like a bug. :) I'd like to handle it in the
>same way that, say, trying to define a CHAR(N) AUTO_INCREMENT
>field is handled:
>
> mysql> create table bogus (id char(2) not null auto_increment primary key);
> ERROR 1063: Incorrect column specifier for column 'id'
Okay, I'm guessing you want it to report an error if somebody specified the
AUTO_INCREMENT table option with a negative value (and not an error to
define an AUTO_INCREMENT field and NOT declare it UNSIGNED). I still think
MySQL should do a "quiet" conversion to UNSIGNED for AUTO_INCREMENT fields.
Worst case scenario: users *won't* hit a limit at 32,000 rows with a signed
SMALLINT field! ;-)
--
Nem W Schlecht schlecht@stripped
NDSU Library http://www.nodak.edu/~nem/
"Perl did the magic. I just waved the wand."
