MySQL set from the initial value of the growth primary key

ALTER TABLE table_name AUTO_INCREMENT=N;
Note that n can only be greater than an integer value of an existing auto_increment, and less than the value is invalid.
Show table status like ' table_name ' can see auto_increment this column is a performance value.
The step value cannot be changed. You can only use the following reference to the last_inset_id () function

MySQL can use auto_increment to set the value of the primary key is self growth, its default value is 1, if you want to set its initial value to 1000, the more stupid way is to insert a record and specify the primary key value of 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 Alter's method to modify directly, for example:

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

Example

1, do not control the start 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) NOT NULL, Emb_c_bustypezhname varchar (255) NOT NULL, Primary KEY (Emb_c_bustypeid) ) Engine=innodb default CHARSET=GBK;

2, the primary key to control the starting point

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) NOT NULL, Emb_c_bustypezhname varchar (255) NOT NULL, Primary KEY (Emb_c_bustypeid) ) Engine=innodb auto_increment=1001 default CHARSET=GBK;

Self-increasing primary key zeroing

Method One:

If no previous data is needed, you can clear all the data directly and start counting the self-added fields back from 1.
TRUNCATE TABLE name

Method Two:

The current value of the 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 it 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 error message will be generated when the table is referenced later. Www.111cn.net
Method Two will not empty the existing data, the operation is more flexible, not only can return the value of zero, but also for the deletion of a large number of consecutive rows, reset the value and insert new data, or starting from the 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 "4 methods" of the self-added primary key

Normally, when we insert a MySQL in our application, we need to get the self added primary key of the inserted record. This article describes 4 methods in the Java environment to get the value of the record primary key auto_increment after the insert:

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

1. The InsertRow () method provided through 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) "CREATE TABLE autoinctutorial" (" + "Prikey INT not NULL auto_increment," + "DataField VARCHAR (), PRIMARY KEY (Prikey)"); rs = Stmt.executequery ("SELECT Prikey, DataField"//Retrieve data + "from autoinctutorial"); Rs.movetoinsertrow (); Move the cursor to the row to be inserted (pseudo record not created) Rs.updatestring ("DataField", "AUTO INCREMENT here"); Modify Content Rs.insertrow (); Insert Record Rs.last (); Move the cursor to the last row int Autoinckeyfromrs = Rs.getint ("Prikey"); Gets the primary key prekey that just inserted the record Rs.close (); rs = null; System.out.println ("Key returned for inserted row:" + Autoinckeyfromrs); finally { Close () Cleanup of rs,stmt }

2. The Getgeneratedkeys () method provided through 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 a demo table like the previous example) ... www.111cn.net Stmt.executeupdate ( "INSERT into Autoinctutorial (DataField)" + "VALUES (' Can I get the Auto Increment Field? ')", Statement.return_generated_keys); To drive to indicate the need to automatically obtain generatedkeys! int autoinckeyfromapi =-1; rs = Stmt.getgeneratedkeys (); Get the self-added primary key! if (Rs.next ()) { Autoinckeyfromapi = Rs.getint (1); } else { Throw an exception from } 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 sequence of positive numbers, so the data column should be declared as unsigned so that the number of the sequence can be increased by one fold.

The Auto_increment data column must have a unique index to avoid duplicate serial numbers.

The Auto_increment data column must have a NOT NULL attribute.

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, and the maximum is 255 if unsigned is added. Once the cap is reached, the auto_increment will fail.