Jump to content
xisto Community
Sign in to follow this  
badinfluence

Can Reset The Id Auto Increment? phpMyadmin

Recommended Posts

regarding phpmyadmin database. I write 2 digits for the max length of value for ID field. So, the user can keep input entry until 99 times. (The ID field is auto-increament)

I write php code to autoDelete preview 30 entries, when it is come to 60th entry, meaning I just store 30 entries on database.

So, the id will be start from 31 to 61, then theRow of entry that has id 0 to 30 are deleted, myphpadmin database can jump backTo start from id 0 again when reach the 99th id?

If the IDCannot jump back to start from 0, then it will not allow to input entry anymoreSince the max length of id is 2 digits only.

Can anyone help me in this? Because I worry one day in future, my database table ID reach the max of length even I put the length as 36 digits. Or can phpmyadmin can has infinity length of the value?

Share this post


Link to post
Share on other sites
reset autoincrement columnCan Reset The Id Auto Increment?

Hi I have a autonumber field set to non duplicating, I'm adding someRecords to the table and I messed up so I needed to delete then andStart again, but this time the autonumber field started incrementingFrom the last deleted record. How can I get it to start incrementingFrom the last record not from a record that isn't there. THANKS

-reply by rumon

Share this post


Link to post
Share on other sites

hi,Can we reset the id auto increment in the mysql database using by phpMyadmin?
let's say the shoutbox. the id #number table.. somehow i deleted some rows.. and i wanted to re-order/reset the id number acendingly in order rather than skip out..
thks


Did anyone try going to the Operations menu in phpmyadmin and change the value of auto_increment. You can enter any number from which u want to start the next entry just make sure that there are no duplicate entries..cheers

Share this post


Link to post
Share on other sites

yes we can reset the autoincrement using phpMyadmin.

please see the link below
http://webobserve.blogspot.de/2011/02/reset-mysql-table-autoincrement.html




hi,Can we reset the id auto increment in the mysql database using by phpMyadmin?
let's say the shoutbox. the id #number table.. somehow i deleted some rows.. and i wanted to re-order/reset the id number acendingly in order rather than skip out..
thks


Share this post


Link to post
Share on other sites

hi,Can we reset the id auto increment in the mysql database using by phpMyadmin?
let's say the shoutbox. the id #number table.. somehow i deleted some rows.. and i wanted to re-order/reset the id number acendingly in order rather than skip out..
thks



Yes, delete the auto-incremented field, re-create with auto-increment and primary key. Done!

Share this post


Link to post
Share on other sites

Yes, delete the auto-incremented field, re-create with auto-increment and primary key. Done!


This is the answer. This works perfectly. Thanks!

Share this post


Link to post
Share on other sites

Yes, delete the auto-incremented field, re-create with auto-increment and primary key. Done!


Such an easy solution I hadnt even thought of it. Thanks for the common sense approach.

Share this post


Link to post
Share on other sites

hi,

Can we reset the id auto increment in the mysql database using by phpMyadmin?

let's say the shoutbox. the id #number table.. somehow i deleted some rows.. and i wanted to re-order/reset the id number acendingly in order rather than skip out..

thks

 

ALTER TABLE `table_name` AUTO_INCREMENT=1

 

Share this post


Link to post
Share on other sites

If you are about to export your data and load it into a re-created table, hold your horses. You do not have to go through all of that effort and you might actually corrupt your data while in the process if it is not performed correctly.You can reset the auto increment to start with a different value, however you cannot set it to a value less than the maximum value in the column. You can find the highest value in the column by using a SELECT query with the MAX group function and then set the auto_increment value to a number one value higher using an ALTER TABLE statement.Here is an example. Assume that you have a table named "department" that is defined as "create table department (id int auto_increment, name varchar(16));"Next, insert some rows into the department table using the statements "insert into department(name) values('administration');" and "insert into department(name) values('sales');" This should use the IDs one and two. You can check this by selecting the rows from the table using a "select * from department;". This should display the values: 1 - administration, and 2 - sales. Now, assume that you deleted the sales row accidentally with a "delete from department where name = 'sales';". You are now left with exactly one row, "1 - administration". Suppose you want to re-create the sales row with the same ID, you know that the highest ID is 1, but just to double-check, execute the query: "select max(id) from department;"To set the auto_increment to 2, type in the query "alter table department auto_increment = 2;"All of these SQL statements can be executed in phpMyAdmin or just about any other SQL interface to MySQL, including the MySQL command line utility.

Share this post


Link to post
Share on other sites

I can add that you need to change the auto increment value only when you finished testing your working software and when you make it public, forget the primary key values, it's not for you, it's for the database, don't care about it, if you want ordered numbers or something like that, you can add an extra column and use it, leave the primary keys, foreign keys alone after the software has gone public, just remember to reset it after your tests, or unit test or etc. before making it public by truncating the database and making the application available for everyone, it's an unique row number and it's a bad practice to make nr. 5 to nr. 2 as it looses it's unique value, why people worry about it? Who cares if the primary key is 451 5489416 154983 15496 or 1 2 3 4, it doesn't make any difference the the application if you code well.

Share this post


Link to post
Share on other sites

I know this is old but I just found this post on Google so I'm going to add my two pence. This can be achieved in PHPMyAdmin by clicking the "Empty" button (top right), or in SQL as:

TRUNCATE TABLE tablename

Share this post


Link to post
Share on other sites

hi,Can we reset the id auto increment in the mysql database using by phpMyadmin?
let's say the shoutbox. the id #number table.. somehow i deleted some rows.. and i wanted to re-order/reset the id number acendingly in order rather than skip out..
thks



Its really simple check out this post.

http://www.electrictoolbox.com/reset-auto-increment-value-mysql/

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.