#MYSQL INSERT INTO INCREMENT VALUE CODE#

You can use a pair of statements: DROP TABLE and CREATE TABLE to reset the auto-increment column. Using DROP TABLE and CREATE TABLE statements

#MYSQL INSERT INTO INCREMENT VALUE CODE#

The following illustrates the syntax of the TRUNCATE TABLE statement: TRUNCATE TABLE table_name Code language: SQL (Structured Query Language) ( sql )īy using the TRUNCATE TABLE statement, you delete all data from the table permanently and reset the auto-increment value to zero. The TRUNCATE TABLE statement removes all the data from a table and resets the auto-increment value to zero. We have three rows with the last auto-increment value is 3 instead of 4, which is what we expected. Now, let’s try to insert a new row into the tmp table and query data from it to see the effect: INSERT INTO tmp( name) However, you can reset the number generated by MySQL to 3 by using the ALTER TABLE statement as the following: ALTER TABLE tmp AUTO_INCREMENT = 3 Code language: SQL (Structured Query Language) ( sql ) If you insert a new row, MySQL will assign 4 to the id column of the new row. WHERE ID = 3 Code language: SQL (Structured Query Language) ( sql ) Let’s delete the last record in the tmp table with id value 3: DELETE FROM tmp Notice that the value must be greater than or equal to the current maximum value of the auto-increment column. You specify the table name after the ALTER TABLE clause and the value which you want to reset to in the expression AUTO_INCREMENT=value. The syntax of the ALTER TABLE statement to reset the auto increment value is as follows: ALTER TABLE table_name AUTO_INCREMENT = value Code language: SQL (Structured Query Language) ( sql )

You can reset the auto-increment value by using the ALTER TABLE statement. Perfect! It is time to practice reset the auto-increment value of the ID column. We have three rows with values of ID column are 1, 2, and 3. Tmp Code language: SQL (Structured Query Language) ( sql ) Third, query the tmp table to verify the insert operation: SELECT

( 'test 3') Code language: SQL (Structured Query Language) ( sql ) Second, insert some sample data into the tmp table: INSERT INTO tmp( name) ) Code language: SQL (Structured Query Language) ( sql ) MySQL reset auto increment value examplesįirst, create a table named tmp and assign the AUTO_INCREMENT attribute to the id primary key column. In MySQL, you can reset auto increment values in various ways. Sometimes, you may need to reset the value of the auto-increment column so that the first record’s identity that you insert into the table starts from a specific number e.g., 1. Whenever you insert a new row into a table, MySQL automatically assigns a sequence number to the AUTO_INCREMENT column.įor example, if the table has eight rows and you insert a new row without specifying the value for the auto-increment column, MySQL will automatically insert a new row with id value 9. Typically, you use the AUTO_INCREMENT attribute for the primary key column of the table. You can assign the AUTO_INCREMENT attribute to a column of a table to generate a unique identity for the new row. MySQL provides you with a useful feature called auto-increment. Summary: in this tutorial, we will show you various ways to reset auto-increment values of AUTO_INCREMENT columns in MySQL.

Mysql insert into increment value