MySQL primary key auto-added configuration

Article One original address: http://ej38.com/showinfo/mysql-202971.html

Article two: Click to transfer to the second article

In database applications, we often use a unique number. The Auto_increment property of the field can be generated automatically in MySQL. MySQL supports a variety of data tables, each of which has a different self-increment property.

ISAM table

If you insert a null into a auto_increment data column, MySQL will automatically generate the next serial number. Numbering starts at 1, and 1 increments by cardinality.

When you insert a record, the value is not explicitly specified for auto_increment, which is equivalent to inserting a null value.

When inserting records, if a numeric value is explicitly specified for the Auto_increment field, there are two cases where an error message occurs if the inserted value repeats with an existing number because the value of the Auto_increment data column must be unique; In case two, If the inserted value is greater than the numbered value, it is inserted into the data column and the next number is incremented from the new value. In other words, you can skip some numbers.

If the maximum value of the self-increment sequence is deleted, the value is reused when a new record is inserted.

If the self-increment column is updated with the update command, an error occurs if the column value repeats with the existing value. If it is greater than the existing value, the next number increments from that value.

If you modify an existing record in the table based on the value in the Auto_increment data column using the Replace command, the Auto_increment data is listed in the WHERE clause of the Replace command, and the corresponding auto_ The increment value will not change. However, if the Replace command modifies an existing record by using a different primary KEY OR unique index (that is, the Auto_increment data column does not appear in the WHERE clause of the Replace command), the corresponding auto_ The increment value-if it is set to null (if it is not assigned a value), it will change.

The last_insert_id () function obtains the last number that is automatically generated by the self-increment column. However, the function is only relevant to the value generated during this session of the server. If the Auto_increment value has not been generated in this session with the server, the function returns 0.

The automatic numbering mechanism for other datasheets is based on the mechanism in the ISAM table.

MyISAM Data Sheet

After you delete the largest numbered record, the number is not reusable.

You can use the "auto_increment=n" option to specify a self-increment initial value when building a table.

Use the ALTER TABLE TABLE_NAME AUTO_INCREMENT=N command to reset the starting value of the increment.

You can use composite indexes to create multiple, independent, self-increment sequences in the same data table by creating a primary KEY OR unique index that consists of multiple data columns for the datasheet and auto_ The Increment data column is included in this index as its last data column. Thus, in this composite index, each of the preceding columns of data constitutes a unique combination, and the Auto_increment data column at the end produces a sequence number corresponding to that combination.

Heap Data Sheet

The heap data table is not allowed to use the self-increment column starting with MySQL4.1.

Self-increment can be set by the AUTO_INCREMENT=N option of the CREATE TABLE statement.

The self-increment initial value can be modified by the AUTO_INCREMENT=N option of the ALTER TABLE statement.

Numbering is not reusable.

The heap data table does not support the use of composite indexes in a single data table to generate multiple serial numbers that do not interfere with each other.

BDB Data Sheet

You cannot change the self-increment initial value by using the AUTO_INCREMENT=N option of the CREATE TABLE OR ALTER TABLE.

Reusable numbering.

Supports the use of composite indexes in one data table to generate multiple serial numbers that do not interfere with each other.

INNDB Data Sheet

You cannot change the self-increment initial value by using the AUTO_INCREMENT=N option of the CREATE TABLE OR ALTER TABLE.

Non-reusable numbering.

Using composite indexes in one data table is not supported to generate multiple serial numbers that do not interfere with each other.

Attention

When using auto_increment, you should pay attention to the following points:

Auto_increment is a property of a data column and applies only to integer type data columns.

The data column that sets the Auto_increment property should be a positive sequence, so the data column should be declared as unsigned so that the number of the sequence can be incremented by one time.

The Auto_increment data column must have a unique index to avoid ordinal duplication.

The Auto_increment data column must have a NOT NULL property.

The maximum value of the Auto_increment data column ordinal is constrained by the data type of the column, such as the maximum number of tinyint data columns is 127, or, for example, unsigned, the maximum is 255. Once the upper limit is reached, the auto_increment will fail.

