Delete one row in the middle and how to update table id (auto increment) in mysql

Question

For instance:

Row Name 1 John 2 May 3 Marry 4 Tom 5 Peter 

Suppose I delete line 2 and line 3, is it possible to update Tom and Peter to line id 2 and 3, respectively, and the next insert line will have line number 4?

Answer 1

yes, but you need to recreate the line:

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

Answer 2

No, because think about the problems this can create. Imagine that you are managing a store’s inventory and each item has an internal barcode based on the identifier in the database table. You decided never to store one element and not retrieve it from the database, and then each identifier for each element with an identifier larger than the deleted element is reduced by 1 ... all these barcodes on all these elements become useless.

Identification numbers in databases are usually not intended for human use or reference. They are intended for the static index of a specific element in the database, which allows the computer to quickly and reliably pull out a given record. When creating the identifier field of your table, make sure that the data type is large enough to account for these lost identifiers when they occur.

Answer 3

This is just an offer. I am not saying that this is the best solution. Just think it over.

You are fulfilling your removal request.

 DELETE FROM table_name WHERE Row IN (2,3); 

After deletion, you make a request for a selection request using PHP and get a data set into an array.

 SELECT Row, Name from table_name ORDER BY Row ASC; 

Then do the UPDATE using a loop.

 $index = 1; foreach($dataset as $item) { // Your update query $sql = "UPDATE table_name SET Row=$index where Name='" . $item['Name'] . "'"; $index++; } 

Before embedding the following query, you should get the maximum row value and set the value to +1 as the insert query row. This is just an idea. Not a complete code.