MySQL auto increment-the mysql auto_increment attribute

MySQL faq:how do I define a MySQL auto increment field?

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

CREATE TABLE pizzas (  ID int auto_increment NOT NULL,  name varchar (+) NOT NULL,  primary key (ID));

　　

As you can see from the that MySQL syntax/example, there is the steps to the this process. First, we define the id field as being a Auto increment field (also known as a "serial" or "Identity" field in other D atabases) of a NOT null int type, like this:

ID int auto_increment NOT NULL,

　　

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

Primary KEY (ID)

　　

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

Testing the MySQL auto increment field

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

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 can see, the id Auto increment field is incrementing itself properly.

Need larger MySQL auto increment values?

If you need to store a 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 and billion records, you'll want to use one of the these larger data types instead:

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

　　

NOTE/CAUTION:I has used an unsigned int as a MySQL auto increment, but I had not used either of the bigint type S.

Getting The most recent MySQL auto_increment value

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

To get the value of that is 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 this command with the same MySQL database connection your used to perform the SQL INSERT (or your ResU Lts'll be Wrong).

MySQL auto increment-the mysql auto_increment attribute