I want to reset and recount a column called id in myusers table, and i want to do this anytime a delete was made on the database, so that the id column will start from 1 till the number of rows in the table, instead of having 568 when the total rows is 18
but if you tell the autoincrement to go back to 0 (or 1), then… what is it going to do when there’s already a row 1?
Then don’t use the autoincrementing ID for your display. But rewriting an autoincrementing ID field can be incredibly dangerous/difficult, especially if that ID is used as a foreign key for other tables (which is the usual reason for an autoincrementing field…)
My fears exactly, the thing is it does not even work, after altering the table the next insert goes back 589.
But there should be a way to recount.
Especially if my value length is 11 and users have already added and deleted many rows to the point the count is heading to 10000078979
Theoretically… If you had all your deleting with cascading deleting of related entities, and you have no imports somewhere outer of your DB, you can try to start again with auto increment.
Thanks very much @m_hutley
I now realize 16 zeros is roughly trillions
So i will set it to a length value of 20 for INT and then hide the id count from being displayed in the front page.
Before the user will reach trillion count the world would have ended.
Or better still we export his data, reset the auto increment to zero manually and then import back his list the other columns.
So that the count will be reset automatically.
You can’t define the value for an INT. An INT always goes from -2147483648 to 2147483647. The 20 in INTEGER(20) is only used for display purposes when you use zerofill (which in general you hardly need).
That said, I pretty much never ever expose an autoincrementing field to the user if i can help it, and so I rarely if ever care about the autoincrement’s current/next value.
Exposing an autoincrement gives information away. (Socially Engineering Information)
How many other users are there? How many different orders are there? If I want to attack his database, it looks like if i’m user 400 and user 401 is my alternate, he’s using an autoincrement. I probably want to try and attack userid number 1, cause that’ll usually be the admin…
@m_hutley you are right but the ids is not actually users ids, is just an email list ids where users store email list for marketing but all the same is a bad idea staying in public view.
Please forgive my cluelessness, thats what i often see anytime i can to create an INT if i set the length value above 20 it prints and error or revert back to the default 10
Well am just learning about this now, you can shade more light on signed and unsigned cases. I used to see Unsigned but had no idea what it does
Baseline description: an INT is the smallest 32 bit number to the largest 32 bit signed number: -2,147,483,648 to 2,147,483,647.
An UNSIGNED INT is also a 32 bit number, but it doesnt use the sign bit, meaning it can use the highest bit to hold more value, but all the numbers must be positive - which means it goes from 0 to 4,294,967,295.