MySQL auto increment

 

MySQL FAQ: How do I define a MySQL auto increment field?

Here's an example of the way I normally create a MySQL auto increment (auto_increment) field:

create table pizzas (
  id int auto_increment not null,
  name varchar(32) not null,
  primary key (id)
);

　　

As you can see from that MySQL syntax/example, there are two steps to this process. First, we define the id field as being an auto increment field (also known as a "serial" or "identity" field in other databases) of a not null int type, like this:

id int auto_increment not null,

　　

Then we specify that it is the primary key for our MySQL table, like this:

primary key (id)

　　

You can name your auto_increment field anything you want to, but modern frameworks like Ruby on Rails and CakePHP really encourage you to name it id.

Testing the MySQL auto increment field

You can easily test your MySQL auto increment field by inserting data into the database table, and then performing a SQL SELECT query on the table to validate that the auto increment field is increasing (and that you aren't getting any other errors).

I can use these statements to populate my pizzas sample database table:

insert into pizzas (name) values ('cheese');
insert into pizzas (name) values ('veggie');
insert into pizzas (name) values ('works');

　　

After inserting those records, when I perform my SQL SELECT query, like this:

select * from pizzas;

　　

I see the following output:

+----+--------+
| id | name   |
+----+--------+
|  1 | cheese | 
|  2 | veggie | 
|  3 | works  | 
+----+--------+

　　

As you can see, the id auto increment field is incrementing itself properly.

 

Need larger MySQL auto increment values?

If you need to store an awful lot of data in one table, it's important to know the limits of the MySQL numeric data types. For instance, the signed int auto increment field I showed above can go up to 2,147,483,647. If your table is going to hold more than two billion records, you'll want to use one of these larger data types instead:

unsigned int                    4,294,967,295
bigint              9,223,372,036,854,775,807
unsigned bigint    18,446,744,073,709,551,615

　　

Note/Caution: I have used an unsigned int as a MySQL auto increment, but I have not used either of the bigint types.

Getting the most recent MySQL auto_increment value

A common MySQL question is, "How do I get the value of my auto increment field after I run my SQL INSERT statement?"

To get the value that was automatically assigned to your auto increment field, use the MySQL last_insert_id function, like this:

select last_insert_id();

　　

Just be sure you run that command with the same MySQL database connection you used to perform the SQL INSERT (or your results will be wrong).