When a full table is deleted, Auto_increment restarts numbering from 1. Full table deletion means that when the following two statements are emitted:

Delete from table_name, or TRUNCATE TABLE table_name
This is because in the case of full table operation, MySQL is actually doing such an optimization operation: first delete all data and indexes in the data table, then rebuild the datasheet. If you want to delete all rows of data and want to keep the sequence number information, use a delete command with where to suppress MySQL optimizations:

Delete from table_name where 1;
This will force MySQL to perform the evaluation of the conditional expression once for each deleted data row.

Force MySQL to no longer use the sequence values that have been used: create a data table specifically designed to generate the auto_increment sequence, and never delete records for that table. When you need to insert a record into the main table, insert a null value in the table that specifically generates the ordinal to produce a number, and then, when inserting data into the main data table, use the last_insert_id () function to obtain the number and assign it to the data column of the main tables ' stored sequence. Such as:

INSERT INTO ID set id = null;insert into main set main_id = last_insert_id ();
Use the ALTER command to add a data column with the Auto_increment property to a data table. MySQL will automatically generate all the numbers.

The simplest way to rearrange an existing sequence number is to first delete the column and then rebuild it, and MySQL will regenerate the sequential numbering sequence.

To generate a sequence without auto_increment, use the last_insert_id () function with parameters. If you use a parameter last_insert_id (expr) to insert or modify a data column, and then call the last_insert_id () function without parameters, the second function call returns the value of expr. Here's how it's done:

First, create a data table with only one data row: The CREATE TABLE seq_table (id int unsigned NOT NULL), insert into seq_table values (0), and then retrieve the serial number using the following operation:

Update seq_table Set seq = last_insert_id (seq + 1); select last_insert_id ();

Second post

Original address: http://www.111cn.NET/database/MySQL/60352.htm

This article is a simple MySQL self-increment initial value of an example to guide a large number of MySQL primary key to get the changes and some common examples, let's look at the hope that the example will help you.

ALTER TABLE table_name AUTO_INCREMENT=N;
Note N can only be greater than the integer value of the existing auto_increment, and the value less than is invalid.
Show table status like ' table_name ' you can see that the Auto_increment column is a value that is present.
The step value cannot be changed. Use only with the last_inset_id () function mentioned below

MySQL can use auto_increment to set the value of the primary key to self-growth, its default value is 1, if you want to set its initial value to 1000, it is more stupid to first insert a record and specify the value of the primary key is 999, and then delete the row record, for example:

The code is as follows	Copy Code
INSERT into Test (PK) values (999); Delete from test where PK = 999;

A better approach is to use the Alter method to modify it directly, for example:

The code is as follows	Copy Code
ALTER TABLE Test auto_increment = 1000;

Example

1, do not control the starting point of the primary key

The code is as follows	Copy Code
CREATE TABLE Emb_t_dictbustype ( Emb_c_bustypeid int NOT NULL auto_increment, Emb_c_bustypeenname varchar (255) is not NULL, Emb_c_bustypezhname varchar (255) is not NULL, Primary KEY (Emb_c_bustypeid) ) Engine=innodb default CHARSET=GBK;

2, control the starting point of the primary key

The code is as follows	Copy Code
CREATE TABLE Emb_t_dictbustype ( Emb_c_bustypeid int NOT NULL auto_increment, Emb_c_bustypeenname varchar (255) is not NULL, Emb_c_bustypezhname varchar (255) is not NULL, Primary KEY (Emb_c_bustypeid) ) Engine=innodb auto_increment=1001 default CHARSET=GBK;

Self-increment primary key zeroing

Method One:

If none of the previous data is needed, you can simply empty all the data and restore the self-increment field from 1 to counting
TRUNCATE TABLE name

Method Two:

