mysql self-increasing AUTO_INCREMENT instance usage

Why use AUTO_INCREMENT?

Let’s examine the customers and orders tables again. Customers in the customers table are uniquely identified by the column cust_id, and each customer has a unique number. Similarly, each order in the orders table has a unique order number, which is stored in the column order_num. These numbers have no special meaning other than that they are unique. When adding a new customer or new order, a new customer ID or order number is required. These numbers can be arbitrary as long as they are unique.

Obviously, the simplest number used is the next number. The so-called next number is a number greater than the current maximum number. For example, if the maximum number of cust_id is 10005, the next customer inserted into the table can have a cust_id equal to 10006.

Is it simple? not necessarily. How do you determine the next value to use? Of course, you can use a SELECT statement to find the largest number (using the Max() function) and then add 1 to it. But this is not reliable (you need to find a way to ensure that no one else inserts rows between the SELECT and INSERT statements you execute, which is very likely for multi-user applications), And it's not very efficient (performing additional MySQL operations is definitely not ideal).

This is where AUTO_INCREMENT comes into play. Consider the following line of code (part of the CREATE TABLE statement used to create the customers table):

cust_id                int               NOT NULL AUTO_INCREMENT,

AUTO_INCREMENT Usage

AUTO_INCREMENT tells MySQL that this column should be used whenever Automatically increment when adding a row. Each time an INSERT operation is performed, MySQL automatically increments the column (hence the keyword AUTO_INCREMENT) and assigns the next available value to the column. This assigns each row a unique cust_id that can be used as a primary key value.

Note:

Only one AUTO_INCREMENT column is allowed per table, and it must be indexed (e.g., by making it a primary key). Overriding AUTO_INCREMENT If a column is specified as AUTO_INCRE-MENT, does it need to use a special value? You can simply specify a value in the INSERT statement, and as long as it is unique (has not been used so far), that value will be used instead of the automatically generated value. Subsequent increments will start using this manually inserted value.

Disadvantages:

Determine the AUTO_INCREMENT value One disadvantage of letting MySQL generate (via auto-increment) the primary key is that you don't know who the values ​​are.

select last_insert_id() function

Consider this scenario: You are adding a new order. This requires creating a row in the orders table, and then creating a row in the orderitms table for each item ordered. order_num is stored along with the order details in the orderitems table. This is why the orders table and the orderitems table are related tables. This obviously requires that you know the resulting order_num after inserting the orders row but before inserting the orderitems row.

So, how do you get this value when using the AUTO_INCREMENT column? This value can be obtained using the last_insert_id() function as follows:

select last_insert_id()

This statement returns the last AUTO_INCREMENT value, which can then be used in subsequent MySQL statements.

[Related recommendations]

1. php Chinese website special recommendation: php programmer toolbox download (one-click to build a php environment)

2. Detailed explanation of the primary key in the mysql database
   

The above is the detailed content of mysql self-increasing AUTO_INCREMENT instance usage. For more information, please follow other related articles on the PHP Chinese website!