mysql organizing database entries

Question

I wondered if it is possible to reorder the entries of a table in mysql, overwriting the deleted entries, and without breaking the other tables. If possible, which command would you use and show me some example.

For example: In table X you have the data:

+----+----------+
| ID | coluna 1 |
+----+----------+
| 1  | exemplo  |
| 2  | exemplo  |
| 3  |          | (apagado)
| 4  | exemplo  |
| 5  |          | (apagado)
| 6  | exemplo  |
+----+----------+

Reordering the table:

+----+----------+
| ID | coluna 1 |
+----+----------+
| 1  | exemplo  |
| 2  | exemplo  |
| 3  | exemplo  |
| 4  | exemplo  |
+----+----------+

Thank you all.

Answer 1

It is possible, but it is not as easy as in a pile, if we imagine a pile, physically, in an environment that there is gravity, if you take any element from it, the ones above will logically "fall" and occupy new positions. A table is not a stack...

To answer your question:

The easiest way would be to delete the ID column and recreate it.

alter table X drop column ID; /*Exclui-se a coluna*/
alter table X add column ID integer not null auto_increment primary key;

There is only one problem in doing this if your table has FK (Foreign Keys) you will not be able to delete the column without losing references in the other tables.

If you need to keep the reference, recommend using a stored procedure (Stored Procedure), for example, once a week, or per month...

The Stored Procedure would need to have one cursor and a accountant. The cursor would serve for you to go through the table (nothing more than a SELECT) and the counter will contain the expected ID value, which is nothing more than the expected ID value (i.e., the ID of the previous record + 1). Inside the cursor loop, you do the check (IF) if the ID is equal to the counter value, if it is beauty, you add 1 to the counter and follow the loop, if it is not you do an update on that line, informs that its ID is now the counter value and then follows the process (add one to the counter and follow the loop)

Just remembering that this is a very high cost for the database (cost => Processing), I would like to know why you want to do this...

Some points on this reordering:

• ID is short for Identity or Identification, It doesn’t make sense for you to keep changing this field. Imagine that every time a person dies, the Government wants to update the CPF and/or RG of all the others who remained alive ? Have you thought about the chaos ?
• Imagine you have a table with 10k (thousand) records. You delete the 1, doing the last way I said, you would have 9999 UPDATES, because everyone would be wrong (you update the 2 to 1, the 3 to 2... the 10000 to 9999), you can be sure that you have a mega LOCK in your database and many I/O problems.
• If you use the ID for some external reference (be it to generate links on your site, access to report by another system, whatever), while doing so, like you update in the other location that is saved, which you updated in your table so that the reference is not lost ?
• You will use a lot of processor and memory from your server just to make the records "organized".
• I recommend that you do not delete/remove the records from the database, but rather mark them (flag) as inactive, so you have them saved in your database, but your system knows that it is not to use, by doing this you can restore some record easily, and still do not have this problem of "organization".

Answer 2

believe it is not possible, but you can arrow the value to each deletion.

ALTER TABLE users AUTO_INCREMENT=4;