auto_increment id with unique key?

Question

I have a unique key hashtag and a auto_increment id.

when one user send a duplicate hashtag it will not be inserted but the id will increase.

eg:

5 amazing 
1 cool 
8 do 
3 nice 

notice that one user sends cool and another send cool after it, so I haven't id 2. why?

CREATE TABLE IF NOT EXISTS `hashtags` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `hashtag` varchar(100) NOT NULL,
  UNIQUE KEY `hashtag` (`hashtag`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ; 

Answer 1

It is a "normal" behavior caused by the execution order built in to InnoDB engine. Might be implemented in this way for speeding up the execution. When you use auto increment, there is some kind of model that stores the next available index (id) for a table. So inserting item with insert clause the engine first asks the next available index (id) from that model, that also increments the value so it is ready for the next guy who wants the next id. So when implemented in this way, the model does not care whether the insert clause execution succeeded or not.