Problem with auto incrementing id column

Question

My db table looks like this pic. http://prntscr.com/22z1n

I recently created a delete.php page. it works correctly, but when I delete the 21st user, the next registered user will receive the 24th identifier instead of 21.

Is it possible to put newly registered users in the first empty line? (In this situation, the 21st line)

In my registration form, the newly registering user can write the names of existing users and be friends with them after registration. For this friendship, I have another table that links the identifier of the newly registered user and the existing user.

For this purpose, I use mysql_insert_id during registration to get the id for the new user. But after deleting the 21st line during the nex registration process, mysql_insert_id gave me the number 21. but it was saved in the 24th line. And put in the table 21 associations for the new user. I want to solve this problem.

Answer 1

The MySQL column auto_incrementmaintains the number inside and always increments it even after deletion. If you need to fill in the empty space, you should process it yourself in PHP, and not use the keyword auto_incrementin the table definition.

, , .

auto_increment reset SQL, , .

-- Doing this will cause problems!
ALTER table AUTO_INCREMENT=12345;

, , :

FOREIGN KEY (friendid) REFERENCES registration_table (id) ON DELETE SET NULL;

. , , . , PHP. mysql_insert_id() .

, , .

SELECT MAX(id) FROM registration_table;

Answer 2

, , , . , . .

Answer 3

Auto increment - , . , .

Answer 4

, . , ( ), SQL- , . , , . , , .

Answer 5

, , , , user_order. , .

, auto_increment.

Answer 6

This is not a good practice for the reset auto_increment value, but if you really need to do this, you can:

ALTER TABLE mytable AUTO_INCREMENT = 1;

Run this query after each deletion. Auto_increment will not be set to 1, it will automatically set the lowest possible value.