MySQL auto increment-the mysql auto_increment attribute

Source: Internet
Author: User
Tags one table ruby on rails

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.