0

everyone!

Currently, I am making a new schema using MySQL, and trying to alter auto_increment from 100. But sometimes when I set auto_increment from 100, it starts from 101, not 100. Is there anybody who knows why???

When I set auto_increment = 100 for the first time in the same schema, it works well. But when I try to alter auto_increment = 200, 300, or 1000, it doesn't start from the number I want. It starts from 201, 301, or 1001. I just started to study MySQL so I need help to figure out why.

Thanks in advance.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Mihee Kim
  • 1
  • 1
  • 1
    I can't reproduce this. After I alter the auto_increment, the next row I insert has that ID. – Barmar Nov 08 '22 at 02:21
  • 1
    Is it possible you're altering to an ID that's already in use? It won't reuse an ID, so it will keep incrementing until it gets to one that's not used yet. – Barmar Nov 08 '22 at 02:22
  • I think you need to explain why you need to change the AUTO INCREMENT parameters at all. AUTO INCREMENT is intended to create a unique identifier for a row. It's not intended to be changed or fiddled with. – Tangentially Perpendicular Nov 08 '22 at 02:37
  • It was just for my practice. I just noticed when I set `alter table auto_increment = 1000` and `@@auto_increment_increment = 1`, it worked well. but when I tried to set `@@auto_increment_increment` to a different number like 5, the problem occurred. – Mihee Kim Nov 08 '22 at 02:51
  • This is "by design". [fiddle](https://dbfiddle.uk/DpDMSYUG). This cannot be treated as a bug because autoincrement provides/guarantees: (1) then next generated value is above any existing value (2) if the attribute was not altered then next generated value is above any previously generated value. The rule 1 also provides the generated value uniqueness when a lot of concurrent generations are performed. That's all. Hence the effect observed is an implementation feature only. In general you'd never access/look at AI PK value, it is used for unique row identifying only (in FKs and JOINs). – Akina Nov 08 '22 at 04:53

1 Answers1

0

The product is behaving as documented.

https://dev.mysql.com/doc/refman/8.0/en/create-table.html says:

AUTO_INCREMENT

An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.

(emphasis mine)

So if you want the next value to start with 100 for example, you need to ALTER TABLE <name> AUTO_INCREMENT=99.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you! but I just noticed when I set `alter table auto_increment = 1000` and `@@auto_increment_increment = 1`, it worked well. **but when I tried to set @@auto_increment_increment to a different number like 5, the problem occurred.** Could you please check this out too? I don't know if it will be automatically solved if I study more about SQL. – Mihee Kim Nov 08 '22 at 02:52
  • What did you expect to happen when you set the increment to 5? – Bill Karwin Nov 08 '22 at 05:29
  • when I set auto_increment=1000, and @@auto_increment_increment=5, I expected to happen 1000, 1005, 1010... – Mihee Kim Nov 08 '22 at 07:46
  • But if the auto_increment is currently set to 1000, the next row inserted will be given the id 1000 + the increment. – Bill Karwin Nov 08 '22 at 16:14