The current value of DBCC CHECKIDENT (' table_name ', reseed, New_reseed_value) is set to New_reseed_value. If a row has not been inserted into the table since the table was created, the first row inserted after the DBCC checkident is executed uses new_reseed_value as the identity. Otherwise, the next inserted row will use New_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, a No. 2627-number error message is generated later when the table is referenced. Www.111cn.net
Method Two does not empty the existing data, the operation is more flexible, not only can the self-increment zero, but also for the deletion of a large number of consecutive rows, reset self-increment and insert new data, or start from a new value, of course, can not and existing conflicts.

The code is as follows	Copy Code
$sql = "Delete from $table _vote"; mysql_query ($sql, $link); $sql = "ALTER TABLE $table _vote auto_increment=1"; mysql_query ($sql, $link);

Get the self-increment primary key "4 methods"

Typically, after inserting an insert operation on MySQL in the app, we need to get the self-increment primary key for the inserted record. This article describes the 4 ways in which the Java environment obtains the value of the record primary key auto_increment after insert:

The InsertRow () method provided by JDBC2.0
The Getgeneratedkeys () method provided by JDBC3.0
Through the SQL Select LAST_INSERT_ID () function
Through the SQL @ @IDENTITY variable

1. The InsertRow () method provided by JDBC2.0
Since jdbc2.0, this can be done in the following way.

The code is as follows

Copy Code

Statement stmt = null; ResultSet rs = null; try { stmt = Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY,//Create statement Java.sql.ResultSet.CONCUR_UPDATABLE); Stmt.executeupdate ("DROP TABLE IF EXISTS autoinctutorial"); Stmt.executeupdate (//Create demo table The CREATE TABLE autoinctutorial (" + "Prikey INT not NULL auto_increment," + "DataField VARCHAR (+), PRIMARY KEY (Prikey))"; rs = Stmt.executequery ("Select Prikey, DataField"//Retrieving data + "from autoinctutorial"); Rs.movetoinsertrow (); Move cursor to row to insert (pseudo record not created) Rs.updatestring ("DataField", "AUTO INCREMENT here?"); Modify Content Rs.insertrow (); Inserting records Rs.last (); Move cursor to last row int Autoinckeyfromrs = Rs.getint ("Prikey"); Gets the primary key prekey of the record you just inserted Rs.close (); rs = null; System.out.println ("Key returned for inserted row:" + Autoinckeyfromrs); } finally { Close () Cleanup of rs,stmt }

2. The Getgeneratedkeys () method provided by JDBC3.0

The code is as follows

Copy Code

Statement stmt = null; ResultSet rs = null; try { stmt = Conn.createstatement (Java.sql.ResultSet.TYPE_FORWARD_ONLY, Java.sql.ResultSet.CONCUR_UPDATABLE); // ... Omit several lines (create demo table as above example) ... www.111cn.net Stmt.executeupdate ( "INSERT into Autoinctutorial (DataField)" + "VALUES (' Can I Get the Auto Increment Field? ')", Statement.return_generated_keys); Indicates to the driver that an automatic acquisition of generatedkeys! is required int autoinckeyfromapi =-1; rs = Stmt.getgeneratedkeys (); Get the self-increment primary key! if (Rs.next ()) { Autoinckeyfromapi = Rs.getint (1); } else { Throw an exception from here } Rs.close (); rs = null; System.out.println ("Key returned from Getgeneratedkeys ():" + Autoinckeyfromapi); } finally {...}

When using Auto_increment, the following points should be noted:

Auto_increment is a property of a data column and applies only to integer type data columns.

The data column that sets the Auto_increment property should be a positive sequence, so the data column should be declared as unsigned so that the number of the sequence can be incremented by one time.

The Auto_increment data column must have a unique index to avoid ordinal duplication.

The Auto_increment data column must have a NOT NULL property.

The maximum value of the Auto_increment data column ordinal is constrained by the data type of the column, such as the maximum number of tinyint data columns is 127, or, for example, unsigned, the maximum is 255. Once the upper limit is reached, the auto_increment will fail.

MySQL primary key auto-added configuration