Auto_increment after a delete

[johnphpnewb]

Today i am working on my member pages. I want to add a username and password changing form. Suddenly, i realized that if a member is to be deleted, then what happens to the database row according to auto_increment? is the table automatically updated? I searched a bit and i see that the row is removed but nothing is changed in the auto_increment values. I see that one can use ALTER TABLE tablename AUTO_INCREMENT = x; to reuse that id but many people suggest to not reuse the id. I assumed that reset means that mysql will recount/move rows to fill in missing numbers. But this is not the case. So is okay to have missing numbers? it bothers me because it is unorganized but i can let it go if it is normal.

However, i also wonder how large should i make an id number to accomodate active members and deleted members? i currently have id int(11). I understand this to mean 11 digits (10,000,000,000) thus 10billion possibilities. Is this correct? I’m really nescient about this subject. I really do not know the answer.

Thank you.

[JimL]

@johnphpnewb

I assumed that reset means that mysql will recount/move rows to fill in missing numbers. But this is not the case. So is okay to have missing numbers? it bothers me because it is unorganized but i can let it go if it is normal.

This is normal, expected and wanted. Simple example. A website has job listings, and I bookmark listing 1164. The next day this listing is deleted, and a new one is registered. My bookmark now goes to the new listing and I (the user) is thoroughly confused.

@johnphpnewb

However, i also wonder how large should i make an id number to accomodate active members and deleted members? i currently have id int(11). I understand this to mean 11 digits (10,000,000,000) thus 10billion possibilities. Is this correct?

@https://stackoverflow.com/a/36707990/1078488

To summarize : The number N in int(N) is often confused by the maximum size allowed for the column, as it does in the case of varchar(N).

But this is not the case with Integer data types- the number N in the parentheses is not the maximum size for the column, but simply a parameter to tell MySQL what width to display the column at when the table’s data is being viewed via the MySQL console (when you’re using the ZEROFILL attribute).

The number in brackets will tell MySQL how many zeros to pad incoming integers with. For example: If you’re using ZEROFILL on a column that is set to INT(5) and the number 78 is inserted, MySQL will pad that value with zeros until the number satisfies the number in brackets. i.e. 78 will become 00078 and 127 will become 00127. To sum it up: The number in brackets is used for display purposes.
In a way, the number in brackets is kind of usless unless you’re using the ZEROFILL attribute.

So the size for the int would remain same i.e., -2147483648 to 2147483648 for signed and 0 to 4294967295 for unsigned (~ 2.15 billions and 4.2 billions, which is one of the reasons why developers remain unaware of the story behind the Number N in parentheses, as it hardly affects the database unless it contains over 2 billions of rows), and in terms of bytes it would be 4 bytes .

For more information on Integer Types size/range, refer to MySQL Manual

What is the size of column of int(11) in mysql in bytes?

So to maximise your ID columns (where there will always be positive numbers, just use unsigned.

[johnphpnewb]

wow! i need to spend more time reading the manual and less time searching Google. I, like other fools, had assumed that int(11) sets the maximum size for the column. Thank you for correcting me. Now i see how foolish it is to specify int(11) along with unsigned and not a zerofill to be seen. I have no idea why i thought that sql would be any different than c++. integer is integer with a predetermined size. Which, now i realize that i should be implementing a smallint with my db. I doubt that i will have 65000 paying members.

Thank you, Jim.

[astonecipher]

Also, you don’t do hard deletes. You have a column that flags the record as deleted and filter on it when doing a query.

[johnphpnewb]

Hello, astonecipher. Thanks for the tip. Although, i think that i need to research the subject. I’m not sure if maintaining user records after a subscription has ended is a violation of EU privacy laws.

[JimL]

I’m not sure if maintaining user records after a subscription has ended is a violation of EU privacy laws.

Well it would be quite annoying re-registering to every site you subscribe to whenever the subscription runs out

[johnphpnewb]

Hi Jim, i agree but i don’t make the laws. I’m not sure if the EU laws prevent soft delete database actions or not. I need to look into it. Better to be safe than sorry. However, i would prefer using a transaction to move deleted users to a deleted users table instead. Plus delete the password field after moving the data.