How to prevent auto increment from missing id numbers in mysql database?

Question

Ok, so let's say I have a mysql database table with two columns, one for id and one for password. If I have three rows of data, and the id values ​​go from 1 to 3, and I delete row 3 and then create another row of data, I will see id = 4 instead of id = 3 in the newly created row. I know that this is related to the value of automatic growth, but I was wondering if I can add code to the php file, which will automatically reset all identifier numbers that you start with id = 1, and go to the last id number in steps of 1 after deleting a row?

My goal is to create a form in which the user enters a password, and the system will match the password with the password in the database. If there is a match, the row with the matching password will be deleted, and the column of identification numbers will be reordered so that id numbers are not skipped.

Update. I create a rotating banner advertising system by setting a random number from 1 to 4 in a variable so that the php file gets a random declaration from id = 1 to id = 4 using a random number variable.If the random number turns out to be 3 and id = 3 does not exist , there will be a space in the banner line. If there is a way around big gaps in this situation, please tell me. thank you in advance

Answer 1

Just run the following SQL query:

ALTER TABLE `tbl_name` AUTO_INCREMENT = 1; 

... but it sounds like a terrible idea, so don't do it. Why is the value of your primary key important? Uniqueness is much more important, and her rejection of it undermines this.

Answer 2

You can use only

 ALTER TABLE 'tbl' AUTO_INCREMENT=# 

to reset to a number greater than the highest value number. If you have 1, 2, 3, and you delete 2, you cannot use this to fill 2. If you delete 3, you can use this to reuse 3 (if you did not put anything higher). This is the best you can do.

Answer 3

 ALTER TABLE 'table' AUTO_INCREMENT = 1; 

However, running this code is not a good idea. There is something wrong with your application if you are dependent on a column that does not have spaces. Are you trying to count the number of users? if so, use COUNT (id)? Are you trying to figure out other tables? If so, use a foreign key.

If you are dead by doing it in the wrong way, you can try to find the lowest available number and make an increase yourself. However, keep in mind that race conditions are related.

Also, keep in mind that if you change the actual numbers in the database, you will need to change all the links to it in other tables and in your code.

Answer 4

You can do it:

• Inventing the mechanism that provides the next available identifier when you want to insert (for example, a transaction involving reading and incrementing an integer column somewhere, pay particular attention to the transaction isolation level!)
• Using UPDATE to reduce all identifiers that exceed the ones you just deleted (again, with a transaction - don't forget that foreign keys must be enabled UPDATE CASCADE!)

But he asks the question: why do you need this? will it be worth the trouble?

He is almost certain that you can achieve your goal without such witchcraft.

Refresh (for comment):

To select a random number of rows, you can do, for example. in MySQL

 SELECT id FROM banners ORDER BY RAND() LIMIT 5 

to select 5 random, guaranteed existing banner identifiers.

A word of caution: there are quite a few people who view ORDER BY RAND() as a bad whistle. However, IMHO is not quite right to put each case in one basket. If the number of rows in the table is controllable (I would consider something below 10K, to be not so much), then ORDER BY RAND() provides a very good and succinct solution. In addition, the documentation itself offers this approach:

However, you can retrieve strings in random order:

mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND () in combination with LIMIT is useful for choosing a random pattern from a set of strings:

mysql> SELECT * FROM table1, table2 WHERE a = b AND c ORDER BY RAND () LIMIT 1000;

RAND () is not intended to be a perfect random generator. This is a quick way to generate random numbers on demand, which is transferred between platforms for the same version of MySQL.

Answer 5

Well, you can simply specify the identifier number that you want the entry to be as part of your insert statement, for example:

 INSERT INTO person VALUES(1,'John','Smith',' [e-mail] ','[phone]'); 

And if there is no primary key conflict (there is no record in the database with id = 1), then MySQL will be happy to execute it.

The ALTER TABLE 'tbl' AUTO_INCREMENT=# function also works and means you do not need to keep track of the counter.

Although you are thinking about this, you can read some of the discussions on natural and surrogate keys . The idea that your id # is especially important is a little unusual and may be a sign of a problematic design.