List:General Discussion« Previous MessageNext Message »
From:Gabriel PREDA Date:March 30 2006 7:38am
Subject:Re: auto_increment and the value 0
View as plain text  
You can override MySQL behaviour of generating a new value if you insert a 0
into an auton_increment field.

Quoting from the manual:

> NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns.
> Normally, you generate the next sequence number for the column by inserting
> either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior
> for 0 so that only NULL generates the next sequence number.
> This mode can be useful if 0 has been stored in a table's AUTO_INCREMENTcolumn.
> (Storing
> 0 is not a recommended practice, by the way.) For example, if you dump the
> table with *mysqldump* and then reload it, MySQL normally generates new
> sequence numbers when it encounters the 0 values, resulting in a table
> with contents different from the one that was dumped. Enabling
> NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem.
> *mysqldump* now automatically includes in its output a statement that
> enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

So you must alter the SQL-mode:
Issue :
mysql>SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO;
For altering the sessions sql_mode...

now you will have to insert the dump with:
mysql>SOURCE /path/to/dump.sql

You can change it globally
mysql>SET @@global.sql_mode = NO_AUTO_VALUE_ON_ZERO;
But it's not recommend ... because until you change it back... MySQL will
not generate auto increment values for your inserts if you use 0... only if
you use NULL !!!
The advantage is that you can import the dump from the command line.

Another way is to add the statement:
SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO;
into the dump itself... but... i think it's not that small...

I believe this will do for you... but keep in mind that a value of 0 in an
auto_increment column is not a good thing ... as everybody said before !

Good luck !
Gabriel PREDA
Senior Web Developer

auto_increment and the value 0Brian Stanton29 Mar
  • Re: auto_increment and the value 0Daniel Kasak29 Mar
    • Re: auto_increment and the value 0mysql30 Mar
      • Re: auto_increment and the value 0Daniel Kasak30 Mar
        • Re: auto_increment and the value 0Simon Garner30 Mar
  • Re: auto_increment and the value 0Gabriel PREDA30 Mar