Rearranging Auto Incremented Rows After Deleting A Specific Row -

Question

I created a table users with row ID and with auto increment but when I deleted a user SQL doesn't arrange the id of users it remains as it is like:

Mona 1
Ahmed 15
john 17

I want to arrange them automatically without manual correction

auto increment ID will not reset to next sequence. You need to write that code by yourself. On the other note, auto increment can be reset by doing `ALTER TABLE table_name AUTO_INCREMENT = 1;` query — Danish Hakim Khan, Apr 15 '19 at 10:29
Whatever you decide to do you will always need to use an order by clause to 'arrange' the data on a select - so I don't see the point of going to the bother of rebuilding an auto_increment column. — P.Salmon, Apr 15 '19 at 10:36
If this field is your primary key then you shouldn't ever attempt to change the value in it. The value should provide you a unique, unchanging reference to that item of data, which can be relied on by others. You should not ascribe any other meaning to it. Otherwise, somewhere else if you have a reference to record 17, and then change the ID of 17 to 10, this makes the reference to 17 useless, and your data integrity is destroyed... you'll start to return incorrect data from your queries. — ADyson, Apr 15 '19 at 11:37
If you want to create a neat sequence of consecutive numbers to show in a list of your rows, then use a separate field for that, and update it separately whenever you add or remove a row - possibly a trigger would be a good way to achieve that — ADyson, Apr 15 '19 at 11:38

score 2 · Answer 1 · answered Apr 15 '19 at 11:46

auto_increment does NOT reuse numbers. If you manually update auto_increment, MySQL will start re-arranging records on disk. This will make your database slow, very, very slow.

Solution: do not use auto_increment for sequential number labeling.

auto_increment does not provide sequential numbers, it provides unique numbers and it uses a sequential algorithm to do so. Having gaps is normal, trying to fix it ruins the performance of your database.

TL;DR:

if you need sequential row numbering, you have to maintain the numbers manually
rearranging values of auto_increment incurs a huge penalty, you'll make your db slow and you'll most likely ruin the integrity

Just don't touch it.

score 1 · Answer 2 · answered Apr 15 '19 at 10:36

Deleting an entry from a table would not affect AUTO_INCREMENT field.

You would have to truncate your table to reset the AUTO_INCREMENT field, but this operation would delete all the data of your current table.

OR

Set the AUTO_INCREMENT field to the id of last deleted row, as suggested in other comments. But this could break the consistency of existing records if any intermediate row is deleted.

score 0 · Answer 3 · answered Apr 15 '19 at 10:36

Using ALTER TABLE statement

Say , you have deleted a row in a table

DELETE FROM tmp WHERE ID = 3;

If you insert a new row, MySQL will assign 4 to the id column of the new row. 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;

Using TRUNCATE TABLE statement

The TRUNCATE TABLE statement removes all the data from a table and resets the auto-increment value to zero.

TRUNCATE TABLE table_name;

Using DROP TABLE and CREATE TABLE statements

Like the TRUNCATE TABLE statement, those statements drop the table and recreate it, therefore, the value of the auto-increment is reset to zero.

DROP TABLE table_name;
CREATE TABLE table_name(...);

ref http://www.mysqltutorial.org/mysql-reset-auto-increment

Tushar Gabhane · Answer 4 · 2020-07-17T05:45:41.157

I have a solution which works for me

Simple PHP script to rearrange database row (count)

    $servername = "$sname";
    $username = "$uname";
    $password = "$pass";
    $dbname = "$dname";

   // Create connection
        $con = new mysqli($servername, $username, $password, $dbname);
      // Check connection
      if (mysqli_connect_errno())
        {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        exit;
        }
        $conn = $con;

        $sql = "SELECT * FROM `$tname` ORDER BY `$column_name` ASC LIMIT $r_limit";
        $result=mysqli_query($conn, $sql);
              $counter='';
if ($result->num_rows > 0) {
    while($row = mysqli_fetch_array($result))                         
      {
         $counter++;         
         $id=$row["id"];

        $update_sql="UPDATE `$tname` SET `$column_name`='$counter' WHERE `id`='$id'";
        
      if ($conn->query($update_sql) === TRUE) {
         $Status="updated successfully";
      }else {
         $Status="Error: " . $conn->error;
      }

    }
}

Click here for Complete Script https://github.com/tushargabhane3?tab=projects

This Script will arrange id column at a sequence, start from 1

We Only enter some few parameters in text box like servername, dbname, table & etc.

After that this script rearrange the any column and show all output in a table format.

Rearranging Auto Incremented Rows After Deleting A Specific Row -

4 Answers4