auto-increment starting value 1

[Itshim]

If I have a column in a table like this:

member_id int unsigned NOT NULL auto_increment

Will MySQL ever assign zero as the auto-increment value?

If so, in the create table statement, is there a way to set the starting value of an auto-increment column? Reading the MySQL manual I see that you can run an 'ALTER TABLE' statement to reset the auto-increment value to a specific number such as:

ALTER TABLE [i]tbl_name[/i]  
AUTO_INCREMENT = [i]n[/i]

but, I that doesn't help because I want to ensure MySQL will never use zero for the auto-increment value.

Thanks,
Itshim

 

[sleipnir214]

This from the MySQL online manual entry for CREATE TABLE:

An integer column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. Such a column must be defined as one of the integer types as described in Section 11.1.1, “Overview of Numeric Types”. (The value 1.0 is not an integer.) See Section 22.2.3.36, “mysql_insert_id()”.

Specifying the NO_AUTO_VALUE_ON_ZERO flag for the --sql-mode server option or the sql_mode system variable allows you to store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value. See Section 5.3.1, “mysqld Command-Line Options”.

By default, even attempting to explicitly set an auto_increment column to zero will cause the column to generate a new value.

Note: There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

To be safe from problems with auto_increment columns rolling over to zero, use large unsigned column types.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Itshim]

Thank you once again sleipnir. Guess I need to familiarize myself with the manual more.

Itshim

 

[sleipnir214]

You don't necessarily have to familiarize yourself with the manual -- just get in the habit of searching the manual yourself.


To find the text in my earlier post, I went to

http://dev.mysql.com

and entered auto_increment in the search field in the upper right-hand corner of the page.

The very first link the search engine provided was to a manual section titled, "Using AUTO_INCREMENT". Near the end of the article was a section headed, "More information about AUTO_INCREMENT is available here:". The very first link in that section contained the information I posted earlier.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Itshim]

Dam, I was on the exact page you found the answer, before I posted, that is where I found the alter table information. I was skimming the page and found the heading AUTO_INCREMENT further down the page, but never saw the other info above. That has been happening to me recently.

Will read more carefully next time, before I start answering my own posts with RTFM.

Thanks.