From: Paul DuBois [mailto:[EMAIL PROTECTED]At 21:09 +0200 6/16/03, Fernando Martins wrote: >In AUTO_INCREMENT fields, inserting a 0 into the field requests a new >sequence number. > >Is it possible to turn off this behaviour? (without messing with the code, >of course)
No.
> >Using NULL to get the next sequence number is good enough for me and I use >quite a lot 0 for special purposes (default record values,...)
That is an unsupported use of an AUTO_INCREMENT column, which is intended to hold only positive integer values. Not negative values, and not zero.
Hmm, the manual is not very consistent with that: "AUTO_INCREMENT will not work with negative numbers."; "It’s possible to create a row where the AUTO_INCREMENT column is 0 by explicitly setting the column to 0 with an UPDATE statement)"; and some examples using UNSIGNED.
(You will find, for example, that if you run CHECK TABLE on your table, it will complain about 0 values in an AUTO_INCREMENT column. Also, dumping and restoring the table will change your zero values.)
Damn! Glad you told me that! I had missed it in the manual. By dumping, you mean BACKUP, mysqldump, SELECT * INTO OUTFILE or all of them? The manual is also not explicit on that.
I'm not sure what happens with BACKUP TABLE, but I suspect that it doesn't alter the values in an AUTO_INCREMENT column because it operates using direct file copy operations. Any method that dumps table contents and them reloads them record by record certainly will change 0 values in an AUTO_INCREMENT columns. This includes reloading output produced by mysqldump or SELECT ... INTO OUTFILE.
I've to say (not to complain) this looks wrong. Definitely unexpected. (A dump/restore modifying data)
The manual recomends using NULL over 0 as the magic value to use to get the sequence number. I had some hopes this could mean some way to cancel the magic effect of 0 (hence my post) or some future plans to drop it. Any chances?
I don't know, but I'd say it's unlikely. You're probably better off using another column to indicate whether or not records are special.
Thanks, Fernando
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
