Reset auto increment in database after delete

PHP
1

Please great talents,

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

 ALTER TABLE myusers AUTO_INCREMENT= 0;

The above code is not doing the at all

2

  1. I think, auto increment should to start from 1.

  2. Are you sure, you should to play with automatically generated ID? What is with you data consistency?

3

@igor_g i have tried starting it from 1 yet nothing.

Is just a columns i created and made it my primary key and also set it to auto increment.

I don’t like seeing 589 when the remaining rows in the table is 18

The old deleted post still remains with an id and new insert starts from where last number stopped.

4

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…)

5

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

6

Big number is not the reason.

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.

But better not.

7

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.

8

Thanks @igor_g

You are right, when we get to the bridge we shall cross it

9

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).

See https://dev.mysql.com/doc/refman/8.0/en/integer-types.html and https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html

1 Like
10

(Unless it’s UNSIGNED :wink: )

11

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…

2 Likes
12

@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.

13

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

14

How technical of an explanation do you want?

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.

1 Like
15

Thanks i just finished reading the reference links @rpkamp sent it was well explained there, so am going to use bigint i

Thanks alot

16

I hope 255 is a character limit for VARCHAR data type? For bigger text i can just use text data type?

17

It is, but the limit on VARCHAR is usually 65535. CHAR has a limit of 255.

18

Thanks alot

19

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.