List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:March 5 2001 3:19pm
View as plain text  

>>>>> "btjones" == btjones  <btjones@stripped> writes:

btjones> I've been thinking on this one for awhile.  I noted a while back that
btjones> auto-increment gets confused if you insert a negative number into the
btjones> auto-increment field, and this has been reported as expected behavior.

btjones> My thoughts are there are two possible solutions to this problem --

btjones>      1. Make all auto_increment columns be assumed unsigned by the silent
btjones> column change routines.

This would be the easy thing to do.

btjones>      2. While it is technically bad practice, I've observed it to be
btjones> relatively common practice to use negative numbers in an auto-incrementing
btjones> primary key column to designate "special" items, such as items that were
btjones> manually added alongside an automated process or to designate data that is
btjones> "display restricted" and easily separated with a simple WHERE
btjones> (primary_key_column > 0).

btjones>      Looking in the source code, I found this section in  Is
btjones> this the only relevant section for updating the auto_increment value stored
btjones> in the table itself?  Does this handle all the various table types?  If so,
btjones> would a patch be out of the question?  It would seem you'd just need to
btjones> teach this routine to ignore negative numbers.  I think this would be an
btjones> ideal feature for mySQL.

btjones> void handler::update_auto_increment()


There is a little more that happens behind the scenes.  The most
important one is probably that some handlers, at least MyISAM,
automaticly adjust the next-to-be-used auto_increment value for
each insert and update.

The main problem is that on this level the handler doesn't know if the
value is signed or not.

Another problem is that I don't know of any database that has a
similar feature like 'auto_increment' that has a special case for
negative numbers.  I haven't really decided if it's even a good idea
to try to support negative numbers for the auto_increment column.  I
haven't yet seen a application where it would be critical to have this
feature;  If we would have this feature it would be nice to first try
to write a full specification of how this would work during all
possible circumstances, like what would happen if you try to insert
an auto_increment value of -1.

[auto_increment]btjones28 Feb
  • [auto_increment]Michael Widenius5 Mar