CODEWITHSUNDEEP
mysqldatabase
jonagoldman
jonagoldman

Reputation: 8754

Reorder / reset auto increment primary key

I have a MySQL table with an auto increment primary key. I deleted some rows in the middle of the table. Now I have, for example, something like this in the ID column: 12, 13, 14, 19, 20. I deleted the 15, 16, 17 and 18 rows.

I want to reassign / reset / reorder the primary key so that I have continuity, i.e. make the 19 a 15, the 20 a 16, and so on.

How can I do it?

Upvotes: 162

Views: 264939

Answers (17)

Ibrahim Hossain
Ibrahim Hossain

Reputation: 71

Delete your id column and execute ALTER TABLE table_name ADD COLUMN id INT NOT NULL AUTO_INCREMENT UNIQUE;

Upvotes: 0

Anush B M
Anush B M

Reputation: 91

This works - https://stackoverflow.com/a/5437720/10219008.....but if you run into an issue 'Error Code: 1265. Data truncated for column 'id' at row 1'...Then run the following. Adding ignore on the update query.

SET @count = 0;
set sql_mode = 'STRICT_ALL_TABLES';
UPDATE IGNORE web_keyword SET id = @count := (@count+1);

Upvotes: 2

Viraj Amarasinghe
Viraj Amarasinghe

Reputation: 941

My opinion is to create a new column called row_order. then reorder that column. I'm not accepting the changes to the primary key. As an example, if the order column is banner_position, I have done something like this, This is for deleting, updating, creating of banner position column. Call this function reorder them respectively.

public function updatePositions(){
    $offers = Offer::select('banner_position')->orderBy('banner_position')->get();
    $offersCount = Offer::max('banner_position');
    $range = range(1, $offersCount);

    $existingBannerPositions = [];
    foreach($offers as $offer){
        $existingBannerPositions[] = $offer->banner_position;
    }
    sort($existingBannerPositions);
    foreach($existingBannerPositions as $key => $position){
        $numbersLessThanPosition = range(1,$position);
        $freshNumbersLessThanPosition = array_diff($numbersLessThanPosition, $existingBannerPositions);
        if(count($freshNumbersLessThanPosition)>0) {
            $existingBannerPositions[$key] = current($freshNumbersLessThanPosition);
            Offer::where('banner_position',$position)->update(array('banner_position'=> current($freshNumbersLessThanPosition)));
        }
    }
}

Upvotes: 0

Shubham Malik
Shubham Malik

Reputation: 163

SELECT * from `user` ORDER BY `user_id`; 

SET @count = 0;

UPDATE `user`  SET `user_id` = @count:= @count + 1;

ALTER TABLE `user_id` AUTO_INCREMENT = 1;

if you want to order by

Upvotes: 7

Shubham Malik
Shubham Malik

Reputation: 163

SELECT * from user ORDER BY user; SET @count = 0; UPDATE user SET uid = @count:= @count + 1; ALTER TABLE user AUTO_INCREMENT = 1;

if you want to order by

Upvotes: 0

Anjani Barnwal
Anjani Barnwal

Reputation: 1522

in phpmyadmin

note: this will work if you delete last rows not middle rows.

goto your table-> click on operations menu-> goto table options->change AUTO_INCREMENT to that no from where you want to start.

your table autoincrement start from that no.

try it. enter image description here

Upvotes: 2

Grwww
Grwww

Reputation: 91

The best choice is to alter the column and remove the auto_increment attribute. Then issue another alter statement and put auto_increment back onto the column. This will reset the count to the max+1 of the current rows and thus preserve foreign key references back to this table, from other tables in your database, or any other key usage for that column.

Upvotes: 0

Rodrigo Prazim
Rodrigo Prazim

Reputation: 858

I had the same doubts, but could not make any changes on the table, I decided doing the following having seen my ID did not exceed the maximum number setted in the variable @count:

SET @count = 40000000;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

ALTER TABLE `users` AUTO_INCREMENT = 1;

The solution takes, but it's safe and it was necessary because my table owned foreign keys with data in another table.

Upvotes: 1

Chris Russo
Chris Russo

Reputation: 480

You can also simply avoid using numeric IDs as Primary Key. You could use Country codes as primary id if the table holds countries information, or you could use permalinks, if it hold articles for example.

You could also simply use a random, or an MD5 value. All this options have it's own benefits, specially on IT sec. numeric IDs are easy to enumerate.

Upvotes: -1

Luis
Luis

Reputation: 9

for InnoDB, do this (this will remove all records from a table, make a bakcup first):

SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS ;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION ;
SET NAMES utf8 ;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 ;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 ;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' ;
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 ;
/* ================================================= */

drop table tablename;
CREATE TABLE `tablename` (
   table structure here!

) ENGINE=InnoDB AUTO_INCREMENT=  ai number to reset  DEFAULT CHARSET= char set here;



/* ================================================= */
SET SQL_MODE=@OLD_SQL_MODE ;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS ;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS ;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT ;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS ;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION ;
SET SQL_NOTES=@OLD_SQL_NOTES ;

Upvotes: 0

lbrutti
lbrutti

Reputation: 1183

Or, from PhpMyAdmin, remove "AutoIncrement" flag, save, set it again and save.this resets it.

Upvotes: 12

Tom Haigh
Tom Haigh

Reputation: 57815

You could drop the primary key column and re-create it. All the ids should then be reassigned in order.

However this is probably a bad idea in most situations. If you have other tables that have foreign keys to this table then it will definitely not work.

Upvotes: 106

willy
willy

Reputation: 23

You can remove the primary key auto increment functionality of that column, then every time you update that column run a query before hand that will count all the rows in the table, then run a loop that iterates through that row count inserting each value into the respective row, and finally run a query inserting a new row with the value of that column being the total row count plus one. This will work flawlessly and is the most absolute solution to someone trying to accomplish what you are. Here is an example of code you may use for the function:

$table_row_count = mysql_result(mysql_query("SELECT COUNT(`field_1`) FROM `table`"), 0);
$viewsrowsdata = mysql_query("
    SELECT `rank`, `field1`, `field2`, `field3`, `field4`
        FROM (SELECT (@rank:=@rank+1) as `rank`, `field1`, `field2`, `field3`, `field4`
            FROM (SELECT * FROM `views`) a
            CROSS JOIN (SELECT @rank:=0) b
            ORDER BY rank ASC) c
");
while ($row = mysql_fetch_assoc($viewsrowsdata)) {
    $data[] = $row;
}
foreach ($data as $row) {
    $new_field_1 = (int)$row['rank'];
    $old_field_1 = (int)$row['field1'];
    mysql_query("UPDATE `table` SET `field_1` = $new_field_1 WHERE `field_1` = $old_field_1");
}
mysql_query("INSERT INTO `table` (`field1`, `field2`, `field3`, `field4`) VALUES ('$table_row_count' + 1, '$field_2_value', 'field_3_value', 'field_4_value')");

Here I created an associative array which I had appended on a rank column with the query within a select query, which gave each row a rank value starting with 1. I then iterated through the associative array.

Another option would have been to get the row count, run a basic select query, get the associative array and iterate it through the same way but with an added variable that updates through each iteration. This is less flexible but will accomplish the same thing.

$table_row_count = mysql_result(mysql_query("SELECT COUNT(`field_1`) FROM `table`"), 0);
$viewsrowsdata = mysql_query("SELECT * FROM `table`");
$updated_key = 0;
while ($row = mysql_fetch_assoc($viewsrowsdata)) {
    $data[] = $row;
}
foreach ($data as $row) {
    $updated_key = $updated_key + 1;
    mysql_query("UPDATE `table` SET `field_1` = '$updated_key' WHERE `field_1` = '$row['field_1']'");
}
mysql_query("INSERT INTO `table` (`field1`, `field2`, `field3`, `field4`) VALUES ('$table_row_count' + 1, '$field_2_value', 'field_3_value', 'field_4_value')");

Upvotes: 0

Anshul
Anshul

Reputation: 5518

Even though this question seems to be quite old, will post an answer for someone who reaches in here searching.

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

If the column is used as a foreign key in other tables, make sure you use ON UPDATE CASCADE instead of the default ON UPDATE NO ACTION for the foreign key relationship in those tables.

Further, in order to reset the AUTO_INCREMENT count, you can immediately issue the following statement.

ALTER TABLE `users` AUTO_INCREMENT = 1;

For MySQLs it will reset the value to MAX(id) + 1.

Upvotes: 418

BANK
BANK

Reputation: 221

SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

I think this will do it

Upvotes: 20

user188226
user188226

Reputation:

You may simply use this query

alter table abc auto_increment = 1;

Upvotes: 33

rxgx
rxgx

Reputation: 5160

To reset the IDs of my User table, I use the following SQL query. It's been said above that this will ruin any relationships you may have with any other tables.

ALTER TABLE `users` DROP `id`;
ALTER TABLE `users` AUTO_INCREMENT = 1;
ALTER TABLE `users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Upvotes: 83

Related Questions